VBA Run-time error '1004'

[VygonDBA]

Hi all,

I've created a form with three VBA functions actioned via 2 macro enabled buttons which when tested prior to going live and up until today worked perfectly however......now I'm getting the above run-time error with the following message when I click my macro enabled 'Save' button which refers to 'SaveCRNWithNewName' (see code below):

"Method 'SaveAs' of object '_workbook failed"

I'm using Excel 2010 with some users on Excel 2007. I'd really appreciate any help anyone can give as I'm quite green where VBA is concerned!

VBA I'm using is below:

Sub NextInvoice()
Range("F6").Value = Range("F6").Value + 1
Range("C13:F30").ClearContents
Range("C33").ClearContents
Range("E35").ClearContents
Range("C35").ClearContents
Range("C37").ClearContents
Range("C38").ClearContents
Range("B38").ClearContents
End Sub


Sub SaveCRNWithNewName()
Dim NewFN As Variant
'Copy sheet to a new workbook
ActiveSheet.Copy
NewFN = "\\vparknas\Public\WAREHOUSE\Good Receiving Forms\2015\CRN" & Range("F6").Value & ".xlsx"
'Save copied workbook with NewFN then close and move to next invoice number
ActiveWorkbook.SaveAs NewFN, FileFormat:=xlOpenXMLWorkbook
ActiveWorkbook.Close
NextInvoice
End Sub


Sub CloseCRN()
ActiveWorkbook.Save
Application.Quit

End Sub

 

[JLGWhiz]

try either deleting the & ".xlsx" from your file name, or change your FileFormat type to a regular workbook. As is, they are incompatible.

 

[VygonDBA]

try either deleting the & ".xlsx" from your file name, or change your FileFormat type to a regular workbook. As is, they are incompatible.

Thanks for your help. I've tried both options and unfortunately neither have solved to issue.

Any other options would be gratefully received.

 

[Joe4]

Also check the value of your "NewFN" variable, to make it sure it is a valid path that you have permission to.

One simple way of doing that is to add a MsgBox to return its value:

[COLOR=#333333][COLOR=#333333]Sub SaveCRNWithNewName()[/COLOR]
[COLOR=#333333]
    Dim NewFN As Variant[/COLOR]
[COLOR=#333333]
    'Copy sheet to a new workbook[/COLOR]
[COLOR=#333333]    ActiveSheet.Copy[/COLOR]
[COLOR=#333333]    NewFN = "\\vparknas\Public\WAREHOUSE\Good Receiving Forms\2015\CRN" & Range("F6").Value & ".xlsx"
[/COLOR]    MsgBox NewFN

[COLOR=#333333]    'Save copied workbook with NewFN then close and move to next invoice number[/COLOR]
[COLOR=#333333]    ActiveWorkbook.SaveAs NewFN, FileFormat:=xlOpenXMLWorkbook[/COLOR]
[COLOR=#333333]    ActiveWorkbook.Close[/COLOR]
[COLOR=#333333]    NextInvoice[/COLOR]
[COLOR=#333333]
End Sub[/COLOR][/COLOR]

 

[VygonDBA]

Thanks for your help and suggestion. I tried adding in the MsgBox idea and also confirmed the file path is valid but still get the same error message: Method 'SaveAs' of object '_Workbook' failed.

 

[Joe4]

Exactly what file format do you want to save the file in?

 

[VygonDBA]

Needs to be saved as .xlsx file but the original file needs to stay as .xlsm.

 

[Joe4]

It looks like you are copying your worksheet, but not pasting it anywhere. Before you paste it, you are saving the Activeworkbook (you have not opened or created a new workbook yet!).

If the file you are saving has macros in it, I don't think you can save it as an .xlsx.
I think you need to copy it to an entirely new workbook, not a new sheet.

 

[VygonDBA]

Thanks for all your help.
The way I figured it, the 'SaveCRNWithNewName' VBA script creates a new direct copy of the original workbook (rather than simply copying the contents of the workbook). When running the aforementioned script I can confirm that it creates a completely new workbook (evidenced by having two excel doc's open when it bugs out - one is the original .xlsm and the new one has the NewFN name at the top). Using your explaination though, how would I go about modifying the VBA so it 'pastes' the copied worksheet to a new workbook (sorry novice VBA'er)?
I've also tried saving the copy as both an .xlsm and .xlsx doc type but still get the same issue.

 

[Joe4]

I went back and recreated your situation, and tried the code you had in your first post, and it all worked fine for me. So I think it may either be an issue with the version of Excel you are running, your file path, or your file name.

1. Which version of Excel are you using?

2. What is your value in cell F6?

3. Do you have any "protected" ranges, that you are unable to clear the contents out of?

4. What happens when you run this little macro?

Sub CheckDirectory()
    If Dir("\\vparknas\Public\WAREHOUSE\Good Receiving Forms\2015\") <> "" Then
        MsgBox "Path exists."
    Else
        MsgBox "Path does NOT exist."
    End If
End Sub