Error 1004 - Access-to-Excel Automation - Save Fails

[Bob Barnes]

Resuling in a Save failed...I use this code w/ other Excel
"template" files.

It fails in the "...SaveAs filename:=IPFile..." below.

B = Month(BegThree): KK = Day(BegThree): C = Year(BegThree)
X = Month(EndThree): W = Day(EndThree): F = Year(EndThree)
M = "Def_" & Right("0" & B, 2) & Right("0" & KK, 2) _
& Right(C, 2) & "_" & Right("0" & X, 2) & Right("0" & W, 2) _
& Right(F, 2)
IPFile = "C:\BobDev\" & M & ".xls"
bIPFile = True
If Dir(IPFile) <> "" Then Kill IPFile
objXLApp.ActiveWorkbook.SaveAs filename:=IPFile, FileFormat:= _
xlNormal, Password:="", WriteResPassword:="", _
ReadOnlyRecommended:=False, CreateBackup:=False

Ideas? TIA - Bob

 

[Douglas J. Steele]

What's the actual value for IPFile when it fails?

Incidentally, you'll making it much harder than it need be.

IPFile = "C:\BobDev\" & Format(BegThree, "mmddyy") & _
"_" & Format(EndThree, "mmddyy") & ".xls"

 

[Ron2006]

I found I had less trouble using the following for the Kill operation


If len(Dir(IPFile)) > 0 Then Kill IPFile


also

Have you done a msgbox or some such for what M comes out to be.
If there is an error in what you think M should be and what it
actually is, it could be having a problem with the file name in the
first place. The kill would not have the problem but the creation
would if M is incorrect.

Been there done that, that is why I ask the question.

Ron

 

[Bob Barnes]

Doug - Was away awhile.
IPFile value = C:\BobDev\Defs_050108_053008.xls

You're correct about the formatting below - will adjust to that.
Even after adjusting to that, and getting..
IPFile value = C:\BobDev\Defs_050108_053008.xls...

it STILL fails.

Other thoughts? TIA - Bob

 

[Bob Barnes]

I checked the IPFile name, and it looks fine.

Thank you for the other way to write the Kill statement.

 

[Douglas J. Steele]

Yeah, I didn't mean to imply that using the easier formatting would solve
the problem.

I was thinking that the file name might be invalid (especially if one of the
two date fields was Null), but obviously that's not the issue.

I'm wondering whether there's a timing issue if you actually do delete the
file. Try putting a DoEvents after the Kill statement, before the SaveAs.

 

[Bob Barnes]

Doug - I ran code where the file existed (a kill) and didn't exist (no
kill)..that ran correctly..and added the DoEvents as you recommended...IE...

If Len(Dir(IPFile)) > 0 Then Kill IPFile ' NewsGroup 5/20/08
DoEvents

....but it still failed both times. I have used this code, successfully,
before for a "SaveAs". Do you know of any other code to save an Excel file
thru automation?

I've had similar automation-to-Excel (can't remember the specifics, but it's
in sorting a Named Range..different settings were required...maybe something
in..

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

....requires a different entry?

TIA - Bob

 

[Douglas J. Steele]

Afraid I've never had a problem with the SaveAs method (and, unfortunately,
as far as I know it's the only game in town...)

See whether removing the optional parameters makes a difference. You're
using the default values in all cases, so

objXLApp.ActiveWorkbook.SaveAs filename:=IPFile

should be all you need.

 

[Bob Barnes]

Doug - Using just...
objXLApp.ActiveWorkbook.SaveAs filename:=IPFile
...WORKED.

Why..I don't know...but if it ain't broke...

THANK you - Bob