CODEWITHSUNDEEP
vbaexcelcsv
JLSmith2118
JLSmith2118

Reputation: 23

Exporting Selection to CSV

I've created a excel spreadsheet template for our customers to populate and send back to us. I want to manually select their populated data and save it as a .csv to import into another piece of software. I, first, attempted this by recording a macro. This didn't work because different customers send different numbers of records.

I've tried snippets of code from online research and came up with this.

Sub Select_To_CSV()
   Dim rng As Range
   Dim myrangearea()
   Range(ActiveCell, ActiveCell.End(xlDown).End(xlToRight)).Select


   Dim myPath As String, v
   myPath = "p:\" & _
     Format(Date, "yyyymmdd") & ".csv"
   'myPath = "x:\" & Format(Date, "yyyymmdd") & ".csv"
    v = SaveAs(myPath)
    If v <> False Then ThisWorkbook.SaveAs v
End Sub

Function SaveAs(initialFilename As String)
  On Error GoTo EndNow
  SaveAs = False
  With Application.FileDialog(msoFileDialogSaveAs)
    .AllowMultiSelect = False
    .ButtonName = "&Save As"
    .initialFilename = initialFilename
    .Title = "File Save As"
    '.Execute
    .Show
    SaveAs = .SelectedItems(1)
  End With
EndNow:
End Function

Sub Select_To_CSV()
   Dim rng As Range
   Dim myrangearea()
   Range(ActiveCell, ActiveCell.End(xlDown).End(xlToRight)).Select


   Dim myPath As String, v
   myPath = "p:\" & _
     Format(Date, "yyyymmdd") & ".csv"
   'myPath = "x:\" & Format(Date, "yyyymmdd") & ".csv"
    v = SaveAs(myPath)
    If v <> False Then ThisWorkbook.SaveAs v
End Sub

This worked really well except when I went back to look at the .csv in the folder it was the same worksheet not the selected columns.

Ultimately what I am looking to do is,

  1. Manually select the columns I want
  2. Run a macro that converts the selected columns to a .csv
  3. Have the Save As Dialog Box appear
  4. Navigate to the certain folder I want.

Upvotes: 2

Views: 6589

Answers (1)

SierraOscar
SierraOscar

Reputation: 17637

Here you go:

Sub MacroMan()

ChDrive "P:" '// <~~ change current drive to P:\
Dim copyRng As Excel.Range
Dim ThisWB  As Excel.Workbook
Dim OtherWB As Excel.Workbook
Dim sName   As String

'// set reference to the 'Master' workbook
Set ThisWB = ActiveWorkbook

'// assign selected range to 'copyRng'
Set copyRng = Application.InputBox(Prompt:="Select range to convert to CSV", Type:=8)

'// If the user selected a range, then proceed with rest of code:
If Not copyRng Is Nothing Then
    '// Create a new workbook with 1 sheet.
    Set OtherWB = Workbooks.Add(1)

    '// Get A1, then expand this 'selection' to the same size as copyRng. 
    '// Then assign the value of copyRng to this area (similar to copy/paste)
    OtherWB.Sheets(1).Range("A1").Resize(copyRng.Rows.Count, copyRng.Columns.Count).Value = copyRng.Value

    '// Get save name for CSV file.
    sName = Application.GetSaveAsFilename(FileFilter:="CSV files (*.csv), *.csv")

    '// If the user entered a save name then proceed:
    If Not LCase(sName) = "false" Then
        '// Turn off alerts
        Application.DisplayAlerts = False
        '// Save the 'copy' workbook as a CSV file
        OtherWB.SaveAs sName, xlCSV
        '// Close the 'copy' workbook
        OtherWB.Close
        '// Turn alerts back on
        Application.DisplayAlerts = True
    End If

    '// Make the 'Master' workbook the active workbook again
    ThisWB.Activate

    MsgBox "Conversion complete", vbInformation
End If

End Sub

This will allow you to manually select a range (including entire columns). It will then transfer said range onto a new sheet, save that sheet as a CSV, using the Save As dialog, and then close it afterwards.

Upvotes: 3

Related Questions