ActiveWorkbook.SaveAs fileName-Crashing Excel

[HdCpr]

Hello All

Hope i can get an help, At work i have a file that we are publishing daily plans ( printing and save a location )

But recently , without any clear reason, when we print the plans ( any day) all excel file prints but crashes without any dialog box messages immediately. It doesn't save to location where it used to save either

This code has worked for the past few years but when I initiate the command now it doesn't work anymore properly . i have been trying to resolve this but with limited vba knowledge not very succesful

I had upload the Couple of images related to codes . below the comment where it stops and crashes. Recently we had an Excel update on workplace as well . Using 2019 VERSION

ActiveWorkbook.SaveAs fileName:=ProdPath & fileName, ReadOnlyRecommended:=True ', FileFormat:=xlNormal

Wondering if anyone could provide any advice / tips on how to solve/troubleshoot ?

Many many thanks

 

[Jeffrey Mahoney]

I don't see XLNormal on the list for XlFileFormat enumeration (XlFileFormat enumeration (Excel))

xlOpenXMLWorkbook = .xlsx
and
xlOpenXMLWorkbookMacroEnabled = .xlsm

maybe try that change and see if it works

 

[HdCpr]

I don't see XLNormal on the list for XlFileFormat enumeration (XlFileFormat enumeration (Excel))

xlOpenXMLWorkbook = .xlsx
and
xlOpenXMLWorkbookMacroEnabled = .xlsm

maybe try that change and see if it works

Thank you soo much but it does not fix the issue .

 

[Jeffrey Mahoney]

I know it's going to sound like simple solutions, but I don't have any way of testing your code. I can see that you load the ProdPath and the filename into variables.

Can you do a debug.print on the filename and share that? Can you confirm that the path exists in windows explorer. Can I assume that rf-fileprint is the name of the server?

 

[HdCpr]

I know it's going to sound like simple solutions, but I don't have any way of testing your code. I can see that you load the ProdPath and the filename into variables.

Can you do a debug.print on the filename and share that? Can you confirm that the path exists in windows explorer. Can I assume that rf-fileprint is the name of the server?

Hello , attached the screenshot for Debug.print on the file name

rf-fileprint yes server, where we keep department s share files

Not sure how to check path exists in windows explorer.

thank for replies

 

[Jeffrey Mahoney]

Open windows explorer and add the path to the address bar. If you get an error, your path is incorrect

try changing that one line in your code to this
ActiveWorkbook.SaveAs filename:=prodpath & filename, ReadOnlyRecommended:=True, FileFormat:=xlOpenXMLWorkbook

 

[rlv01]

You could also add some debug code to test the path just before this line:

ActiveWorkbook.SaveAs filename:=prodpath & filename, ReadOnlyRecommended:=True, FileFormat:=xlOpenXMLWorkbook

Example:

    ''' Begin debug code
    Dim Msg As String
    With CreateObject("Scripting.FileSystemObject")
        Msg = "prodpath: " & prodpath & vbCr
        Msg = Msg & "filename: " & Filename & vbCr
        Msg = Msg & "filepath: " & prodpath & Filename & vbCr & vbCr
        If .FolderExists(prodpath) Then
            Msg = Msg & "Folder '" & prodpath & "' exists"
            Debug.Print Msg
            MsgBox Msg, vbInformation, "Folder Verified"
        Else
            Msg = Msg & "Folder '" & prodpath & "' does not exist"
            Debug.Print Msg
            MsgBox Msg, vbCritical, "Folder Not Found"
            Exit Sub
        End If
    End With
    ''' End debug code
    
    ActiveWorkbook.SaveAs Filename:=prodpath & Filename, ReadOnlyRecommended:=True, FileFormat:=xlOpenXMLWorkbook

 

[HdCpr]

Open windows explorer and add the path to the address bar. If you get an error, your path is incorrect

try changing that one line in your code to this
ActiveWorkbook.SaveAs filename:=prodpath & filename, ReadOnlyRecommended:=True, FileFormat:=xlOpenXMLWorkbook

hello again

FileFormat:=xlOpenXMLWorkbook-- i amended , again when at the same point , excel stop responding and crash out

also tried the Window explorer which actually found the path
thank you again for advises

 

[HdCpr]

Hello Both

thank you very much for both - i had realised actually below code was causing crush

ActiveWorkbook.BreakLink Name:=CurrentFile, Type:=xlExcelLinks


removed it , and started to save to path and stopped crush

 

[smozgur]

BreakLink method's Name parameter is supposed to be the link's name as the workbook's full name including the path, not the workbook name only. I don't think the CurrentFile, even if you used Activeworkbook.FullName will work.
You can use the following code to break all links in a workbook unless you know the exact name of the link that you want to delete and use it to delete it individually:

Dim arrLinks As Variant
    arrLinks = ActiveWorkbook.LinkSources(Type:=xlLinkTypeExcelLinks)
    If Not IsEmpty(arrLinks) Then
        For Each arrLink In arrLinks
            ActiveWorkbook.BreakLink arrLink, xlLinkTypeExcelLinks
        Next arrLink
    End If

The Type parameter is also supposed to be xlLinkTypeExcelLinks, however, xlExcelLinks is another constant with the same value, 1, so it shouldn't be a problem.