PDA

View Full Version : Solved: open file with a date variable



vzachin
06-30-2009, 11:02 AM
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

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"

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

what am i missing?

thanks
zach

vzachin
06-30-2009, 11:18 AM
i didn't try hard enough
this works now

mydat = Format(Date, "yyyymmdd")
myfile = "xxxx_" & mydat & ".dat"
Workbooks.Open Filename:= _

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


sorry for the post

mdmackillop
06-30-2009, 11:18 AM
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

p45cal
06-30-2009, 11:20 AM
deleted

vzachin
06-30-2009, 04:41 PM
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
07-01-2009, 12:40 AM
Dat is normally a text file. Just use your original "Open" line (it was hidden on my screen)

vzachin
07-01-2009, 10:39 AM
thanks malcolm