CODEWITHSUNDEEP
vbaexcelmacros
Brett Nelson
Brett Nelson

Reputation: 113

Excel Personal.xlsb saving document Macro

I have a macro to see if a cell contains this string if so executes a "save as" command with this set name standard. When I try to run the macro the if statements seem to not work. When I go through step by step it hits the if statements but saves the personal.xlsb instead of the file I'm working on. Here the code I know I have something wrong with it

Dim FName           As String
Dim FPath           As String
Dim answer          As Integer
If ActiveWorkbook.Sheets("Sheet1").Range("A1") = "String1" Then
    FPath = "C:\String1"
    FName = Sheets("Sheet1").Range("A1").Text
    If Len(FPath & "\" & FName) = 0 Then 
        answer = MsgBox("Do you want to Save File As: " & FName & "?", vbYesNo + vbQuestion, "Microsoft Excel")
        If answer = vbYes Then
            ThisWorkbook.SaveAs Filename:=FPath & "\" & FName
        End If
    Else
        ThisWorkbook.Save
    End If
End If

I am open to suggestion the most I was is to check if the file contains a string if so verify it does not already exist and if so just save instead of save as.

Upvotes: 0

Views: 411

Answers (1)

David Zemens
David Zemens

Reputation: 53623

ThisWorkbook refers to the workbook where the code resides. Presumably, the code being in the Personal.xlsb, that is why it's saving the XLSB file and not the activeworkbook.

Try this instead:

Dim FName           As String
Dim FPath           As String
Dim fullName As String
Dim rng As Range
Dim s as String
s = "String1"
With ActiveWorkbook
    Set rng = .Sheets("Sheet1").Range("A1")
    If rng.Value = s Then
        FPath = "C:\" & s
        FName = rng.Text
        fullName = FPath & "\" & FName
        If Len(fullName) = 0 Then 
            If MsgBox("Do you want to Save File As: " & FName & "?", vbYesNo + vbQuestion, "Microsoft Excel") = vbYes Then
                .SaveAs Filename:=fullName
            End If
        Else
            .Save
        End If
    End If
End With

Upvotes: 1

Related Questions