VBA to save a selected area as CSV

[keranali]

Hello everyone,

Is there a VBA to save a selected number of cells as a CSV file allowing the save as dialog box to open and to allow a file location. When finished saving the CSV file opens.


Thanks
K

 

[vadius]

Hi all

Thanks for the code below which works well. One question though : How can avoid the confirmation pop up windows "Save as" (I am creating 25 files daily and have to press yes 25 times which is a bit annoying)

Thanks

I think I may have the code you're looking for:-

Option Explicit
 
Public Sub ExcelRowsToCSV()
 
  Dim iPtr As Integer
  Dim sFileName As String
  Dim intFH As Integer
  Dim aRange As Range
  Dim iLastColumn As Integer
  Dim oCell As Range
  Dim iRec As Long
 
  Set aRange = Application.InputBox("Select a range:-", , Selection.Address, , , , , Type:=8)
  iLastColumn = aRange.Column + aRange.Columns.Count - 1
 
  iPtr = InStrRev(ActiveWorkbook.FullName, ".")
  sFileName = Left(ActiveWorkbook.FullName, iPtr - 1) & ".csv"
  sFileName = Application.GetSaveAsFilename(InitialFileName:=sFileName, FileFilter:="CSV (Comma delimited) (*.csv), *.csv")
  If sFileName = "False" Then Exit Sub
 
  Close
  intFH = FreeFile()
  Open sFileName For Output As intFH
 
  iRec = 0
  For Each oCell In aRange
    If oCell.Column = iLastColumn Then
      Print #intFH, oCell.Value
      iRec = iRec + 1
    Else
      Print #intFH, oCell.Value; ",";
    End If
  Next oCell
 
  Close intFH
 
  MsgBox "Finished: " & CStr(iRec) & " records written to " _
     & sFileName & Space(10), vbOKOnly + vbInformation
 
End Sub

Any good?