Saving a spreadsheet from within an ACCESS macro

[Marv]

Anyone out there had any experience with saving with the
DoCmd.Save? I had to switch my macro to vb code due to
lan connectvitity and the long delays in retriving data
over my work LAN. I can now open the spreadheet and
delay the action, but when the vb code comes across my:
DoCmd.Save , "c:\temp\filename" it comes back with an
error that says the object is not open. At first I
thought it was because the dealy in my code was not long
enough... but now I watch it open and sit there ready to
be saved, and the error still comes up. After it comes
up, I can manually go to the file menu and and save as
the spreadsheet to the right location, for the macro to
later import the spreadsheet data... but it won't do it
automatically... any suggestions? Is my code wrong (this
is my first attempt to write in vb code)? or is there
another, better way to do it?

Marv

 

[TPratt]

I've had pretty good luck opening an Excel file, formatting, and then
closing the file with the following code:

Dim xlApp as Object
Dim strPath as String

Set xlApp = CreateObject("Excel.Application")
xlApp.Visible = True

strPath = "C:/My Documents/Filename.xls"

With xlApp
.WorkBooks.Open Filename:= strPath

Do your formatting then...

.ActiveWorkBook.Close SaveChanges:= True
.Application.Quit
End With

 

[Marv]

Thanks I will try it!!

Marv

-----Original Message-----
I've had pretty good luck opening an Excel file, formatting, and then
closing the file with the following code:

Dim xlApp as Object
Dim strPath as String

Set xlApp = CreateObject("Excel.Application")
xlApp.Visible = True

strPath = "C:/My Documents/Filename.xls"

With xlApp
.WorkBooks.Open Filename:= strPath

Do your formatting then...

.ActiveWorkBook.Close SaveChanges:= True
.Application.Quit
End With






.