I'm currently trying to select a file to upload automatically on a website using VBA. I am stuck at the file choice, when the file browser windows pops up. Using SendKeys I try to send the full path the the file I'm trying to upload (filepath1, it is pretty long), but the SendKeys function only ever returns half of the string path or so, and doesn't respect the enter commands. The SendKeys function also works one time of out two.. leaving the search bar empty from time to time. I've seen a lot of people complaining about this on the net but no other solution then to pause the application between calls, which is what I did.
I've used Public Declare Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As Long) in a module to define the Sleep 1 calls.
Can anyone help me out here? Any help will be much appreciated thank you!
Sub GenerateKML_Macro()
' GenerateKML Macro
Dim IE As New InternetExplorer
Dim ExplorerInput As HTMLInputElement
Dim ExplorerButton As Object
Dim IEDoc As HTMLDocument
Dim NextThingy As Object
Dim iBox As HTMLIFrame
Dim iframeDoc As HTMLDocument
Dim filepath1 As Variant
Dim name_of_file As Variant
filepath1 = "...\Excel_Template\Template.xlsx"
name_of_file = "Template.xlsx"
'Load
IE.navigate "https://www.earthpoint.us/ExcelToKml.aspx"
'Show
IE.Visible = True
'Wait
WaitIE IE
'Select document
Set IEDoc = IE.document
WaitIE IE
Set iBox = IEDoc.all("iframe1")
Set iframeDoc = iBox.contentDocument
Set ExplorerButton = iframeDoc.all.Item("FileUpload1")
'ExplorerButton.Click
Sleep 1
ExplorerButton.Focus 'can't set ExplorerButton.value directly for security reasons
Sleep 1
Application.Wait Now
Sleep 1
SendKeys (filepath1)
Sleep 1
SendKeys ("{ENTER}")
Sleep 1
SendKeys (name_of_file)
Sleep 1
SendKeys ("{ENTER}")
Sleep 1
'Wait
WaitIE IE
'Close
'IE.Quit
'Unload
Set IE = Nothing
End Sub
Sub WaitIE(IE As InternetExplorer) 'Loop until page is loaded Do Until IE.readyState = READYSTATE_COMPLETE DoEvents Loop End Sub
Ps: However many times I ran the application the SendKeys never managed to select the Template.xlsx file I'm trying to reach. I devided the search path in two variable to make it clearer, but nothing came out of it.