VBA code hangs while opening a file from URL

Question

I am trying to directly open an axf file in excel and then using my other functions I format the data. The problem I am having is sometimes the file opens without any delay and sometimes it never open and hangs the excel.

Sub Workbook_Open()

Dim OriginalSecuritySetting As MsoAutomationSecurity
sCSVLink = "http://www.bom.gov.au/fwo/IDQ60801/IDQ60801.99367.axf"
sfile = "IDQ60801.99367.axf"
ssheet = "Hay_Point_Data"

Set wnd = ActiveWindow
Application.DisplayAlerts = False
Application.ScreenUpdating = False
Sheets(ssheet).Cells.ClearContents

OriginalSecuritySetting = Application.AutomationSecurity
Application.AutomationSecurity = msoAutomationSecurityForceDisable
Workbooks.Open Filename:=sCSVLink

End Sub

Please advise how can I make it to run every time. Thanks

The hang is due to the server being slow to respond - you can't fix that from your side. You could maybe try to download the file asynchronously so that Excel doesn't lock up while waiting. Eg see https://stackoverflow.com/questions/7747877/asynchronous-file-downloads-from-within-vba-excel — Tim Williams, Dec 23 '20 at 03:43

score 1 · Accepted Answer · answered Dec 23 '20 at 03:30

You need something like this: How do I download a file using VBA (without Internet Explorer) Answered by Ole Henrik Skogstrøm To open the same file, you need to import the data type, in this way, it is possible to identify the workbook and finish its adjustments. Good Luck

Sub DownloadFile()

Dim myURL As String
Dim SrtPath As String
Dim SrtFile As String

myURL = "http://www.bom.gov.au/fwo/IDQ60801/IDQ60801.99367.axf"

Dim WinHttpReq As Object
Set WinHttpReq = CreateObject("Microsoft.XMLHTTP")
WinHttpReq.Open "GET", myURL, False, "username", "password"
WinHttpReq.send

SrtPath = ActiveWorkbook.Path & "\" & "IDQ60801.yourname.axf"
If WinHttpReq.Status = 200 Then
    Set oStream = CreateObject("ADODB.Stream")
    oStream.Open
    oStream.Type = 1
    oStream.Write WinHttpReq.responseBody
    oStream.SaveToFile SrtPath, 2 ' 1 = no overwrite, 2 = overwrite
    oStream.Close
End If
Workbooks.Open Filename:=SrtPath
End Sub

VBA code hangs while opening a file from URL

1 Answers1