Export range of data as csv file

[wjeil]

Hello,

I have some data in a worksheet which i want to export as a csv file. Everything except the first row must be exported as an csv file, with a pop-up to save the file. The result must be like the second table, thus with a comma spearating the 2 columns. How do I do this? It also is a dynamic range so the last row can differ.

Config7.8.xlsm
	A	B
1	OrderID	SKU_ID
2	Order 1	A2
3	Order 2	B26
4	Order 3	A1
5	Order 4	A9
6	Order 4	A20
7	Order 5	B16
8	Order 6	A3
9	Order 7	A24
10	Order 7	A20
11	Order 7	A19
12	Order 7	B26
13	Order 7	A1
14	Order 7	A6
15	Order 8	A11
16	Order 9	A13
Input_File

350_Test.xlsx
	E
1	Order 1,A2
2	Order 2,B26
3	Order 3,A1
4	Order 4,A9
5	Order 4,A20
6	Order 5,B16
7	Order 6,A3
8	Order 7,A24
9	Order 7,A20
10	Order 7,A19
11	Order 7,B26
12	Order 7,A1
13	Order 7,A6
14	Order 8,A11
15	Order 9,A13
Blad1

Cell Formulas
Range	Formula
E1:E15	E1	=A1&","&B1

I would really appreciate if someone can help me out.

 

[rpaulson]

Sub Create_Txt_File()

myfile = "c:\test\test.csv"
If Dir(myfile) <> "" Then Kill myfile ' deletes file if it exists

For r = 2 To Cells(Rows.Count, "A").End(xlUp).Row
    lcol = Cells(r, 256).End(xlToLeft).Column
    For c = 1 To lcol
        delim = ""
        If c < lcol Then delim = ","
        data = data & Cells(r, c) & delim
    Next c
        data = data & vbLf
 Next r
   
Open myfile For Append As #1
Print #1, data
Close #1
data = ""

End Sub

 

[wjeil]

Thanks! Only 1 adjustment, I only want to save column A and B (there are more columns active in C,D and E). Now they are all included. And how can i choose the save location with a pop-up screen? So that the directory can be chosen.?

Thanks in advance!

 

[Marc L]

Hi, first that no needs to use Blad1 worksheet …​

​

What is strange according to your expected csv text file is there is no header within, really ?​

 

[Marc L]

So you can delete the useless Blad1 worksheet and use this VBA demonstration as a starting point :​

Sub Demo1()
    Dim V, F%, R&
        V = Application.GetSaveAsFilename(, "Text files,*.csv", , "File to save"):  If V = False Then Exit Sub
        F = FreeFile
        Open V For Output As #F
    With [Input_File!A1].CurrentRegion.Columns("A:B").Rows
        For R = 2 To .Count:  Print #F, Join(Application.Index(.Item(R).Value2, 1, 0), ","):  Next
    End With
        Close #F
End Sub

 

[rpaulson]

wjeil give this a try. it allows you to change how many columns you want.

it allows the user to select the save to folder

Sub Create_Txt_File_2Columns()

    Set fldr = Application.FileDialog(msoFileDialogFolderPicker)
    With fldr
        .Title = "Select a Folder"
        .AllowMultiSelect = False
        .InitialFileName = Application.DefaultFilePath
        If .Show <> -1 Then Exit Sub
        sItem = .SelectedItems(1)
    End With


myfile = sItem & "\test.csv" 'adjust this name as needed

If Dir(myfile) <> "" Then Kill myfile ' deletes file if it exists

For r = 1 To Cells(Rows.Count, "A").End(xlUp).Row
    lcol = 2 'number of columns wanted
   
    For c = 1 To lcol
        delim = ""
        If c < lcol Then delim = ","
        data = data & Cells(r, c) & delim
    Next c
        data = data & vbLf
 Next r
  
Open myfile For Append As #1
Print #1, data
Close #1
data = ""

End Sub

 

[Marc L]

​

As a reminder Print #F, Join(Application.Index(.Item(R).Value2, 1, 0), ",") is universal whatever the Excel version​

​

but as recent versions have the TEXTJOIN worksheet function so it could be Print #F, Application.Textjoin(",", False, .Item(R)) …​