Workbook.SaveAs Excel 2007 Unhelpful Error 1

[Gruuuu]

I've recently upgraded to 2007, and for the most part, my VBA seems to work. This is one of the odd, rare occasions where this is not true:

Workbook's purpose: serve as a "kickoff" processor (I give it a list of workbooks, it opens them in turn and runs the code saved in each one.)

One of the modules in the kickoff book has a collection of functions which are commonly used among the other workbooks. One of the functions just saves the book to a specific, dynamically defined folder. Currently I'm keeping everything in 2003 format, as such, I specify the format in the .SaveAs method.

Code is as follows:

Kickoff Book:

Sub main()

'Main and report books
Dim masterBook As Workbook, wkgBook As Workbook

'Paths
Dim wkgFolder As String, histFolder As String, dbFolder As String

'timeframe variables
Dim weDate As Date, fy As String, period As String, week As String

'checkbox variables
Dim rptRun As Boolean, rptHist As Boolean, rptDB As Boolean, rptVerify As Boolean

'Report Name
Dim rpt As Range

Set masterBook = ActiveWorkbook
wkgFolder = masterBook.Sheets("Main").Range("wkg").Value
histFolder = masterBook.Sheets("Main").Range("hst").Value
dbFolder = masterBook.Sheets("Main").Range("db").Value
weDate = masterBook.Sheets("Main").Range("we").Value
fy = masterBook.Sheets("Main").Range("fy").Value
period = masterBook.Sheets("Main").Range("pd").Value
week = masterBook.Sheets("Main").Range("wk").Value
rptRun = masterBook.Sheets("Main").runbox.Value
rptHist = masterBook.Sheets("Main").histbox.Value
rptDB = masterBook.Sheets("Main").dbbox.Value
rptVerify = masterBook.Sheets("Main").verifybox.Value

'Now, to loop through the report list and run macros for each one.
    For Each rpt In masterBook.Sheets("Main").Range("reports")
        If rpt = Empty Then
            GoTo skip
            Exit Sub
        Else
            macroName = "'" & rpt.Value & "'!subordinate_macro.subordinate_macro"
            Set wkgBook = Workbooks.Open(Filename:=masterBook.Sheets("Main").Range("wkg").Value & rpt.Value, UpdateLinks:=False)
            Application.Run macroName, rptRun, rptHist, weDate, rptDB, rptVerify, wkgFolder, histFolder, dbFolder, masterBook.Name, rpt.Value
            wkgBook.Close
        End If
skip:
    Next rpt
    MsgBox "List completed."
End Sub

One of the reports (they all get the same error)

Sub subordinate_macro(ByVal rptRun As Boolean, _
                      ByVal rptHist As Boolean, _
                      ByVal weDate As Date, _
                      ByVal rptDB As Boolean, _
                      ByVal rptVerify As Boolean, _
                      ByVal wkgFolder As String, _
                      ByVal histFolder As String, _
                      ByVal dbFolder As String, _
                      ByVal masterbook As String, _
                      ByVal rpt As String)


'Run
If rptRun Then
    ActiveWorkbook.RefreshAll
    Sheets(3).Name = "WE " & Format(weDate, "mm-dd-yy")
End If

'Verify
If rptVerify Then
    macroName = "'" & masterbook & "'!stdfn.stdVerify"
    Application.Run macroName, masterbook
End If

'History
If rptHist Then
    ActiveWorkbook.Save
    macroName = "'" & masterbook & "'!stdfn.stdHist"
    Application.Run macroName, histFolder, rpt, weDate
End If

'Databank
If rptDB Then
    'Paste Values
    macroName = "'" & masterbook & "'!stdfn.stdPV"
    Application.Run macroName
    'Save
    macroName = "'" & masterbook & "'!stdfn.stdDB"
    Application.Run macroName, dbFolder, rpt
End If

End Sub

And the function module (Just the one giving me problems):

Sub stdHist(ByVal histFolder As String, _
            ByVal rpt As String, _
            ByVal weDate As Date)
            
Dim rwb As Workbook
Dim hfile As String

    Set rwb = Workbooks(rpt)
    hfile = histFolder & Left(rpt, Len(rpt) - 4) & " we" & Format(weDate, "mmddyy") & ".xls"
    Application.DisplayAlerts = False
  ° [b]rwb.SaveAs hfile, xlExcel8[/b]
    Application.DisplayAlerts = True
    Set rwb = Nothing
End Sub

bolded code is where I receive this error:

Runtime Error '1004':

Method 'SaveAs' of object '_Workbook' failed

I've tried removing the fileformat value, which does save the file, however it's saved in 2007 format with the .xls extension.

