Create new CSV file from XLS file

[ntosi01]

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.

 

[id107]

Is this what you need?

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]

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.