none
Macro to Save Excel file using cell references to create file Save As Name and Create A Folder RRS feed

  • Question

  • Hi, would any of you kind folks out there be able to help me out creating a macro to create a file name using 2 cell references contained within a worksheet and then creating a folder name using the 2 same cell references. I have NO experience with macro's and my attempts so far, have failed miserably!!

    We use Excel 2016 and have a file called 'Customer Record.xlsm'. This file contains a number of worksheets with protected cells to prevent users from changing \ altering cell contents. The file is also a READ ONLY file.

    Sheet 2 (INFO Capture) contains 2 cells that I would like to use to create a file name and also create a folder name.

    Cell K2 contains a date

    Cell J10 contains text (a customer surname)

    The macro should create a file name in the following format 'Surname 20180423'

    The macro should create a folder name in the following format 'Surname 20180423'

    The macro should create the folder in the following location: - 

    C:\Users\TY\OneDrive - TCKRC\Jobs 1 Estimates

    The macro should execute when 'Save As' is clicked or some other keystroke.

    Thanking you in advance of finding a solution!


    Tuesday, April 24, 2018 7:23 AM

Answers

  • Hell T6NYY,

    Please try to refer to below code and adjust it for your need.

    Sub Test()
    UserNameStr = Worksheets("INFO Capture").Range("J10").Value
    DateStr = Format(Worksheets("INFO Capture").Range("K2").Value, "YYYYMMDD")
    FileNameStr = UserNameStr & " " & DateStr
    FolderNameStr = UserNameStr & " " & DateStr
    FullFolderStr = "C:\Users\terryx\Desktop\TestFolder\" & FolderNameStr
        
        'if the folder does not exist, create the folder
        Set FSO = CreateObject("Scripting.FileSystemObject")
        If FSO.FolderExists(FullFolderStr) = False Then
            MkDir FullFolderStr
        End If
    'save the document to the folder
    ActiveWorkbook.SaveAs FullFolderStr & "\" & FileNameStr
    End Sub
    

    Best Regards,

    Terry


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    • Marked as answer by T6NYY Friday, April 27, 2018 5:37 AM
    Thursday, April 26, 2018 3:24 AM
  • Terry Can’t thank you enough, couple of tweaks and the macro worked like a dream. You’re a scholar and a gentleman. Thank you, if you were a bit nearer I’d buy you a beer! Cheers Tony
    • Marked as answer by T6NYY Thursday, April 26, 2018 7:41 PM
    Thursday, April 26, 2018 7:41 PM
  • Terry Can’t thank you enough, couple of tweaks and the macro worked like a dream. You’re a scholar and a gentleman. Thank you, if you were a bit nearer I’d buy you a beer! Cheers Tony
    • Marked as answer by T6NYY Friday, April 27, 2018 5:37 AM
    Friday, April 27, 2018 5:36 AM

