VBA for save file to CVS format.

[MrRajKumar]

Hello Experts.

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
Save As file in CSV format in the below location
C:\Users\RajKum\My Documents\Converted.

( If converted folder is not in My Documents needs to create this )

I tried to record a macro, I couldn't figure this out. It needs to check the Document folder for the Converted folder.

How can I do this.

Appreciate all your help

Raj

EDIT: Also please needs to have a pop up for the file name.

 

[cgcamal]

Hi MrRajKumar,

For the part you need to create thr folder converted if not exist
you can try something like this:

Sub Test()
Dim MyPath As String

MyPath = "C:\Users\RajKum\My Documents\Converted"

On Error Resume Next [COLOR=SeaGreen]'To handle error If folder already exists [/COLOR]
    
MkDir MyPath[COLOR=SeaGreen] 'Creating the path you need
[/COLOR]
    ActiveWorkbook.SaveAs Filename:=MyPath & "\MyFile.xls", FileFormat:=xlExcel8, _
        Password:="", WriteResPassword:="", ReadOnlyRecommended:=False, _
        CreateBackup:=False
End Sub

Hope this helps.

 

[MrRajKumar]

Thank you for your reply.

But I am newbie in VBA. How can I include all the condition together also a inputbox for the file name? Also file should be save on CSV.

Appreciate the help.

Raj

 

[cgcamal]

MrRajKumar,

I'm not sure if it is what you want. This macro needs you to change the path from where you will want to
open the inputfile, in addition you need to enter complete file name with extension(e.g "My file name.xls")

Sub Test()
Dim MyPathFrom As String, MyPathTo As String
Dim MyFile As String

Set wf = WorksheetFunction

MyPathFrom = "C:\Users\RajKum\My Documents\From"
MyPathTo = "C:\Users\RajKum\My Documents\Converted"

MyFile = InputBox(Prompt:="Enter file name please.", Title:="ENTER FILE NAME")

Workbooks.Open Filename:=MyPathFrom & "\" & MyFile

MyFile = "\" & Left(MyFile, wf.Search(".", MyFile, 1) - 1) [COLOR=Green]'Getting only file name without extension[/COLOR]

On Error Resume Next[COLOR=Green] 'To handle error If folder already exists[/COLOR]
   
MkDir MyPathTo 'Creating the path you need

Application.DisplayAlerts = False [COLOR=Green]'To avoid the Excel promts when you save a file as CSV[/COLOR]

    ActiveWorkbook.SaveAs Filename:=MyPathTo & MyFile, FileFormat:=xlCSV, CreateBackup:=False
    
Application.DisplayAlerts = True [COLOR=Green] 'Turning on again Display alerts[/COLOR]
End Sub

Regards