Avatar of M Zahid
M Zahid
Flag for United Arab Emirates asked on

save excel in the same active file's folder.

Hi Guys,

trying to save the file in current folder but not working, its keep saving in (My Documents) or wherever it likes :P

can anyone help plzz?

Private Sub CommandButton1_Click()

    Dim FileExtStr As String
    Dim FileFormatNum As Long
    Dim Sourcewb As Workbook
    Dim Destwb As Workbook
    Dim FilePath As String
    Dim FileName As String

    With Application
        .ScreenUpdating = False
        .EnableEvents = False
    End With

    Set Sourcewb = ActiveWorkbook

    'Copy the sheet to a new workbook
    ActiveSheet.Copy
    Set Destwb = ActiveWorkbook


    With Destwb
        If Val(Application.Version) < 12 Then
            'You use Excel 97-2003
            FileExtStr = ".xls": FileFormatNum = -4143
        Else
            'You use Excel 2007-2016
                Select Case Sourcewb.FileFormat
                Case 51: FileExtStr = ".xlsx": FileFormatNum = 51
                Case 52:
                    If .HasVBProject Then
                        FileExtStr = ".xlsm": FileFormatNum = 52
                    End If
                Case 56: FileExtStr = ".xls": FileFormatNum = 56
                Case Else: FileExtStr = ".xlsb": FileFormatNum = 50
                End Select
            End If
    End With

        'Change all cells in the worksheet to values if you want
        With Destwb.Sheets(1).UsedRange
            .Cells.Copy
            .Cells.PasteSpecial xlPasteValues
            .Cells(1).Select
        End With
        Application.CutCopyMode = False

    'Save the new workbook and close it
    FilePath = ActiveWorkbook.Path
    FileName = "" & Sourcewb.Name
    'filesavename = ActiveWorkbook.Name
    
    With Destwb
        .SaveAs FilePath & FileName & FileExtStr, FileFormat:=FileFormatNum
        .Close SaveChanges:=False
    End With

    MsgBox "You can find the new file in " & FilePath

    With Application
        .ScreenUpdating = True
        .EnableEvents = True
    End With

End Sub

Open in new window

VB ScriptMicrosoft OfficeVBAMicrosoft Excel

Avatar of undefined
Last Comment
Shums Faruk

8/22/2022 - Mon
SOLUTION
Shums Faruk

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
M Zahid

ASKER
Hello Shums

its saving in now one folder before e.g this file folder is ( C:\1. D Drive\2 - Wrokshop\2 - Working\Report) but saving in (C:\1. D Drive\2 - Wrokshop\2 - Working)
Shums Faruk

Check where is your current working workbook is? Which folder?.

ThisWorkbook will save the file only where your operating workbook is.
ASKER CERTIFIED SOLUTION
Shums Faruk

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
M Zahid

ASKER
file saved in Report folder
Your help has saved me hundreds of hours of internet surfing.
fblack61
Shums Faruk

Isn't this what you want?
M Zahid

ASKER
Thx its a big help.
Shums Faruk

You're Welcome Zahid! Glad I was able to help
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.