Save File In CSV Format VBA

[Raj Kumar]

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]

Hi Raj Kumar,

Welcome to ExcelFox!

Try


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

[Raj Kumar]

Thank you, Admin.

Works like a charm...! :thumbsup:

[Admin]

Hi Raj Kumar,

You are welcome !

Glad I could help you. :cheers: