Results 1 to 4 of 4

Thread: [RESOLVED] Excel 2003 VBA: Getting error 1004 when trying to open a *.csv file via VBA

  1. #1

    Thread Starter
    Junior Member
    Join Date
    Jul 2009
    Posts
    17

    Resolved [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

  2. #2
    PowerPoster
    Join Date
    Dec 2004
    Posts
    25,618

    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

  3. #3

    Thread Starter
    Junior Member
    Join Date
    Jul 2009
    Posts
    17

    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

  4. #4

    Thread Starter
    Junior Member
    Join Date
    Jul 2009
    Posts
    17

    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
  •  



Click Here to Expand Forum to Full Width