I've recreated the steps necessary to run this exact scenario in completely new workbooks, but that seems to work just fine. I suspect something isn't working with my path string, but I know for certain that the path is correct, because I've copied the value of the variable and fed it into Explorer, and it pulls up the right folder.

Anyone have any ideas on what could be causing this?

 

[xlbo]

try saving without the extension specified
The xlExcel8 should tell excel which extension to put there

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[HughLerwill]

I have a several examples in running code which include the .xls extension in 'hfile' and so I do not expect that to be your problem. If you Macro record such a sequence Excel includes the .xls in the Filename parameter.

If the path in 'hfile' is a valid one the line of code should run under Excel 2007/10. However it will not run on earlier versions of Excel because they do not recognize the XlExcel8 constant, to handle that you need something like;

If Val(Application.Version) < 12 Then
rwb.SaveAs hfile
Else
rwb.SaveAs hfile, 56
'56 = xlExcel8 in Excel 12
' the constant is not recognized pre v12 so use literal value
End If

 

[Gruuuu]

Removing the .xls extension did not solve the issue, sadly.

Hugh, I've gone ahead and put the version spec code in there, because it's wise to do anyway, but it certainly didn't solve anything. Since the file is being run in 2007, it ran with the same code, and failed in the exact same way.

I, too, have set up other books with exactly this setup, and it works just fine. The only odd thing that I've found is this:

The .Path property of the workbook (rwb) is defined like so:
"Z://Path/Path/Path..."

I'm not certain if this is usually the case or not though.

 

[HughLerwill]

Its an invalid path!

 

[Gruuuu]

Well, yes, that's obvious. This is why I brought it up.

...but that's not in my hfile.

If I look at the value of hfile, it's along the lines of

"Z:/Path/Path/File.xls"

So, if indeed it's trying to insert the extra slash... why for? I don't get it.

 

[Gruuuu]

Although I suppose the much more salient question is: how do it make it not do that?

I can confirm that, through a brief testing run, verbal reprimand has no effect.

 

[PHV]

I'd trace the value of histFolder ...

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[Gruuuu]

histFolder is clean

"X:/G...y/Period 11/Week 4/"

(elipsis included to hide file structure)

 

[PHV]

So, where is the Z: coming from ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[Gruuuu]

Just an example. Sorry. It's just a networked drive. The actual path is "X:/..." et cetera.

The path information in both the histFolder and hfile variables are 100% correct. I've checked and double checked that, to be sure.
(I mean I copied the value from the watch window and pasted it into explorer, I've spat the value out to a cell and copied it from there, eyeballed it to death to see if there were some funky characters or whatever. Everything showed clean)

 

[HughLerwill]

Just to test try replacing;

rwb.SaveAs hfile, xlExcel8

with;

rwb.SaveAs "X:\What\You\Know\The\Path2\Be.xls", xlExcel8

and see if that works.

Try using conventional backslashes rather than forward slashes just in case that is causing a problem.

 

[PHV]

The actual path is "X:/..." et cetera
Really ?
Not "X:[!]\[/!]..." et cetera ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[Gruuuu]

You gotta be flippin kiddin me.

The actual path is "X:/..." et cetera
Really ?
Not "X:\..." et cetera ?

This has not once ever been an issue with Microsoft products, since it automatically converts slashes.

Actually now that I'm thinking about it, I should have seen this one coming. I noticed at one point that Excel was trying to apply URL encoding to the file path when saving (%20 all over the place for spaces), which is completely viable in file names, which is why I was still boggled.
But forward slashes never fly!
So clearly at some point it was kicking it out because it didn't pass URL encoding standards. Maybe because it's a network drive?

Bizarre that 2007 started trying to URL encode my file paths though.

Anyhow, thanks, PHV for once again pointing out the blindingly obvious mistakes!

-> PHV

 

[SkipVought]

Workbook.SaveAs Excel 2007 Unhelpful Error

Have you found any HELPFUL errors? ;-)

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[Gruuuu]

Truth be told, YES.

I'm generally inclined to look at the error popup and say "geez, great, *close*" But I've been forcing myself to clock on the Help button.

Even though I'm at the very least vaguely aware of the information within, it can jog the memory pretty well sometimes.

This error did not include a help button.

 

[SkipVought]

Ohhhhhh!

look at the error popup

You're referring to the error message!

Personally, I have never found an error to be helpful. I rather prefer to not have any, and take pride in producing error-free code. But on occasion, more than I care to admit, my code generates an error or two and THEN I value a cogent and relevent error message, which I find helpful.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[xlbo]

Skip - Error free code?

Easy

Sub GiveMeNoErrors()

On error resume next

'code here

End Sub

;-)

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[SkipVought]

Untrapped errors that cause DEBUGS -- AVOID!!!

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}