Problem with SaveAs Method

[cicconmr]

    Dim mcDate As String
    Dim mcDocName As String
    
    myDate = Day(Now)
    Worksheets("Index Changes").Cells.Select
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
        xlNone, SkipBlanks:=False, Transpose:=False
    Application.CutCopyMode = False
    Worksheets("data").Delete
    Worksheets("Funding Data").Delete
    Worksheets("Tables").Delete
    
    mcDate = InputBox("What is today's date?", "Save File As...")
    mcDocName = "SP Changes " & mcDate & ".xls"

    ActiveDocument.SaveAs Filename:=mcDocName, FileFormat:=wdFormatXMLDocument

I'm getting an Object Required error (424), not sure where that is coming in at, I'm assuming on the string mcDocName? What am I doing wrong there....?

 

[ChrisM]

I'm not sure what ActiveDocument refers to, if you want to save your workbook, then it should be:

ActiveWorkbook.SaveAs

Though coincidentally, you have pointed out a potential bug in your code. If you don't test the results of the inputbox, then your user could theoretically type in invalid file name characters. Several of your punctuation characters, other than a period, are invalid.

 

[cicconmr]

I changed it to ActiveWorkbook.SaveAs and am now getting another run-time error

"SaveAs method of Object _Workbook Failed"

Not sure what that means? Am I writing out the method properly? Can I do it like this

ActiveWorkbook.SaveAs(mcDocName,wdFormatXMLDocument)?

 

[mikerickson]

Filename should be the complete path.
Perhaps

With ActiveDocument
    .SaveAs Filename:=.Path & Application.PathSeparator & mcDocName, FileFormat:=wdFormatXMLDocument
End With

 

[Ruddles]

ActiveWorkbook.SaveAs(mcDocName,wdFormatXMLDocument)?

I wouldn't imagine Excel would know what wdFormatXMLDocument is - it's part of the Word object model.

Try typing ?wdFormatXMLDocument in the VBA Immediate window in Excel and then try it again in Word.

Where did you get this code? It appears to be written to run in Word rather than Excel.

 

[cicconmr]

That is probably the problem, I was looking at the library online but I must have been in Micrsoft Word?

What is the appropriate Excel code for a save as?

ActiveWorkbook.SaveAs Filename:=mcDocName, FileFormat:=xlXMLSpreadsheet

?

 

[cicconmr]

Yes that worked, just have to be sure that in my input box there are no characters as mentioned earlier.

Problem solved, thank you guys for noting that!

 

[Ruddles]

"I've already told you more than I know."