Error 1004 save and close excel document

[cstuder]

I receive emails containing excel documents and they open as (excel) read only. I run two other macro's to format it, then I want to save and close it (by using the below code).
I get a 1004 error code on line ThisWorkbook.SaveAs rootDir & FileName & ".xlsx" Any suggestions on how to change the code so it saves properly?


Sub CHProIII()
'
' CHProIII Macro
' save as and close file
'
Dim rootDir As String
Dim FileName As String

rootDir = "G:\REBATES\CutlerHammer\CH Pro's\"
rootDir = Replace(rootDir, "@1", Application.UserName)

FileName = ActiveSheet.Range("J3").Value & " " & ActiveSheet.Range("J1").Value & ".xlsx"

ThisWorkbook.SaveAs rootDir & FileName & ".xlsx"

ThisWorkbook.Close

End Sub

 

[Joe4]

Put this line of code before your "SaveAs" line, and tell us exactly what it returns:

MsgBox rootDir & FileName & ".xlsx"

 

[cstuder]

Screenshots

 

[Joe4]

OK, first thing, is that you have the extension build into the file name already, so you don't need to add it a second time in your SaveAs statement.

But the reason you are getting an error is because you are trying to save THIS workbook, which contains VBA code, with an "xlsx" extension.
That is not allowed. You cannot save files with VBA code to an "xlsx" (macro free) extension.
You must use an extension which allows VBA, like "xlsm" or "xlsb", to name a few.
And you must save it with the appropriate corresponding FileFormat.

See: XlFileFormat enumeration (Excel)

 

[cstuder]

I did read about that on another thread, but .xlsm didn't help. How would you write that?

 

[Joe4]

If you click on the link under the File Format field in the the document I referenced, you will see that "xlsm" has value 52.
So your code should look something like:

Sub CHProIII()
'
' CHProIII Macro
' save as and close file
'
Dim rootDir As String
Dim FileName As String

rootDir = "G:\REBATES\CutlerHammer\CH Pro's\"
rootDir = Replace(rootDir, "@1", Application.UserName)

FileName = ActiveSheet.Range("J3").Value & " " & ActiveSheet.Range("J1").Value & ".xlsm"

ThisWorkbook.SaveAs rootDir & FileName, 52

ThisWorkbook.Close

End Sub

 

[cstuder]

Still not working, ugh!

 

[Joe4]

Double-check the screen print you posted in post 3, and confirm that the file path shown there is accurate.

 

[cstuder]

Great point....here is the directory.

Sub CHProIII()
'
' CHProIII Macro
' save as and close file
'
Dim rootDir As String
Dim FileName As String

rootDir = "G:\REBATES\CutlerHammer\CH Pro's\"
rootDir = Replace(rootDir, "@1", Application.UserName)

FileName = ActiveSheet.Range("J3").Value & " " & ActiveSheet.Range("J1").Value & ".xlsm"

ThisWorkbook.SaveAs rootDir & FileName, 52
ThisWorkbook.Close

End Sub

 

[Joe4]

Try to see if you can save it when you hard-code it, i.e.

ThisWorkbook.SaveAs "G:\REBATES\CutlerHammer\CH Pro's\DRB64x6ll.xlsm", 52

If that works, then it probably means that you have some illegal character in J1 or J3 causing issues.
If that does not work, please tell me the exact error code/message you are getting.
Is it still the same message as before, or is it different?