Access-to-Excal Automation - Saving an Excel File

[Bob Barnes]

I have this running in Access...

ActiveWorkbook.SaveAs filename:=IPFile, FileFormat:= _
xlNormal, Password:="", WriteResPassword:="", _
ReadOnlyRecommended:=False, CreateBackup:=False

"IPFile" is a string for the File Path.

Running this code for a "first save" of the IPFile
is fine. However, I get an "object not set" if the
File exists.

Solution???

TIA - Bob

 

[Jim]

Listen to anyone else that responds over my comment.

I've not had this issue before but that may be because if a file exists that
is about to be recreated (save, filecopy, etc.) I delete or kill the file
first.

 

[Bob Barnes]

Jim...Your comment is Wonderful.

I did this...snippet...
IPFile = "C:\BobDev\" & M & S & ".xls"
bIPFile = True: ChDir "C:\BobDev"
If Dir(IPFile) <> "" Then Kill IPFile
ActiveWorkbook.SaveAs filename:=IPFile, FileFormat:= _
xlNormal, Password:="", WriteResPassword:="", _
ReadOnlyRecommended:=False, CreateBackup:=False

And...it WORKS - THANK you - Bob

 

[Van T. Dinh]

I tend to check for existence of the file first and then use Workbook.Save
if the file exists or Workbook.SaveAs if the file doesn't.

Check Excel VB Help on Save & SaveAs methods of the Workbook object.

 

[Bob Barnes]

Van - Thank you.

I will add this to my "Knowledge File".

Bob

 

[Van T. Dinh]

Hi Bob

You're welcome ...

You might like to add Tony Toews' "Newsgroup Answers" into your KF
collection (it will compete with Microsoft KB one day <smile>):

http://www.granite.ab.ca/newsgroupanswersmdb.htm