VBA script to download CSV from Web to Workbook

ojhawkins

New Member
Joined
Nov 17, 2011
Messages
39
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
 

Excel Facts

Copy formula down without changing references
If you have =SUM(F2:F49) in F50; type Alt+' in F51 to copy =SUM(F2:F49) to F51, leaving the formula in edit mode. Change SUM to COUNT.
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.
 
Upvote 0
' 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
 
Upvote 0
Hello,

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

Code:
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
 
Upvote 0

Forum statistics

Threads
1,213,517
Messages
6,114,089
Members
448,548
Latest member
harryls

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top