Hi, Below is the strPDFLink and file under PMLA Act 2002 and Rules frame thereunder.pdf File - Requirement under.
Hello,
I have a list of URLs in column A which are for separate reports in Excel format. (The URLs are formatted like in the attachment below)
In column B, I have a list of the names I would like to save/name each file as, e.g Bookings.
I would like help to create a macro that does the following:
1. Opens the URL from cell A1
2. Macro waits until the Open/Save option appears before continuing
3. Choose to 'Save As' the file as name stated in cell B1, to desktop without opening
4. Choose yes to replace file that already exists with that name
5. Close the IE window opened in step 1
6. Opens the URL from cell A2
7. Macro waits until the Open/Save option appears before continuing
8. Choose to 'Save As' the file as name stated in cell B2, to desktop without opening
9. Choose yes to replace file that already exists with that name
10. Close the IE window opened in step 6
11. etc until end of list of URLs in Column A is reached
Hoping someone can help - sounds simple when I type it out but I have tried before and can't seem to figure out the whole script for the macro.
Many Thanks!
- list.jpg (37.8 KB, 96 views)
- list.jpg (168.8 KB, 5 views)
I am trying to build an Excel VBA command which will automatically do the following things:
1. Import a list from another excel wordbook in an already made excel spreadsheet where items will be matched to the corresponding column (i.e. identify address, postal code etc and assign them to the column containing address, post code etc)
2. From the post code list (column) copy paste sub 9,999 rows and paste them online to the following url (h t t p : / / i m d - b y - p o s t c o d e . o p e n d a t a c o m m u n i t i e s . o r g /)
3. Then after those values are pasted to the textbox area, I would like the ‘Get Deprivation Data’ button to be pressed and then the created xlsx file be downloaded
4. The downloaded excel workbook will be opened and all information copied/pasted to my original workbook on a specific blank worksheet
5. Repeat this function until all sub 9,999 rows are completed (i.e. if I have 30,000 rows of data do this for 3 times)
2. From the post code list (column) copy paste sub 9,999 rows and paste them online to the following url (h t t p : / / i m d - b y - p o s t c o d e . o p e n d a t a c o m m u n i t i e s . o r g /)
3. Then after those values are pasted to the textbox area, I would like the ‘Get Deprivation Data’ button to be pressed and then the created xlsx file be downloaded
4. The downloaded excel workbook will be opened and all information copied/pasted to my original workbook on a specific blank worksheet
5. Repeat this function until all sub 9,999 rows are completed (i.e. if I have 30,000 rows of data do this for 3 times)
So far, and what I have started doing is step 2 and 3, I have managed to copy paste the values to the textbox area and “press” the ‘Get Deprivation Data’ button, but I am stacked on how to download the generated xlsx document created by the website on the next page. The code I am using is the following, but I keep getting a bug when it comes to downloading the file:
Sub papafi_1_command()
Dim ie As Object
Dim MyURL As String
Set ie = CreateObject('InternetExplorer.Application')
'create new instance of IE. use reference to return current open IE if
'you want to use open IE window. Easiest way I know of is via title bar.
MyURL = 'h t t p : / / i m d - b y - p o s t c o d e . o p e n d a t a c o m m u n i t i e s . o r g/'
ie.Navigate MyURL
'go to web page listed inside quotes
ie.Visible = True
While ie.Busy
DoEvents 'wait until IE is done loading page.
Wend
'Generate text string
Dim str As String
Dim arr() As Variant
Dim tableRow As Integer
Dim tableCol As Integer
'Assign range to an array
arr = Range('C7:C10005')
Sheets('RPInputs').Range('C7:C10005').Select
Selection.Copy
'Loop through each row of the range to format a tab delimited text string
For tableRow = LBound(arr) To UBound(arr)
For tableCol = LBound(arr, 2) To UBound(arr, 2)
How To Download File From Url
str = str & arr(tableRow, tableCol) & vbTab
Next tableCol
str = str & vbNewLine
Next tableRow
With ie.Document
'Assign text string to textarea
.getElementById('postcodes').Value = str
'Timedelay
Application.Wait (Now + TimeValue('0:00:03'))
'Button Click
.getElementById('submit').Click
'Timedelay #2
Application.Wait (Now + TimeValue('0:00:30'))
'Download
Dim strURL As String
Dim strPath As String
Dim strString As String
Dim iEnd As Long
Dim iStart As Long
strString = 'Visit my webpage at h t t p : / / i m d - b y - p o s t c o d e . o p e n d a t a c o m m u n i t i e s . o r g/' 'the string you want to search
iStart = InStrRev(strString, 'http') 'This is where your url starts
Vba Download All Files From Url
iEnd = InStrRev(strString, '.com') 'This is where your url endsstrURL = Mid(strString, iStart, (iEnd - iStart) + 4)
'~~> URL of the Path
'strURL = 'h t t p : / / i m d - b y - p o s t c o d e . o p e n d a t a c o m m u n i t i e s . o r g/'
' ~~> Destination for the file
strPath = 'C:USersFilipposPDownloadsdeprivation-by-postcode (test).xlsx'
ret = URLDownloadToFile(0, FieldStart, strPath, 0, 0)
If ret = 0 Then
MsgBox 'File successfully downloaded'
Else
MsgBox 'Unable to download the file'
End If
End With
End Sub
Hope I was understood on what I am trying to do, any help is welcomed and much appreciated. I am interest in managing to download the file, however if you can help and on step 1 or 5 it will be highly appreciated.
Thanks a lot for your time and help in advance!