Is there a way to Print a range in a for output that is a string variable?

user_14720005 0
Viewed 50

I'm attempting to export this section of an excel sheet into a txt file. n_select determines how many rows have data and need to be exported. I've tried making a Range from n_select, but then it gives me a type mismatch error.

Private Sub Exporter_Click()
    Dim n_personne As Integer
    Dim n_select As String
    Dim user As String
    Dim fileName As String

    n_personne = 1
    user = Environ("username")
    fileName = "Résultats Pêchés"
    
    'DETERMINER LE NOMBRE DE RANGS DONNERS
    Do Until IsEmpty(Worksheets("output").Cells(n_personne, 1).Value) = True
        n_personne = n_personne + 1
    Loop

    'DETERMINER L'INTERVALLE A EXPORTER
    n_select = "A1:" + Cells(n_personne, 5).Address(RowAbsolute:=False, ColumnAbsolute:=False)
    
    Open "C:\Users\" + user + "\Desktop\" + fileName + ".txt" For Output As #1
    Print #1, Range(n_select)
    Close #1
    Call Shell("Explorer.exe ""C:\Users\" + user + "\Desktop\" + fileName + ".txt")
End Sub
vba excel
1 Answers

Few Suggestions

  1. Use Variables, Objects. Makes your life easier. :)
  2. Instead of +, use & for concatenation.
  3. To Bulk write a range to text file, store it in an array and transpose it. This way no loop will be required.
  4. Avoid the loop to find the last row. You may want to see How to find the last row.
  5. When working with rows, avoid the use of Integer. Use Long.

Is this what you are trying?

Option Explicit

Private Sub Exporter_Click()
    Dim ws As Worksheet
    Dim lRow As Long
    Dim rng As Range
    Dim FilePath As String
    Dim fileName As String
    
    '~~> Construct your path and filename
    FilePath = "C:\Users\" & Environ("username") & "\Desktop\"
    fileName = FilePath & "Résultats Pêchés.txt"
    
    '~~> Set your worksheet
    Set ws = ThisWorkbook.Sheets("Output")
    
    With ws
        '~~> Find last row in Col A
        lRow = .Range("A" & .Rows.Count).End(xlUp).Row
        
        '~~> Identify the range that you want to write to text
        Set rng = .Range("A1:A" & lRow)
    End With
    
    Dim rngLines() As Variant
    Dim output As Variant

    '~~> Transfer the range value to a 2D array and join
    rngLines = Application.Transpose(rng.Value)
    output = Join(rngLines, vbCrLf)
   
    '~~> Write to text file
    Open fileName For Output As #1
    Print #1, output
    Close #1
    
    '~~> Launch the file
    Call Shell("Explorer.exe " & Chr(34) & fileName & Chr(34))
End Sub

EDIT

Thank you so much, I really appreciate it. How would I go about copying multiple columns? The data is going to be in the range of A1 to E last row. When I change the variable rng Set rng = .Range("A1:E" & lRow) to that, it gives me an error that there is an invalid procedure or argument. – WhiteStrips 6 mins ago

Is this what you want?

Option Explicit

Private Sub Exporter_Click()
    Dim ws As Worksheet
    Dim lRow As Long
    Dim rng As Range
    Dim FilePath As String
    Dim fileName As String
    
    '~~> Construct your path and filename
    FilePath = "C:\Users\" & Environ("username") & "\Desktop\"
    fileName = FilePath & "Résultats Pêchés.txt"
    
    '~~> Set your worksheet
    Set ws = ThisWorkbook.Sheets("Output")
    
    With ws
        '~~> Find last row in Col A
        lRow = .Range("A" & .Rows.Count).End(xlUp).Row
        
        '~~> Identify the range that you want to write to text
        Set rng = .Range("A1:E" & lRow)
    End With
    
    Dim rw As Variant, col As Variant
    Dim output As String
    
    For Each rw In rng.Rows
        For Each col In rng.Cells
            output = output & col.Value & ","
        Next col
        output = output & vbNewLine
    Next rw
   
    '~~> Write to text file
    Open fileName For Output As #1
    Print #1, output
    Close #1
    
    '~~> Launch the file
    Call Shell("Explorer.exe " & Chr(34) & fileName & Chr(34))
End Sub
user_11405790
Related