1

I want to open a file from a folder in FTP Site. This folder has multiple reports with the same name only difference is timestamp i.e. Reports for each day

So i was trying to use a Wild Character * in my code to pick up todays report , see the code below

sReportDate = Format(ReportDate, "mmddyy")
ReportName = "_______Notification_of_Separation_______.POSFTPADPRFT1JFAGP02." &   sReportDate  & "*.csv" 

Workbooks.OpenText fileName:="ftp://UserID:Password@IPAddress/Separation Notifications/" & ReportName

but it is not working and doesn't opens the file.

however if i hard code the report name it works, there is some thing wrong with wild character.

Any help ??

Swapnil agrawal
  • 29
  • 2
  • 2
    What if there were two matching files - which one would you expect to open? – Tim Williams Jul 03 '12 at 20:30
  • 1
    I tried something similar long long time ago in VB6 using the API `FtpFindFirstFile` to get the complete file name and path using wild cards. Unfortunately since I don't have access to a FTP site so I can't test it. You might want to search google on `FtpFindFirstFile API`? Once you have the complete file name and path you can then open that in Excel... – Siddharth Rout Jul 03 '12 at 21:55
  • @Tim : There will one file for each day with the date and time at the end , there isn't any doubt on that – Swapnil agrawal Jul 05 '12 at 16:51
  • @Swapnil - you might know that, but Excel doesn't (what would happen if there __were__ multiple matches?) If you want to determine the exact name of the file you want, you'll have to run a directory listing on the folder and use the name of the file which matches your requirements. – Tim Williams Jul 05 '12 at 17:03

1 Answers1

1

From Excel VBA Help:

FileName Required String. The file name of the workbook to be opened

Names of individual files on Windows systems (and I guess you are running Excel under Windows) cannot contain wildcard characters. You must specify the exact filename of the file you want to open.

Since you say all the files in the directory have "the same name only difference is timestamp", and you are providing that in sReportDate, why is the wildcard needed anyway?

If in fact the filename has a variable timestamp after the date, then you will need to interrogate the folder to find the relevant file to open. There's a start with doing that in another question: Does Dir() make any guarantee on the order of files returned? You can use wildcards when you are examining filenames in a directory — then, having found one which matches your date, you know the relevant name and can open it explicitly.

Community
  • 1
  • 1
Andrew Leach
  • 12,945
  • 1
  • 40
  • 47