Hello all,
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)
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)
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
iEnd = InStrRev(strString, ".com") 'This is where your url ends
strURL = 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:\USers\FilipposP\Downloads\deprivation-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!
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)
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)
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
iEnd = InStrRev(strString, ".com") 'This is where your url ends
strURL = 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:\USers\FilipposP\Downloads\deprivation-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!
