running Excel VBA in Access

  • Thread starter Thread starter GEORGIA
  • Start date Start date
G

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
 
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")
 
Thanks! worked like a charm!

Douglas J. Steele said:
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")
Click to expand...
 
Back
Top