SaveAs macro bug needs fixing

[kelly mort]

I am stacked with this code and I need someone to fix a few bugs for me. I have observed that when I cancel the dialogue box, then the workbook starts to behave some weird way. For instance, there is an image control on one of my worksheets that I load an image to from a userform – it fails to load the image when I cancel that dialogue until I re-open the workbook. But when I succeed in creating the saveAs copy, it works very fine. In a way trying to fix it, I am looking at the steps below. Meanwhile, if there is a better and cooler way to get what I am struggling with, then I am very much interested in knowing that cool trick.




The thing is, I don’t really know what goes on when I access the “file save as” dialogue.


I am thinking of saving it with the name of the active workbook. But since I don’t really know what is happening, I can not figure the algorithm out yet.


So I tried saving it with the same name of the workbook, which gave me option to overwrite the name since it already existed. I thought I suppressed the alert but it came up. So I decided to kill the file if it exists and then save it. But that too raised an error saying I have been denied permission to do so. Now I have no other option than to come here and call on tech support.

Thanks for reading and have a nice time

ThisWorkbook.Save
    With Application
        ChDrive ThisWorkbook.Path
        ChDir ThisWorkbook.Path
        .EnableEvents = False
    FileSaveAs = .GetSaveAsFilename(FileFilter:="Exel Files(*.xlsm),*.xlsm", _
                  Title:="Add name to your new file")
        If FileSaveAs <> "False" Then
            ThisWorkbook.SaveAs FileName:=FileSaveAs, _
            Password:="", writerespassword:="", ReadOnlyRecommended:=False
        Else
            MsgBox "Operation aborted", vbInformation
    If Dir(ThisWorkbook.Path & "\" & ThisWorkbook.Name) <> "" Then
                Kill ThisWorkbook.Path & "\" & ThisWorkbook.Name
            End If
            ThisWorkbook.SaveAs FileName:=ThisWorkbook.Name, _
            Password:="", writerespassword:="", ReadOnlyRecommended:=False
            ThisWorkbook.Save
            .EnableEvents = True
            Exit Sub
        End If
        .EnableEvents = True
    End With

 

[dmt32]

Hi,
try

Dim FileSaveAs As Variant
    
    ChDrive ThisWorkbook.Path
    ChDir ThisWorkbook.Path
    
    
    FileSaveAs = Application.GetSaveAsFilename(FileFilter:="Exel Files(*.xlsm),*.xlsm", _
                                                Title:="Add name to your new file")
    
    If FileSaveAs <> False Then
    
        With Application
            .DisplayAlerts = False
            ThisWorkbook.SaveAs Filename:=FileSaveAs, FileFormat:=52, _
                                Password:="", writerespassword:="", _
                                ReadOnlyRecommended:=False
            .DisplayAlerts = True
        End With
    
    Else
    
        MsgBox "Operation aborted", vbInformation, "Save Aborted"
    
    End If

Dave

 

[kelly mort]

Hi,
try

Dim FileSaveAs As Variant
    
    ChDrive ThisWorkbook.Path
    ChDir ThisWorkbook.Path
    
    
    FileSaveAs = Application.GetSaveAsFilename(FileFilter:="Exel Files(*.xlsm),*.xlsm", _
                                                Title:="Add name to your new file")
    
    If FileSaveAs <> False Then
    
        With Application
            .DisplayAlerts = False
            ThisWorkbook.SaveAs Filename:=FileSaveAs, FileFormat:=52, _
                                Password:="", writerespassword:="", _
                                ReadOnlyRecommended:=False
            .DisplayAlerts = True
        End With
    
    Else
    
        MsgBox "Operation aborted", vbInformation, "Save Aborted"
    
    End If

Dave

Great!!!!!!

Problem solved. Thanks and have a nice time