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

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

user2426
1#
user2426 Published in May 27, 2018, 1:01 am

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.

You need to login account before you can post.

About| Privacy statement| Terms of Service| Advertising| Contact us| Help| Sitemap|
Processed in 0.304224 second(s) , Gzip On .

© 2016 Powered by mzan.com design MATCHINFO