Importing .csv file from web to excel

Arnie_26

New Member
Joined
Jul 19, 2006
Messages
2
Hi All,
I am a novice Excel/VBA user and I would be gratiful for some good advice on a problem I'm having with importing a .csv file from the web.
I have written/copied some VBA code to import a .csv file from http://www.afr.com/sharetables/weekly/2006/10/13/DDiswk061013.csv
into a worksheet called "import sheet".
Because the location and file name changes each week to say http://www.afr.com/sharetables/weekly/2006/10/20/DDiswk061020.csv
the following week I reference a cell named "ASXIWkURL" which updates weekly for the URL location.
This seems to work fine as I tested it by importing the same .csv file i previously downloaded manually to my "C:". But when I use the same code to access the same file on the web directly I get an error saying "Unable to open file http://www.afr.com/sharetables/weekly/2006/10/13/DDiswk061013.csv.
The Internet site cannot return object you request. (http/1.0 403)".



Here is the code I am using
Code:
Public Function GetName(ByVal Name As String, Optional ByVal NoErr As Boolean) As String
If Not NoErr Then
  On Error GoTo Woops:
End If
GetName = Range(Name)
Exit Function
Woops:
MsgBox "You have deleted a defined cell range name, Please contact " & myEmail _
  & " for assistance." & vbCrLf & vbCrLf & "You could try using Special->Upgrade Version to fix the problem" _
  , vbCritical, "Name " & Name & " not found"
GetName = ""
End Function





Sub WebData_Weekly()
Application.Calculation = xlManual
Sheets("Import sheet").Activate
Dim url As String
urlIWk = GetName("ASXIWkURL")

Application.ScreenUpdating = False
On Error GoTo CantDo:
Application.StatusBar = "Getting Data ..."

     
 'Industrials
     
     
     'THEN NEXT LINE OF CODE where I would like
     'to have the url retrived from the text box
     'and used for each report.

    With ActiveSheet.QueryTables.Add(Connection:= _
        "URL;" & urlIWk, _
         Destination:=Range("A1"))


         
  .FieldNames = False
  .RefreshStyle = xlOverwriteCells
  .RowNumbers = False
  .FillAdjacentFormulas = False
  .RefreshOnFileOpen = False
  .HasAutoFormat = True
  .BackgroundQuery = True
  .TablesOnlyFromHTML = False
  .Refresh BackgroundQuery:=True
  .SavePassword = False
  .SaveData = True
    End With
On Error GoTo 0
 

Excel Facts

Excel Can Read to You
Customize Quick Access Toolbar. From All Commands, add Speak Cells or Speak Cells on Enter to QAT. Select cells. Press Speak Cells.

Forum statistics

Threads
1,136,515
Messages
5,676,302
Members
419,619
Latest member
jalme

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
Top