Solved: open file with a date variable

[vzachin]

hi,

i download a daily file that is in this format.
i want to automate the download but i'm having trouble with the date

http://xxxx.xxxx.com/xxxx_xxx/xxxx/xxxx/xxxx/xxxx_20090630.dat

this is what i've tried
[vba]
Sub reportdate()
dim mydat as string
mydat = Format(Date,"yyyymmdd")
Workbooks.Open Filename:= _

http://xxxx.xxxx.com/xxxx_xxx/xxxx/xxxx/xxxx/xxxx_mydat.dat"

[/vba]
but i could not open the file. mydat does not show today's date at all.

what am i missing?

thanks
zach

[vzachin]

i didn't try hard enough
this works now
[vba]
mydat = Format(Date, "yyyymmdd")
myfile = "xxxx_" & mydat & ".dat"
Workbooks.Open Filename:= _

"http://xxxx.xxxx.com/xxxx_xxx/xxxx/xxxx/xxxx/" & myfile & " "

[/vba]

sorry for the post

[mdmackillop]

[VBA]Sub Macro1()
Dim MyDat As String
MyDat = Format(Date, "yyyymmdd") & ".dat"
MsgBox "http://xxxx.xxxx.com/xxxx_xxx/xxxx/xxxx/xxxx/xxxx_" & MyDat
ActiveWorkbook.FollowHyperlink "http://xxxx.xxxx.com/xxxx_xxx/xxxx/xxxx/xxxx/xxxx_" & MyDat
End Sub
[/VBA]

[p45cal]

deleted

[vzachin]

hi malcolm,

thanks for the solution. just a quick question:
with your method, the file opens with notepad.
with my method, the file opens with Excel.
any insight as to why this would happen?

thanks again
zach

[mdmackillop]

Dat is normally a text file. Just use your original "Open" line (it was hidden on my screen)

[vzachin]

thanks malcolm