Save File In CSV Format VBA

**Raj Kumar** · 05-31-2011, 06:17 AM

I am using EXCEL 2007 in windows 7.

I have a simple file with 2 worksheets. Sheet1 & Sheet2.

I need to do these things via VBA.

Copy Sheet2!A1:C500 Paste Special Values only.
Delete Sheet2!1:1 row 1 whole row
Delete Sheet1
Ask an Inputbox for the file name
Save As file in CSV format in the below location
C:\Users\RajKum\My Documents\Converted.

Needs to create Converted folder, if not exists.

Appreciate all your help

Raj

**Admin** · 05-31-2011, 08:38 AM

Hi Raj Kumar,

Welcome to ExcelFox!

Try

Code:

Sub SaveAsCSV()
    
    Dim CSVFileName     As String
    Dim wbkActive       As Workbook
    Dim wbkNew          As Workbook
    Dim i               As Long
    Dim blnFolderExists As Boolean
    Dim FilePath        As String
    
    FilePath = "C:\Users\RajKum\My Documents\Converted\"
    
    With Application
        .ScreenUpdating = 0
        .DisplayAlerts = 0
    End With
    
    Set wbkActive = ThisWorkbook
    Set wbkNew = Workbooks.Add
    
    wbkNew.Worksheets(1).Range("a1:c499").Value = wbkActive.Worksheets(2).Range("a2:c500").Value
    
    CSVFileName = Application.InputBox("Enter the New CSV File Name", Type:=2)
    
    Application.DisplayAlerts = 0
    For i = wbkNew.Worksheets.Count To 2 Step -1
        wbkNew.Worksheets(i).Delete
    Next
    
    blnFolderExists = CBool(Len(Dir(FilePath, vbDirectory)))
    
    If Not blnFolderExists Then
        MkDir FilePath
    End If
    
    If Right$(FilePath, 1) <> "\" Then FilePath = FilePath & Application.PathSeparator
    
    wbkNew.SaveAs Filename:=FilePath & CSVFileName, FileFormat:=6 '"CSV"
    wbkNew.Close
    
    Set wbkNew = Nothing
    Set wbkActive = Nothing
    
    With Application
        .DisplayAlerts = 1
        .ScreenUpdating = 1
    End With

End Sub