-
Jan 21st, 2013, 02:41 PM
#1
Thread Starter
Junior Member
[RESOLVED] Excel 2003 VBA: Getting error 1004 when trying to open a *.csv file via VBA
The machine is a Win XP Pro PC with Excel 2003 (no other office application).
My program is trying to open a csv file in a series of specific folders.
The .csv file has a variable part to its name (ProfileTable1, ProfileTable2 or other number) so I want to use a wildcard like
workbook.open (filepath &"\ProfileTable*.csv")
If I run the macro from my own machine (which uses Win7, MSOffice2010 and Excel2010), no problem
But from the WinXP PC, I get an error message stating the file cannot be found.
I can however open the csv file if I specify its exact name.
It seems that in the WinXP configuration with Excel2003, the use of a wildcard with the workbook.open method is not practical.
However, I still need to open these files without knowing their full exact name.
Thanks in advance for any hints
Regards
Olivier
-
Jan 21st, 2013, 03:17 PM
#2
Re: Excel 2003 VBA: Getting error 1004 when trying to open a *.csv file via VBA
use DIR to return the full name from wildcard
Code:
mypath = "c:\test\"
myfile = dir(mypath & "profiletable*.csv")
if len(myfile > 0 then workbooks.open(mypath & myfile)
if there are multiple files you can use a do loop
Code:
do while len(myfile) > 0
'open file etc
myfile = dir
loop
i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next
dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part
come back and mark your original post as resolved if your problem is fixed
pete
-
Jan 21st, 2013, 03:54 PM
#3
Thread Starter
Junior Member
Re: Excel 2003 VBA: Getting error 1004 when trying to open a *.csv file via VBA
Hello westconn1,
Thank you for the tip, it does work in the sense that it opens the csv file.
However I used workbooks.open(myfile) since mypath was already included in the dir statement.
It works differently than my code in that it does not parse the csv file, which causes a problem in my downstream code.
But it is progress and I appreciate it. Now I have to look at how to parse the csv correctly (one cell per item)
Regards
Olivier
-
Jan 21st, 2013, 06:25 PM
#4
Thread Starter
Junior Member
Re: Excel 2003 VBA: Getting error 1004 when trying to open a *.csv file via VBA
Problem Solved!
Contrary to my previous statement, the path and filename are needed.
To ensure the file is recognized as a csv, it is best to use the .open method as follows:
workbook.open Filname:= mypath & myfile, Format:=2
Thanks
Olivier
Tags for this Thread
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|