Create new CSV file from XLS file

ntosi01

New Member
Joined
Dec 15, 2009
Messages
2
Hi all,
I have a file in xls format and I need to convert it to csv format, but I want it to be a new file and want to be able to specify the name of the new file with a message box. The csv file needs to concatenate all columns of data so that only column A has data. For example the new file will only have data in column A. Cell (A1) would be A1 & "," & A2 & " ," & A3....etc. Please help. I appreciate any thoughts or ideas. I am not much of a programmer so the more information the better. Thanks.;)
 

Some videos you may like

Excel Facts

Pivot Table Drill Down
Double-click any number in a pivot table to create a new report showing all detail rows that make up that number

id107

Well-known Member
Joined
Apr 16, 2008
Messages
578
Is this what you need?
Code:
Sub test()
 
    Dim MyFile As String, i As Long, LastRow As Long, SaveDir As String
 
'Set the directory to save the file to. Leave the "\" at the end.
    SaveDir = "M:\Excel Test\"
    MyFile = InputBox("Please input name to save file as")
    LastRow = Cells(Rows.Count, "A").End(xlUp).Row
 
    ReDim MyArray(LastRow) As Variant
 
    For i = 1 To LastRow
        If i = LastRow Then
            MyArray(i - 1) = Range("A" & i).Value
        Else
            MyArray(i - 1) = Range("A" & i).Value & ","
        End If
    Next i
 
    Range("A:A").Clear
 
    Range("A1").Value = Join(MyArray)
 
    ActiveWorkbook.SaveAs Filename:=SaveDir & MyFile & ".csv"
 
End Sub
 

ntosi01

New Member
Joined
Dec 15, 2009
Messages
2
Thanks for the code. I think I am on the right track with that information. Let me give it another try and see what I can come up with. The only issue I have is that I need to do what is done in Cell A1 through Cell A150 (or what ever the end of the range is for that particular file). Thanks.
 

Watch MrExcel Video

Forum statistics

Threads
1,122,567
Messages
5,596,904
Members
414,110
Latest member
docops

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top