Opening Excel file from Access

[aran]

Hello,

I have about 200 records in an Access database that each have a corresponding Excel file attached to it. I have this code

Set xlApp = New Excel.Application
    With xlApp
        .Visible = True
        Set xlWB = .Workbooks.Open("C:\Documents and Settings\All Users\" & Text167, , False)
        With xlWB.Sheets("Sheet1")
End With
End With
 

Text167 is the textbox in Access that contains the filename for each specific record. The filenames are always in the format "name - year". So an example would be "ABC - 2005".

That code opens the Excel files perfectly EXCEPT when the filename has a period in it. Whether it's a period at the end or in the middle of the filename it won't open the file. So if it's "A.BC - 2005" or "ABC. - 2005" it won't open the file. It says the file doesn't exist.

Is there something I'm not seeing in the code?

[MMcCarthy]

Hello,

I have about 200 records in an Access database that each have a corresponding Excel file attached to it. I have this code

Set xlApp = New Excel.Application
    With xlApp
        .Visible = True
        Set xlWB = .Workbooks.Open("C:\Documents and Settings\All Users\" & Text167, , False)
        With xlWB.Sheets("Sheet1")
End With
End With
 

Text167 is the textbox in Access that contains the filename for each specific record. The filenames are always in the format "name - year". So an example would be "ABC - 2005".

That code opens the Excel files perfectly EXCEPT when the filename has a period in it. Whether it's a period at the end or in the middle of the filename it won't open the file. So if it's "A.BC - 2005" or "ABC. - 2005" it won't open the file. It says the file doesn't exist.

Is there something I'm not seeing in the code?

It won't see the period as valid in the name. It will assume it's followed by a file extension. You will have to rename the files.

Mary

[aran]

It won't see the period as valid in the name. It will assume it's followed by a file extension. You will have to rename the files.

Mary

Thank you.

So this is more of a limitation of Windows rather than Access or VBA?

[MMcCarthy]

Thank you.

So this is more of a limitation of Windows rather than Access or VBA?

Yes, it is I'm afraid.

[aran]

For anyone curious, I've found a solution for this:

Set xlApp = New Excel.Application
        With xlApp
            .Visible = True
            Set xlWB = .Workbooks.Open("C:\Documents and Settings\All Users\" & Text167 & ".xls", , False)
End With
End With
 

All I had to do was add the file extension at the end.

[MMcCarthy]

For anyone curious, I've found a solution for this:

Set xlApp = New Excel.Application
        With xlApp
            .Visible = True
            Set xlWB = .Workbooks.Open("C:\Documents and Settings\All Users\" & Text167 & ".xls", , False)
End With
End With
 

All I had to do was add the file extension at the end.

Thanks for posting the solution aran.

Mary