All replies

  • Hi,

    This is the forum to discuss questions and feedback for Microsoft Excel features, I'll move your question to the MSDN forum for Excel

    https://social.msdn.microsoft.com/Forums/en-US/home?forum=exceldev

    The reason why we recommend posting appropriately is you will get the most qualified pool of respondents, and other partners who read the forums regularly can either share their knowledge or learn from your interaction with us. Thank you for your understanding.

    Regards,

    Emi


    Please remember to mark the replies as answers if they helped. If you have feedback for TechNet Subscriber Support, contact tnsf@microsoft.com.


    Click here to learn more. Visit the dedicated forum to share, explore and talk to experts about Microsoft Teams.

    Wednesday, April 25, 2018 2:39 AM
  • Many thanks Emi

    T

    Wednesday, April 25, 2018 5:43 AM
  • Hell T6NYY,

    Please try to refer to below code and adjust it for your need.

    Sub Test()
    UserNameStr = Worksheets("INFO Capture").Range("J10").Value
    DateStr = Format(Worksheets("INFO Capture").Range("K2").Value, "YYYYMMDD")
    FileNameStr = UserNameStr & " " & DateStr
    FolderNameStr = UserNameStr & " " & DateStr
    FullFolderStr = "C:\Users\terryx\Desktop\TestFolder\" & FolderNameStr
        
        'if the folder does not exist, create the folder
        Set FSO = CreateObject("Scripting.FileSystemObject")
        If FSO.FolderExists(FullFolderStr) = False Then
            MkDir FullFolderStr
        End If
    'save the document to the folder
    ActiveWorkbook.SaveAs FullFolderStr & "\" & FileNameStr
    End Sub
    

    Best Regards,

    Terry


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    • Marked as answer by T6NYY Friday, April 27, 2018 5:37 AM
    Thursday, April 26, 2018 3:24 AM
  • Terry Can’t thank you enough, couple of tweaks and the macro worked like a dream. You’re a scholar and a gentleman. Thank you, if you were a bit nearer I’d buy you a beer! Cheers Tony
    • Marked as answer by T6NYY Thursday, April 26, 2018 7:41 PM
    Thursday, April 26, 2018 7:41 PM
  • Terry Can’t thank you enough, couple of tweaks and the macro worked like a dream. You’re a scholar and a gentleman. Thank you, if you were a bit nearer I’d buy you a beer! Cheers Tony
    • Marked as answer by T6NYY Friday, April 27, 2018 5:37 AM
    Friday, April 27, 2018 5:36 AM
  • Hi Terry, my colleagues have been very impressed with your macro and are asking for more! Might I beg your indulgence and ask if you might assist me again please.

    The macro above has: - 

    1 Created a folder called 'SURNAME YYYYMMDD', saved in the following location, C:\Users\TY\OneDrive - TCKRC\Jobs 1 Estimates

    2 Created a file called 'SURNAME YYYYMMDD.xlsm', that has been saved in the folder above.

    Within the file 'SURNAME YYYYMMDD.xlsm' there is a WORKSHEET called 'APPT LETTER'. I would like to save a copy of this worksheet as a PDF, by way of a macro.

    The name of the PDF should be YYYYMMDD SURNAME APPT.PDF

    The PDF file should be saved within the folder created previously, called 'SURNAME YYYYMMDD'

    Many thanks

    Tony

    Saturday, April 28, 2018 5:35 AM
  • Hello T6NYY,

    For export PDF, please try to use Worksheet.ExportAsFixedFormat Method.

    See.

    Sub Test()
    UserNameStr = Worksheets("INFO Capture").Range("J10").Value
    DateStr = Format(Worksheets("INFO Capture").Range("K2").Value, "YYYYMMDD")
    FileNameStr = UserNameStr & " " & DateStr
    PDFNameStr = DateStr & " " & UserNameStr & " APPT"
    FolderNameStr = UserNameStr & " " & DateStr
    FullFolderStr = "C:\Users\terryx\Desktop\TestFolder\" & FolderNameStr
        
        'if the folder does not exist, create the folder
        Set FSO = CreateObject("Scripting.FileSystemObject")
        If FSO.FolderExists(FullFolderStr) = False Then
            MkDir FullFolderStr
        End If
    ActiveWorkbook.Worksheets("APPT LETTER").ExportAsFixedFormat xlTypePDF, FullFolderStr & "\" & FolderNameStr
    ActiveWorkbook.SaveAs FullFolderStr & "\" & FileNameStr
    End Sub

    Since your original issue is resolved and the thread is closed. If you get further more issue, please post new thread for new issue.

    Thanks for understanding,

    Best Regards,

    Terry


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Saturday, April 28, 2018 5:54 AM
  • Hi Terry

    Points noted about posting. As I have encountered an issue with the second macro I didn't know whether to continue this string or post a new one and possibly lose some continuity, apologies if this is incorrect.

    Within your first macro I inadvertently asked you to produce a folder and file in the following format, SURNAME YYYYMMDD. Anyway, I modified your first macro (see below) and it now successfully creates

    a folder named 'YYYYMMDD SURNAME'

    a file named 'YYYYMMDD SURNAME.xlsm'

    Sub Test()

    UserNameStr = Worksheets("INFO Capture").Range("J10").Value

    DateStr = Format(Worksheets("INFO Capture").Range("K2").Value, "YYYYMMDD")

    FileNameStr = DateStr & " " & UserNameStr

    FolderNameStr = DateStr & " " & UserNameStr

    FullFolderStr = "C:\Users\TY\OneDrive - TCKRC\Jobs 1 Estimates\" & FolderNameStr

       

        'if the folder does not exist, create the folder

        Set FSO = CreateObject("Scripting.FileSystemObject")

        If FSO.FolderExists(FullFolderStr) = False Then

            MkDir FullFolderStr

        End If

    'save the document to the folder

    ActiveWorkbook.SaveAs FullFolderStr & "\" & FileNameStr

    End Sub

    The second macro does produce a PDF (albeit, it creates another folder in the SURNAME YYYYMMDD format and another *.xlsm file in the SURNAME YYYYMMDD format, that was my error, apologies).

    The PDF created from the second macro does not add the suffix 'APPT' to the file name.

    I have tried to modify your second macro to produce a PDF file in the YYYYMMDD SURNAME APPT.PDF format, that is saved in the YYYYMMDD SURNAME folder created from the first macro. As my macro skills are pitiful, I have failed miserably!

    Could I ask if you are able to modify the macro for me please.

    Many thanks

    Tony

    Sunday, April 29, 2018 9:47 AM