Home When using VBA to open a CSV file directly from internet explorer, I cant then Interact with the file.

# When using VBA to open a CSV file directly from internet explorer, I cant then Interact with the file.

Christian T
1#
Christian T Published in 2018-02-14 12:56:09Z
 So I have written some code to download analytic data from twitter by simulating a button click. It's not pretty code but its all I can find to work for now. I am successfully managing to click the file download, after which a 'frame notification bar' appears with the open, save options. I am successfully clicking open twice, however this is where I run into problems. The problem being that I then want to interact with the data in the CSV file which I have just chosen to open, however the CSV file doesn't come into existence until after the code finishes running. I know there must be a simple solution to this but I just don't know what to search for. I have tried to play with Wait and DoEvents to see if that helps but no luck so far. Here is my code: Private Sub CommandButton1_Click() UserForm1.Hide Dim appIE As Object Set appIE = CreateObject("internetexplorer.application") With appIE .Navigate "https://analytics.twitter.com/user/QinetiQ/tweets" .Visible = True End With Do While appIE.Busy DoEvents Loop Application.Wait (Now + TimeValue("0:00:04")) Set HTMLDoc = appIE.document Set btn = HTMLDoc.getElementsByClassName("btn btn-default ladda-button")(0) btn.Click Application.Wait (Now + TimeValue("0:00:07")) Application.SendKeys "%{S}" Dim o As IUIAutomation Dim e As IUIAutomationElement Set o = New CUIAutomation Dim h As Long h = appIE.Hwnd h = FindWindowEx(h, 0, "Frame Notification Bar", vbNullString) If h = 0 Then Exit Sub Set e = o.ElementFromHandle(ByVal h) Dim iCnd As IUIAutomationCondition Set iCnd = o.CreatePropertyCondition(UIA_NamePropertyId, "Open") Dim Button As IUIAutomationElement Application.Wait (Now + TimeValue("0:00:03")) SendKeys "%(o)" Dim wb As Workbook DoEvents Application.Wait (Now + TimeValue("0:00:15")) Set wb = GetWB Dim ws As Worksheet Set ws = wb.ActiveSheet End Sub Function GetWB() As Workbook Dim wb As Workbook wbName = "tweet" For Each wb In Application.Workbooks If wb.Name Like wbName & "*" Then Set GetWB = wb MsgBox ("Found it") Exit Function End If Next wb MsgBox ("failed to find worksheet") End Function  I know I have used some really bad techniques and apologies for that. Please can anyone help, thankyou.
Chronocidal
2#
Chronocidal Reply to 2018-02-14 13:36:28Z
 You can split the macro in two by using Application.OnTime - this will let you finish the macro (so that the CSV file can open) and then have a new macro scheduled to start a couple of seconds later:  'This is the end of CommandButton1_Click Application.Wait (Now + TimeValue("0:00:03")) SendKeys "%(o)" Dim wb As Workbook DoEvents Application.OnTime Now()+TimeSerial(0,0,15), "ContinueDownloadMacro" 'In 15 seconds the "ContinueDownloadMacro" Sub will start End Sub Public Sub ContinueDownloadMacro() Dim wb As Workbook, ws As Worksheet Set wb = GetWB Set ws = wb.ActiveSheet End Sub 
 You need to login account before you can post.
Processed in 0.463841 second(s) , Gzip On .