CODEWITHSUNDEEP
excelvba
duncan297
duncan297

Reputation: 31

Export a worksheet range to csv using excel-vba

I am exporting from Excel to a CSV by macro using this to name the file specifically from data in the worksheet in cells in cells that will form no part of the CSV data, only the file name:

Private Sub CommandButton1_Click()
Dim Path As String
Dim FileName1 As String
Dim FileName2 As String
Dim FileName3 As String
Path = "T:\Richards Reports\MG Orders\"
FileName1 = Range("A1")
FileName2 = Range("O1")
FileName3 = Range("M1")
ActiveWorkbook.SaveAs FileName:=Path & FileName1 & "_" & FileName2 & "_" &       FileName3 & ".txt", FileFormat:=xlCSV
End Sub

However, I need to be able to limit the output to a specific range, for example cells I6 to I60, I am struggling to find a way of accomplishing this, any suggestions appreciated. TIA Duncan

Upvotes: 3

Views: 9342

Answers (2)

Michał Turczyn
Michał Turczyn

Reputation: 37367

One way would be concatenate values in cells (with comma between) and save it manually:

Dim content As String
Dim rng As Range
Set rng = Range("A1:E2")

For Each cell In rng
    content = content & "," & cell.Value
    'if we go to another row insert semicolon
    If cell.Column = rng.Columns.Count Then
        content = content & ";"
    End If
Next cell

content = Right(content, Len(content) - 1) 'remove unnecessary comma at the beginning

Set FSO = CreateObject("Scripting.FileSystemObject")
'don't forget to insert your file path here
Set wfile = FSO.CreateTextFile("YourPathHere", 2) 'connection for writing

wfile.WriteLine content
wfile.Close

Here I used comma (,) as field separator and semicolon (;) as line separator, you can change it as you want. Also, set range to the one you want to save.

Upvotes: 1

avb
avb

Reputation: 1753

Here is the code to save selected range to .csv

Sub saveSelection2csv()
Dim range2save As Range
Dim filename As Range
Dim dataRow As Range
Dim dataRowArr() As Variant

    Set filename = Worksheets("Arkusz1").Range("A1")

    Open ThisWorkbook.Path & "\" & filename.Value & ".csv" For Output As #1

    For Each dataRow In Selection.Rows
        dataRowArr = dataRow.Value
        dataRowArr = Application.Transpose(Application.Transpose(dataRowArr))
        Print #1, Join(dataRowArr, ",")
    Next

    Close #1
End Sub

Upvotes: 2

Related Questions