I'm looping through a list of csv files from a website which I want to open and extract the data from. The reason I'm using VBA is because the specific files I need to open varies day per day, and those specific file references is available only in an excel view. The second reason is because I've already made a similar scraping application in vba, so I already had half the code.
The end user of the application doesn't need clean or fast code, just that it works, because checking these files manually now is a daily chore of 2 hours per day.
So far I'm already logged into the website where the secondairy files are stored (This website has no API so I'm scraping it) and I'm opening those files by letting the code click the buttons. The code then clicks the export button, which opens the dialog box to
open, save (dropdown), cancel
I just want to open and extract the data then close, so I'm using the code from VBA Internet Explorer Automation - How to Select "Open" When Downloading a File . It feels like my code is bugging on this part...
Private Declare PtrSafe Function FindWindowEx Lib "user32" Alias "FindWindowExA" (ByVal hWnd1 As LongPtr, ByVal hWnd2 As LongPtr, ByVal lpsz1 As String, ByVal lpsz2 As String) As LongPtr
Dim ie As InternetExplorer
Dim h As LongPtr
Function Download()
Dim o As IUIAutomation
Dim e As IUIAutomationElement
Set o = New CUIAutomation
h = ie.Hwnd
h = FindWindowEx(h, 0, "Frame Notification Bar", vbNullString)
If h = 0 Then Exit Function
Set e = o.ElementFromHandle(ByVal h)
Dim iCnd As IUIAutomationCondition
Set iCnd = o.CreatePropertyCondition(UIA_NamePropertyId, "Open")
Dim Button As IUIAutomationElement
Set Button = e.FindFirst(TreeScope_Subtree, iCnd)
Dim InvokePattern As IUIAutomationInvokePattern
Set InvokePattern = Button.GetCurrentPattern(UIA_InvokePatternId)
InvokePattern.Invoke
End Function
before I invoke this function I check the amount of workbooks and make excel wait upto 20 seconds until another workbook is opened. I do that with this code
xnum1 = Application.Workbooks.Count
Download
t = Now
tStop = t + TimeValue("00:00:20") 'Adjust the TimeValue as needed "hh:mm:ss"
Do Until t = tStop Or Application.Workbooks.Count > xnuml
DoEvents
t = Now
Loop
However it seems that even with this wait time, excel doesn't want to open the file during every loop. If I run it step by step, it does open the file, when I let it run on it's own, it doesn't.
After I downloaded the file I export the data to the main file and then close it, using Application.DisplayAlerts to ensure no dialog box prevents the closing of the file. But because the code bugs in the download I've had instances where the file opened too late and then it didn't close, and because they all have the same filename I made an extra safegaurd in the main code:
xnum1 = Application.Workbooks.Count
For y = 1 To xnum1
If Left(Application.Workbooks(y).Name, 10) = "export.csv" Then
Application.Workbooks(y).Close
End If
Next
I can't seem to figure out why excel isn't opening the files properly and then closing them properly... Seems like the code just runs, goes into error mode because the file didn't open in the second run, although no error is given by the code itself even when I step through it.
My gut is telling me that this Download function is where the problem lies, but I can't point out how to fix it...
I've also tried with sendkeys %{O}, the shortkey to click open, but this also didn't open the file. When I manually click alt + O it does open the file...
Any suggestions?
