I was in the midst of working on a project recently to import a TXT file, run a series of sub routines on the data in order to do basic functions such as removing trailing spaces, organize and sort the data, perform a couple of integrity checks, and quickly export it back to the user for further analysis.
The end goal was to have some of the sheets exported from the Excel workbook and then saved separately. This way I could separate the data processing from the data analysis steps and distribute the workload across the team.
The data analysis portion was pretty straightforward, but sifting through the various file dialog (fd) boxes and then allowing the user to select a location to save the extracted worksheets was proving to be more cumbersome than expected.
Ultimately through a few trials and testing different methods on the web, the following is where the code arrived.
Function SaveSheetByName(ByVal SheetNames As Variant, ByVal SaveName As String) As String 'This function will save the sheets passed in by when calling the function. 'You are able to pass in multiple sheets at once, they just need to be an array Dim EndOfFIleName As String Dim strSaveName As String Dim intChoice as Integer MsgBox ("Next: Choose a location to save results") Application.FileDialog(msoFileDialogFolderPicker).Title = _ "Choose a location to save the exported file" saveLocation = Application.FileDialog(msoFileDialogFolderPicker).Show 'Determine what choice the user made and retrieve the file path selected by the user If saveLocation <> 0 Then SavePath = Application.FileDialog(msoFileDialogFolderPicker).SelectedItems(1) & "\" End If 'Defines the timestamp for file name - this makes it easy to find the files later EndOfFIleName = Month(Now) & "_" & Day(Now) & _ "_" & Year(Now) & "@" & Hour(Now) & "-" & Minute(Now) 'Concatenates the string that is the file name as well as path and ending strSaveName = SavePath & SaveName & " " & EndOfFIleName 'Passes the SheetNames Array and each sheet is copied Sheets(SheetNames).Copy ActiveWorkbook.SaveAs Filename:=strSaveName, FileFormat:=xlWorkbookDefault, _ ReadOnlyRecommended:=False ActiveWorkbook.Close 'This is the returned value that can be used to return a message to the user SaveSheetByName = "The file: " & SaveName & " " & "was saved to: " & SavePath End Function
A few things to note about the above code:
- SheetNames should be passed in as an array with either one or more sheets listed. This is because Sheet() accepts arrays
- There is no option to change the sheet name through this process, so if you want to change the sheet prior to saving, I would suggest changing it before adding it to the array
- The file name is set automatically for the user. The purpose of the message box popping up early on is because it lets the user know what is going on next
- The string that is returned to the SaveSheetByName is useful if you are looking to display a message at the end of the sub routine that calls this overall function
In order to call the function here is the associated code:
SavedFile = SaveSheetByName(Array("Sheet 1"), "Sheet 1 from Workbook") MsgBox ("The file(s) have been exported" & vbNewLine & SavedFile)
The MsgBox at the end enables you to pass in the SavedFile variable for a message back to the user. If you for instance wanted to save two files, I would suggest changing the variable to SavedFile1 and SavedFile2 and then just putting more vbNewLine’s between each variable to make it a little more reader friendly when displaying the MsgBox such as:
SavedFile1 = SaveSheetByName(Array("Sheet 1"), "Sheet 1 from Workbook") SavedFile2 = SaveSheetByName(Array("Sheet 2"), "Sheet 2 from Workbook") MsgBox ("The file(s) have been exported" & vbNewLine & SavedFile1 _ & vbNewLine & SavedFile2)
Questions, comments, and feedback are welcome!
Leave a Reply