save excel in the same active file's folder.

M Zahid
M Zahid used Ask the Experts™
on
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

Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
ShumsManaging Director/Excel VBA Developer
Distinguished Expert 2018
Commented:
Hi Zahid,

Try change this line:
FilePath = ActiveWorkbook.Path
to
FilePath = ThisWorkbook.Path

Author

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)
ShumsManaging Director/Excel VBA Developer
Distinguished Expert 2018

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

ThisWorkbook will save the file only where your operating workbook is.
Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Managing Director/Excel VBA Developer
Distinguished Expert 2018
Commented:
As you already changed FilePath = ThisWorkbook.Path
then try changing this line as well;
.SaveAs FilePath & FileName & FileExtStr, FileFormat:=FileFormatNum
to
.SaveAs FilePath & "\" & FileName & FileExtStr, FileFormat:=FileFormatNum

Author

Commented:
file saved in Report folder
ShumsManaging Director/Excel VBA Developer
Distinguished Expert 2018

Commented:
Isn't this what you want?

Author

Commented:
Thx its a big help.
ShumsManaging Director/Excel VBA Developer
Distinguished Expert 2018

Commented:
You're Welcome Zahid! Glad I was able to help

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial