running Excel VBA in Access

[GEORGIA]

hi,
I'm tyring to run this code I've gotten from Excel forum in Access
I've added MS Excel 11.0 Object Libray and VBA in reference but still no luck
it gives an error saying "test.xls" was not found but it is there.
Run-time error 1004

Thank you for your help.

Excel.Application.DisplayAlerts = False

Dim FName As String
Dim WB As Workbook

ChDrive "C"
ChDir "C:\MY Documents"

FName = Dir("*.xls")
Do Until FName = ""
Set WB = Workbooks.Open(Filename:=FName)
WB.SaveAs Filename:=Replace(FName, ".xls", ".csv"), FileFormat:=xlCSV

WB.Close savechanges:=True
FName = Dir()
Loop

 

[Douglas J. Steele]

FName will be strictly test.xls, not the complete path.

Change your code to

Set WB = Workbooks.Open(Filename:="C:\MY Documents\" & FName)
WB.SaveAs Filename:=Replace("C:\MY Documents\" & FName, ".xls", ".csv"),
FileFormat:=xlCSV

In fact, there's really no need for the ChDrive and ChDir statements: use

FName = Dir("C:\MY Documents\*.xls")

 

[GEORGIA]

Thanks! worked like a charm!

FName will be strictly test.xls, not the complete path.

Change your code to

Set WB = Workbooks.Open(Filename:="C:\MY Documents\" & FName)
WB.SaveAs Filename:=Replace("C:\MY Documents\" & FName, ".xls", ".csv"),
FileFormat:=xlCSV

In fact, there's really no need for the ChDrive and ChDir statements: use

FName = Dir("C:\MY Documents\*.xls")