Getting a file name using DIR()

[USAMax]

I am trying to open two csv files, the first one starts with ca_ewor and the second starts with ca_pr. If I run the following commands...

    Dim cf As String
    cf = Dir(Worksheets("Sheet1").Cells(1, 2) & "\ca_ewor*.csv")
    cf = Dir(Worksheets("Sheet1").Cells(1, 2) & "\ca_pr*.csv")

cf gives me the first file's name then cf gives me the second file's name.


If I open the first file before getting the name of the second file then I get a, "Run-time error '9': Subscript out of range"

    Dim cf As String
    cf = Dir(Worksheets("Sheet1").Cells(1, 2) & "\ca_ewor*.csv")
        Workbooks.OpenText Filename:=cf, Origin:=65001, _
            StartRow:=1, DataType:=xlDelimited, TextQualifier:= _
            xlDoubleQuote, ConsecutiveDelimiter:=False, _
            Tab:=False, Semicolon:=False, Comma:=True, _
            Space:=False, Other:=False                      'Open the Cummins-Allison Problem Report
    cf = Dir(Worksheets("Sheet1").Cells(1, 2) & "\ca_pr*.csv")

I know I could use two different variables and I am sure there are other ways around this but can someone tell me why this is happening.

OTHER FACTS
Each file has a different number of columns and I do expect the number of columns to change if this is related.
I am using Excel 2010

 

[ZVI]

Dir returns file name without path.
You need to add path (see in red) to Workbooks.OpenText Filename:=Worksheets("Sheet1").Cells(1, 2) & "\" & cf, ...

 

[USAMax]

Thank you Vladimir,

You are correct, I did forget to include the path in the open statement. I corrected this but I am still getting an error in the second statement when the file is open. cf = Dir(Worksheets("Sheet1").Cells(1, 2) & "\ca_pr*.csv")

If I close the file then I don't have a problem. Could it be that it cannot read the name of the file that is open?

Dave

 

[Norie]

Dave

The second Dir will be referring to the newly opened workbook for the path and I've a feeling that workbook doesn't have a worksheet named 'Sheet1'.

Try putting the path in a variable at the top of the code.

Dim cf As String
Dim strPath As String

    strPath = Worksheets("Sheet1").Cells(1,2).Value

    cf = Dir(strPath & "\ca_ewor*.csv")

 

[USAMax]

BRILLIANT! Once I read that I realized the problem. It was like a kick in the head when I read it.

Thank you!