In some cases it may be necessary to save an individual worksheet as a CSV file within a directory. In this article we will show you how to quickly and easily install code that will allow you to select a sheet from a drop down list and convert it to a new CSV at the push of a button.
The code is designed to name the file based upon the name of the sheet and the date and time in which the sheet is converted to a CSV which will ensure that it produces the unique filename.
The instructions provided in this article are designed to be suitable for somebody that does not know anything about VBA so don’t worry if you haven’t used VBA before.
Setting Up Your Excel File To Save CSV’s
To setup your CSV file there are a couple of things that need to be done.
Step 1: Create a new Excel spreadsheet and save it as an Macro-Enabled Workbook. Please note that this is not the default setting for excel.
Step 2: Go into the Visual Basic Editor by pressing ALT + F11
Step 3: Create a new module By selecting insert and then module. the module will appear on the left-hand side of the screen and is circled in red.
Step 4: Select the module that has just been created and copy the code below into the module.
Sub SavingSheet_As_A_CSV() Dim ws As Worksheet Dim sFileName As String Dim WB As Workbook Dim Sheetname Application.DisplayAlerts = False 'gets the Names of each sheet and places it into the combobox For Each ws In Worksheets UserForm1.ComboBox1.AddItem ws.Name Next ws 'shows userform to get user input UserForm1.Show Sheetname = UserForm1.ComboBox1.Value 'create file name with sheet name date and time sFileName = Sheetname & " " & Day(Date) & "-" & Month(Date) & "-" & Year(Date) & " " & Hour(Time) & "h-" & Minute(Time) & "m" & ".csv" 'Copy the contents of required sheet ready to paste into the new CSV Worksheets(Sheetname).Range("A:Z").Copy 'Define your own range 'Open a new XLS workbook, save it as the file name Set WB = Workbooks.Add 'pastes in data into sheet and closes file With WB .Title = "MyTitle" .Subject = "MySubject" .Sheets(1).Select ActiveSheet.Paste .SaveAs "C:\Users\server\OneDrive\Documents\test\" & sFileName, xlCSV .Close End With Application.DisplayAlerts = True End Sub
Step 5: The location where the files will be saved needs to be modified within the code to suit the location where you want to save the files. This line of code is highlighted in bold above.
If you are not 100% sure what the file path is for your location the easiest way to find it is to right click on a file that is already stored in the location where you want the files stored and select properties.
The file path can be copied from the information provided.
IMPORTANT: The file path provided must have an additional backlash added at the end. For the example below the path provide is C:\Users\server\OneDrive\Documents\test. The path that needs to be added to the code is C:\Users\server\OneDrive\Documents\test\.
Step 6: For the code to work as intended a userform needs to be added to the file to allow the selection to be made. To add a userform select insert and then userform.
Step 7: Add a combobox to the userform then by selecting the combobox icon from the toolbox and dragging on to the toolbox.
Step 8: Add a command button to the userform by dragging the icon onto the userform.
Step 9: This step is optional and is purely added for the benefit of the user. Add a label and modified comment
Step 10: Double click on the button that has been created and insert the code below. Once this step is complete the code is ready to run.
How To Loop Across Columns In VBA
How To Apply An If Statement If The Cell Only Contains A Partial Match To Text (With And Without VBA)
How Do I Combine Two Macros? (With And Without VBA)
How Format A Cell In VBA? A Simple Way To Find The Code?