VBA script to download CSV from Web to Workbook

[ojhawkins]

Hi all!

I have a workbook and want to be able to download a CSV file from a web address (http://www.asx.com.au/data/options_code_list.csv) and paste it into one of the sheets using a Clickbutton.

This is beyond my current understanding of VBA and have had trouble finding a good answer.

Would really appreciate if anyone could help.

Cheers,
O

 

[John_w]

Just do File - Open and specify the URL as the file name and copy paste to your sheet. If you want to automate this with VBA do these steps with the macro recorder.

 

[tlowry]

' try this...
' must have a worksheet (tab) called: "CSV Transfer"

Sub transfercsv()
sCSVLink = "http://www.asx.com.au/data/options_code_list.csv"
sfile = "options_code_list.csv"
ssheet = "CSV Transfer"

Set wnd = ActiveWindow
Application.ScreenUpdating = False
Sheets(ssheet).Cells.ClearContents
Workbooks.Open Filename:=sCSVLink
Windows(sfile).Activate
ActiveSheet.Cells.Copy
wnd.Activate
Sheets("CSV Transfer").Paste
Application.DisplayAlerts = False
Windows(sfile).Close False
Application.DisplayAlerts = True
Application.ScreenUpdating = True
End Sub

 

[mathchick]

Thanks so much, tlowry! This solved a huge problem for me that I wouldn't have figured out otherwise (was trying to make something else work, but your solution was much better!).

Also posting link in case it's helpful to others to see how I applied it to my problem:
http://www.mrexcel.com/forum/showthread.php?t=622952

 

[SteveOranjin]

Hello,

Can someone help me understand why the above code worked, but this code has not?

Sub Macro3()
'Defining Variables
Dim myValue As Variant
Dim Link As Variant


'Creating a link
Link = "http://cf3.myplumbingshowroom.com/scheduledScripts/brandDataAPI.cfm?brandCode="


'creating a the variable to be passed into the link


myValue = InputBox("Enter your brand code here")


Do While Len(myValue) <> 3
    myValue = InputBox("Incorrect Entry. Please enter a brandcode with 3 letters only here.")
Loop


'completing our link
Link = "http://cf3.myplumbingshowroom.com/scheduledScripts/brandDataAPI.cfm?brandCode=" & myValue
'checking if it exists
MsgBox Link


'if the query already exists, we're going to delete it. Otherwise, we're going to ignore that function.


On Error Resume Next
ActiveWorkbook.Queries("brandDataAPI").Delete
On Error GoTo 0




    Application.CutCopyMode = False
    ActiveWorkbook.Queries.Add Name:="brandDataAPI", Formula:= _
        "let" & Chr(13) & "" & Chr(10) & "    Source = Csv.Document(Web.Contents(" & Link & "),[Delimiter="","", Columns=11, Encoding=1252, QuoteStyle=QuoteStyle.None])," & Chr(13) & "" & Chr(10) & "    #""Promoted Headers"" = Table.PromoteHeaders(Source, [PromoteAllScalars=true])," & Chr(13) & "" & Chr(10) & "    #""Changed Type"" = Table.TransformColumnTypes(#""Promoted Header" & _
        "s"",{{""BrandName"", type text}, {"" BrandCode"", type text}, {"" BrandID"", Int64.Type}, {"" datalastUpdate"", type date}, {"" numproducts"", Int64.Type}, {""priceMethod"", type text}, {""URL"", type text}, {""showPrice"", Int64.Type}, {""MAP_YN"", Int64.Type}, {""MAP"", type text}, {""msrpNotes"", type text}})" & Chr(13) & "" & Chr(10) & "in" & Chr(13) & "" & Chr(10) & "    #""Changed Type"""
    With ActiveSheet.ListObjects.Add(SourceType:=0, Source:= _
        "OLEDB;Provider=Microsoft.Mashup.OleDb.1;Data Source=$Workbook$;Location=""brandDataAPI"";Extended Properties=""""" _
        , Destination:=Range("$A$1")).QueryTable
        .CommandType = xlCmdSql
        .CommandText = Array("SELECT * FROM [brandDataAPI]")
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .BackgroundQuery = True
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .PreserveColumnInfo = False
        .Refresh BackgroundQuery:=True
        .ListObjects.Add.QueryTable.ListObject.DisplayName
    End With
End Sub