VBA to create a folder (specific folder name) and save the file with cell value and date into it.

[rebeila]

I want VBA to create a folder named as a Report + date + cell value. Then I want the excel file to be saved there as cell value and date as well. I s that possible?

THANK YOU FOR YOUR HELP

 

[Muhammad_Usman]

Yes, it's possible to create a VBA macro in Excel that will create a folder with a specific name and then save the Excel file within that folder using VBA. Here's an example VBA code that does this:

Sub CreateFolderAndSaveFile()
    Dim folderPath As String
    Dim fileName As String
    Dim cellValue As String


    ' Define the folder path and cell value
    folderPath = "C:\YourFolderPath\" ' Update with the desired path
    cellValue = Range("A1").Value ' Update with the cell reference containing the cell value


    ' Create a folder with a name that includes "Report", the current date, and the cell value
    folderPath = folderPath & "Report " & Format(Now, "yyyy-mm-dd") & " " & cellValue
    MkDir folderPath


    ' Define the file name as the cell value and the current date
    fileName = cellValue & " " & Format(Now, "yyyy-mm-dd") & ".xlsx"


    ' Save the workbook in the newly created folder
    ThisWorkbook.SaveAs folderPath & "\" & fileName


    ' Optionally, display a message
    MsgBox "Folder and file created: " & folderPath & "\" & fileName
End Sub
```

This code does the following:

1. Defines the folder path and the cell value. You should update the folder path to your desired location and specify the cell reference that contains the value you want to use.

2. Creates a folder with a name that includes "Report," the current date, and the cell value.

3. Defines the file name as the cell value and the current date.

4. Saves the current workbook in the newly created folder using the `SaveAs` method.

5. Optionally, displays a message to indicate that the folder and file have been created.

You can run this macro to create a folder and save the Excel file with the specified name and location.

 

[rebeila]

Yes, it's possible to create a VBA macro in Excel that will create a folder with a specific name and then save the Excel file within that folder using VBA. Here's an example VBA code that does this:

Sub CreateFolderAndSaveFile()
    Dim folderPath As String
    Dim fileName As String
    Dim cellValue As String


    ' Define the folder path and cell value
    folderPath = "C:\YourFolderPath\" ' Update with the desired path
    cellValue = Range("A1").Value ' Update with the cell reference containing the cell value


    ' Create a folder with a name that includes "Report", the current date, and the cell value
    folderPath = folderPath & "Report " & Format(Now, "yyyy-mm-dd") & " " & cellValue
    MkDir folderPath


    ' Define the file name as the cell value and the current date
    fileName = cellValue & " " & Format(Now, "yyyy-mm-dd") & ".xlsx"


    ' Save the workbook in the newly created folder
    ThisWorkbook.SaveAs folderPath & "\" & fileName


    ' Optionally, display a message
    MsgBox "Folder and file created: " & folderPath & "\" & fileName
End Sub
```

This code does the following:

1. Defines the folder path and the cell value. You should update the folder path to your desired location and specify the cell reference that contains the value you want to use.

2. Creates a folder with a name that includes "Report," the current date, and the cell value.

3. Defines the file name as the cell value and the current date.

4. Saves the current workbook in the newly created folder using the `SaveAs` method.

5. Optionally, displays a message to indicate that the folder and file have been created.

You can run this macro to create a folder and save the Excel file with the specified name and location.

Thanks for hlep!! Unfortunately I get runtime error 10014 for this part of the code: ThisWorkbook.SaveAs folderPath & "\" & fileName.

Maybe the problem is because I added this: Sheets("Data1").Range("N3").Value. Because I have more sheets than 1.

 

[rebeila]

Thanks for hlep!! Unfortunately I get runtime error 10014 for this part of the code: ThisWorkbook.SaveAs folderPath & "\" & fileName.

Maybe the problem is because I added this: Sheets("Data1").Range("N3").Value. Because I have more sheets than 1.

Hey, I did some more digging around and I think the problem is either or both: saving the file with the same name as the folder, or/and saving macro enabled file.