ActiveWorkbook.SaveAs fileName-Crashing Excel

HdCpr

New Member
Joined
Aug 8, 2021
Messages
24
Office Version
  1. 365
Platform
  1. MacOS
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
 

Attachments

  • Capture.PNG
    Capture.PNG
    148.9 KB · Views: 20
  • Capture-1.PNG
    Capture-1.PNG
    90.7 KB · Views: 21

Excel Facts

Get help while writing formula
Click the italics "fx" icon to the left of the formula bar to open the Functions Arguments dialog. Help is displayed for each argument.
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?
 
Upvote 0
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 :)
 

Attachments

  • Capture--.PNG
    Capture--.PNG
    76.8 KB · Views: 15
Upvote 0
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
 
Upvote 0
You could also add some debug code to test the path just before this line:
VBA Code:
ActiveWorkbook.SaveAs filename:=prodpath & filename, ReadOnlyRecommended:=True, FileFormat:=xlOpenXMLWorkbook

Example:
VBA Code:
    ''' 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
 
Upvote 0
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
 

Attachments

  • Capture.PNG
    Capture.PNG
    24.4 KB · Views: 4
  • Capture-1.PNG
    Capture-1.PNG
    17.3 KB · Views: 4
  • Capture.PNG
    Capture.PNG
    24.4 KB · Views: 4
  • Capture-1.PNG
    Capture-1.PNG
    17.3 KB · Views: 3
Upvote 0
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
 

Attachments

  • Capture.PNG
    Capture.PNG
    45.5 KB · Views: 12
Upvote 0
Solution
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:

VBA Code:
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.
 
Upvote 0

Forum statistics

Threads
1,213,536
Messages
6,114,213
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top