时间戳

首页 > 解决方案 > 有没有办法在作为字符串变量的输出中打印一个范围？

excel - 有没有办法在作为字符串变量的输出中打印一个范围？

问题描述

我正在尝试将 Excel 工作表的这一部分导出到 txt 文件中。n_select确定有多少行有数据需要导出。我试过制作Rangefrom n_select，但它给了我一个类型不匹配的错误。

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

标签： excelvba

解决方案

一些建议

  1. 使用变量、对象。让您的生活更轻松。:)
  2. 代替+,&用于连接。
  3. 要将Bulk范围写入文本文件，请将其存储在数组中并转置。这样就不需要循环了。
  4. 避免循环查找最后一行。您可能想查看如何找到最后一行。
  5. 处理行时，避免使用Integer. 使用Long.

这是你正在尝试的吗？

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

编辑

非常感谢，我真的很感激。我将如何去复制多列？数据将在 A1 到 E 最后一行的范围内。当我将变量 rng Set rng = .Range("A1:E" & lRow) 更改为此时，它会给我一个错误，即存在无效的过程或参数。– WhiteStrips 6 分钟前

这是你想要的吗？

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

推荐阅读