Share via

Save newly created file in specific folder

Anonymous
2020-06-17T16:40:29+00:00

Hello!

I've a macro which creates a new file and saves in the same folder as the macro is stored. Now I want to store it in another folder with path: 

C:\Users\debi.w\Documents\M Files

Can you tell me the changes required to be done in below code?

#CODE

CSVname = "XXYY" & Format(Date, "YYYYMMDD") & "INTRADAY" & ".csv"

Flagname = "XXYYCOMPLETE." & Format(Date, "YYYYMMDD") & "NEW"

CSVPath = Template.Path & "" & CSVname 

FlagPath = Template.Path & "" & Flagname

'''''''''''''''''''''' To Create and save the Flag excel and the CSV file '''''''''''''''''''''''''''''''''''''''''''

Set XLfile = Workbooks.Add

Set CSVfile = Workbooks.Add

With Template

        ScrappedSh.Name = "XXYYCOMPLETE-" & Format(Date, "YYYYMMDD")

        ScrappedSh.Name = "XXYY-" & Format(Date, "YYYYMMDD")

        .Sheets(ScrappedSh.Name).Copy Before:=CSVfile.Worksheets("Sheet1")

        ScrappedSh.Name = "Raw Data"

End With

XLfile.SaveAs Filename:=FlagPath, FileFormat:=xlOpenXMLWorkbook

XLfile.Close

CSVfile.SaveAs Filename:=CSVPath, FileFormat:=xlCSV

CSVfile.Close

Microsoft 365 and Office | Excel | For home | Windows

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments

Answer accepted by question author

  1. Anonymous
    2020-06-17T17:49:58+00:00

    Hi Debbie

    Replace these two lines

    CSVPath = Template.Path & "" & CSVname 

    FlagPath = Template.Path & "" & Flagname

    with

    CSVPath = "C:\Users\debi.w\Documents\M Files" & "" & CSVname 

    FlagPath = "C:\Users\debi.w\Documents\M Files" & "" & Flagname

    So try

    ***********************************************************************************

    CSVname = "XXYY" & Format(Date, "YYYYMMDD") & "INTRADAY" & ".csv"

    Flagname = "XXYYCOMPLETE." & Format(Date, "YYYYMMDD") & "NEW"

    CSVPath = "C:\Users\debi.w\Documents\M Files" & "" & CSVname 

    FlagPath = "C:\Users\debi.w\Documents\M Files" & "" & Flagname

    '''''''''''''''''''''' To Create and save the Flag excel and the CSV file '''''''''''''''''''''''''''''''''''''''''''

    Set XLfile = Workbooks.Add

    Set CSVfile = Workbooks.Add

    With Template

            ScrappedSh.Name = "XXYYCOMPLETE-" & Format(Date, "YYYYMMDD")

            ScrappedSh.Name = "XXYY-" & Format(Date, "YYYYMMDD")

            .Sheets(ScrappedSh.Name).Copy Before:=CSVfile.Worksheets("Sheet1")

            ScrappedSh.Name = "Raw Data"

    End With

    XLfile.SaveAs Filename:=FlagPath, FileFormat:=xlOpenXMLWorkbook

    XLfile.Close

    CSVfile.SaveAs Filename:=CSVPath, FileFormat:=xlCSV

    CSVfile.Close

    ************************************************************************************************

    I hope this helps you

    Regards

    Jeovany

    1 person found this answer helpful.
    0 comments

0 additional answers

Sort by: Most helpful
Most helpful Newest Oldest