VBA excel CSV export skip first row

[NLSSS]

Hi everybody,

Below is the VBA code i use behind a button to export the current sheet to a semicolon ( separated txt file. The code works fine as it is, but it also exports the headers in the excel sheet, and I don't want this. I'm new to VBA and I'm quite happy I got the below code to work but it would be better if the header would be skipped. I have tried different things, but can't get it to work. can you please help me with this? I need to use the below code to make sure the separation is always done by semicolon (this sheet is used on multiple computers with different local settings). I cannot use the local separator as it will be a semicolon for me, but a comma for a colleague.

Thank you in advance.



Private Sub CommandButton1_Click()
Dim rCell As Range
Dim rRow As Range
Dim sOutput As String
Dim sFname As String, lFnum As Long

sFname = ThisWorkbook.Path & "\MyFile6.txt"
lFnum = xlCSV

Open sFname For Output As lFnum
For Each rRow In ActiveSheet.UsedRange.Rows
For Each rCell In rRow.Cells
sOutput = sOutput & rCell.Text & ";"
Next rCell
sOutput = Left(sOutput, Len(sOutput) - 1)
Print #lFnum, sOutput
sOutput = ""
Next rRow
End Sub

 

[Joe4]

Welcome to the Board!

Just add a row counter to your code, and have it skip the create/export part for row 1, i.e.

Private Sub CommandButton1_Click()


    Dim rCell As Range
    Dim rRow As Range
    Dim sOutput As String
    Dim sFname As String, lFnum As Long
    Dim rowCount As Long


    sFname = ThisWorkbook.Path & "\MyFile6.txt"
    lFnum = xlCSV
    rowCount = 1


    Open sFname For Output As lFnum
    For Each rRow In ActiveSheet.UsedRange.Rows
        If rowCount > 1 Then
            For Each rCell In rRow.Cells
                sOutput = sOutput & rCell.Text & ";"
            Next rCell
            sOutput = Left(sOutput, Len(sOutput) - 1)
            Print #lFnum, sOutput
            MsgBox rRow.Row & vbCrLf & sOutput
            sOutput = ""
        End If
        rowCount = rowCount + 1
    Next rRow


End Sub

 

[NLSSS]

Wow that was quick, and it works, excellent!

I do have another question, if I should open a new topic let me know.
When i use the export button on a sheet with, for example, 100 rows, only 50 rows are exported to the text file. The next 50 rows are only exported after the excelsheet is closed. Do you have an idea how this is possible?

Thanks in advance again!

 

[Joe4]

Maybe try adding this after the Next rRow line:

Close #lFnum

 

[NLSSS]

Hi Joe4,

Thank you too for your quick response, this solution also works, my code is now as I want it, thanks!