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
    Set Destwb = ActiveWorkbook

    With Destwb
        If Val(Application.Version) < 12 Then
            'You use Excel 97-2003
            FileExtStr = ".xls": FileFormatNum = -4143
            '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.PasteSpecial xlPasteValues
        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

M ZahidAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

ShumsExcel & VBA ExpertCommented:
Hi Zahid,

Try change this line:
FilePath = ActiveWorkbook.Path
FilePath = ThisWorkbook.Path
M ZahidAuthor Commented:
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)
ShumsExcel & VBA ExpertCommented:
Check where is your current working workbook is? Which folder?.

ThisWorkbook will save the file only where your operating workbook is.
Ensure Business Longevity with As-A-Service

Using the as-a-service approach for your business model allows you to grow your revenue stream with new practice areas, without forcing you to part ways with existing clients just because they don’t fit the mold of your new service offerings.

ShumsExcel & VBA ExpertCommented:
As you already changed FilePath = ThisWorkbook.Path
then try changing this line as well;
.SaveAs FilePath & FileName & FileExtStr, FileFormat:=FileFormatNum
.SaveAs FilePath & "\" & FileName & FileExtStr, FileFormat:=FileFormatNum

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
M ZahidAuthor Commented:
file saved in Report folder
ShumsExcel & VBA ExpertCommented:
Isn't this what you want?
M ZahidAuthor Commented:
Thx its a big help.
ShumsExcel & VBA ExpertCommented:
You're Welcome Zahid! Glad I was able to help
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
VB Script

From novice to tech pro — start learning today.