Import External Data Macro

[shoo]

Hi,

I'm trying to write a macro so I can view a web page via importing data in
Excel. Normally this isn't an issue for me as I can run the automated Macro
via Tools-Macro and then import the web page that I desire via Data-Import
external Data. However, the webpage that I want to import will actually
change each day.

Here is a ample:

ftp://ftp.nasdaqtrader.com/symboldirectory/regsho/nasdaqth20051228.txt (Dec
28 2005 data)


Currently I can run the following macro and it will import the data:

With ActiveSheet.QueryTables.Add(Connection:= _

"URL;ftp://ftp.nasdaqtrader.com/symboldirectory/regsho/nasdaqth20051228.txt",
_
Destination:=Range("C6"))
.Name = "nasdaqth20051228"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.WebSelectionType = xlEntirePage
.WebFormatting = xlWebFormattingNone
.WebPreFormattedTextToColumns = True
.WebConsecutiveDelimitersAsOne = True
.WebSingleBlockTextImport = False
.WebDisableDateRecognition = False
.WebDisableRedirections = False
.Refresh BackgroundQuery:=False
End With



However, the next day I will want to view a different page - instead of

ftp://ftp.nasdaqtrader.com/symboldirectory/regsho/nasdaqth20051228.txt

(Dec 28, 2005 data)

I would want Dec 30th data:

ftp://ftp.nasdaqtrader.com/symboldirectory/regsho/nasdaqth20051230.txt



Does anyone know of any easy way that I can write a macro so that the page
I'm requesting can change from day to day without having to go into the macro
and changing the day manually?? I have no experience with writing macros but
I can actually write something in Excel so that the desired webpage address
would appear in a particular cell (say A1)

TIA

 

[Carim]

Hi

When defining your URL, why don't you use simple concatenation, with
the & operator ... such as & range("a1").value ...

HTH
Cheers
Carim