CODEWITHSUNDEEP
vba
percydeveloper
percydeveloper

Reputation: 49

VBA code hangs while opening a file from URL

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

Upvotes: 0

Views: 217

Answers (1)

Julio Gadioli Soares
Julio Gadioli Soares

Reputation: 336

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

Upvotes: 1

Related Questions