Thread: importing external data from a website

Hi there!

I am trying to download a csv file from a website by using the import external data function. I have created a macro that does it but I want to generalise it. In the Macro below I would like that:

a) Instead of using ds090312fo (which stands for 12 March 2009)
to use the previous business day, i.e. today Monday we should use
090313 (13 March 2009)).

b) If I leave excel open to make it automatically so that the program runs
by itself everyday at 8.30 am.

Does anyone have a clue? Any help on the questions would be highly appreciated. THANK YOU VERY MUCH!

Pep

Sub Macro1()
'
' Macro1 Macro
' Macro recorded 16/03/2009 by Ppg
'

'
    With ActiveSheet.QueryTables.Add(Connection:= _
        "TEXT;[url]http://www.liffe.com/data/ds090312fo.csv", Destination:=Range("A1"))
        .Name = "ds090312fo"
        .FieldNames = True
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .TextFilePromptOnRefresh = False
        .TextFilePlatform = 850
        .TextFileStartRow = 1
        .TextFileParseType = xlDelimited
        .TextFileTextQualifier = xlTextQualifierDoubleQuote
        .TextFileConsecutiveDelimiter = False
        .TextFileTabDelimiter = True
        .TextFileSemicolonDelimiter = False
        .TextFileCommaDelimiter = True
        .TextFileSpaceDelimiter = False
        .TextFileColumnDataTypes = Array(1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1)
        .TextFileTrailingMinusNumbers = True
        .Refresh BackgroundQuery:=False
    End With
End Sub

Hi RaviShankar,
Thank you very much for your quick reply.

I have however a small problem when running the macro. If I .try to debug I go to the line:

Refresh BackgroundQuery:=False

which is the problem I was always getting. Do you happen to know whether this is a problem on the macro itself or in the settings?

I tried in different PCs but it did not work either.

Thanks a lot!

Ppg

ppg

Please take a couple of minutes and read ALL theForum Rules then wrap your VBA code (Rule 3)

Please make sure you wrap your VBA code in both of your postings in this thread

Hi RaviShankar,

Thank you very much for your quick reply. Here below is the Macro that you sent me:

Sub Macro1()
'
' Macro1 Macro
' Macro recorded 16/03/2009 by Ppg
'

'
    With ActiveSheet.QueryTables.Add(Connection:= _
        "TEXT;http://www.liffe.com/data/ds090312fo.csv", Destination:=Range("A1"))
        .Name = "ds090312fo"
        .FieldNames = True
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .TextFilePromptOnRefresh = False
        .TextFilePlatform = 850
        .TextFileStartRow = 1
        .TextFileParseType = xlDelimited
        .TextFileTextQualifier = xlTextQualifierDoubleQuote
        .TextFileConsecutiveDelimiter = False
        .TextFileTabDelimiter = True
        .TextFileSemicolonDelimiter = False
        .TextFileCommaDelimiter = True
        .TextFileSpaceDelimiter = False
        .TextFileColumnDataTypes = Array(1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1)
        .TextFileTrailingMinusNumbers = True
        .Refresh BackgroundQuery:=False
    End With
End Sub

I have however a small problem when running the macro. When I run it the macro

crashes and if I try to debug it goes directly to the line below:

Refresh BackgroundQuery:=False

which is the problem I was always getting. Do you happen to know whether this is a problem on the macro itself or in the settings?

I tried in different PCs but it did not work either.

Thanks a lot!

Ppg