downloading, opening and parsing a csv file

[eugene.gokhvat]

Hello,

I have designed a macro that utilizes the InternetExplorer.Application
object to log in to a website and download a zip file containing a csv
of daily-updated data. Unfortunately, I could not find a way to
manipulate the "File Download" prompt and resorted to using SendKeys
(bad) to open the excel file and csv. I cannot simply open the
download url directly because it is not accessible outside of the
authenticated ie session.

As a result, the csv file that I attempt to open will not load until
the macro is complete, so I cannot continue parsing it and sending it
to an access database, as I would like to.

I have included the pertinent parts of my code. I would like
"exported" in CleanData to take on the name of the newly-opened csv
file, and then continue to parse its contents, but vba does not
recognize the second workbook that I open because it delays its
opening until the code is done running.

Any advice as to breaking the macro, or opening the file in another
way would be greatly appreciated.

Thank you,
Eugene



Sub GetData()

RUNNER_WORKBOOK = ActiveWorkbook.Name

Set ie = CreateObject("InternetExplorer.Application")

With ie

.Visible = True

.Navigate "*********"

Do While .readyState <> 4: DoEvents: Loop

' Login
.document.all.Item("username").Value = "*********"
.document.all.Item("password").Value = "*********"
.document.all.Item("submitbtn").Click

Do While .readyState <> 4: DoEvents: Loop
Do Until .document.URLUnencoded

today = Format(Range("date"), "DDMMMYY")

mURL = "*********" & "date=" & today

' download the file
.Navigate mURL

' select [Open] at Download File prompt
Application.Wait Now + TimeValue("00:00:02")
SendKeys "{LEFT}"
Application.Wait Now + TimeValue("00:00:0001")
SendKeys "{LEFT}"
Application.Wait Now + TimeValue("00:00:0001")
SendKeys "{ENTER}"

' open the csv file from within the zipped file
Application.Wait Now + TimeValue("00:00:02")
SendKeys "{DOWN}"
Application.Wait Now + TimeValue("00:00:0001")
SendKeys "{ENTER}"

' pass through the prompt
Application.Wait Now + TimeValue("00:00:02")
SendKeys "{LEFT}"
Application.Wait Now + TimeValue("00:00:0001")
SendKeys "{ENTER}"

.Quit

End With

End Sub


Sub Clean()

nVisCnt = 0

For Each wn In Application.Windows
If wn.Visible Then
nVisCnt = nVisCnt + 1
Else
nHiddenCnt = nHiddenCnt + 1
End If
Next

Workbooks(nVisCnt).Activate

exported = Workbooks(nVisCnt).Name

Range(Range("A4:AK4"), Range("A4:AK4").End(xlDown)).Copy

End Sub


Sub ToAccess(database As String, table As String)

Dim objAccess As Access.Application
Set objAccess = CreateObject("Access.Application")
objAccess.OpenCurrentDatabase database, False
objAccess.Visible = True
objAccess.DoCmd.OpenTable table

objAccess.DoCmd.RunCommand acCmdPasteAppend

objAccess.Quit

Set objAccess = Nothing

End Sub