Excel VBA：围绕行循环，然后引用该行中的单元格

Question

我尝试在特定工作表中循环包含数据的单元格，然后将该行中的每个单元格打印到文件中。

这是我的代码，但打印行的语法有误。我不明白这是什么问题。我正试图为包含数据的每一行打印A列中的内容。

Private Sub GENERATE_ORDER_BUTTON_Click()

    Dim path As String
    path = "H:\order.json"

    Set fs = CreateObject("Scripting.FileSystemObject")
    Set A = fs.createtextfile(path, True)
    A.WriteLine ("{")
    A.Close
    
    Dim outputstring As String
    outputstring = "  ""BASE_CE"": ["
    
    Open path For Append As #1
    Print #1, outputstring
    Close #1
    
    
    Dim LastRow As Long, ws As Worksheet

    Set ws = Sheets("CE Router")

    LastRow = ws.Range("A" & Rows.Count).End(xlUp).Row
    
    Set RowRange = ws.Range("A2:A" & LastRow)
    
    For Each rrow In RowRange
    
        Set ws = Sheets("CE Router")
        
        Open path For Append As #1
        Print #1, "    {"
        Print #1, ws.Range("A" & rrow).Value
        Close #1
    
    Next rrow

 
End Sub

Answer 1

• 仅供参考<代码>VBE

学习模式

Option Explicit

Sub orderButton()
    Const FilePath = "F:\Test\2020\65234018\order.json"
    ' Create a reference to  VBE>Tools>References>Microsoft Scripting Runtime.
    ' Now you have intellisense for File System Object, so you can learn.
    Dim fs As Scripting.FileSystemObject: Set fs = New FileSystemObject
    fs.CreateTextFile FilePath, True
    Dim tFile As File: Set tFile = fs.GetFile(FilePath)
    Dim tStream As TextStream: Set tStream = tFile.OpenAsTextStream(ForWriting)
    With tStream
        ' 1.
        .WriteLine ("{")
        .WriteLine "  ""BASE_CE"": ["
        ' 2.
        Dim ColumnRange As Range
        Dim cel As Range
        Dim LastRow As Long
        ' If other workbook than the one containing this code,
        ' then use its name or 'ActiveWorkbook'.
        With ThisWorkbook.Worksheets("CE Router")
            LastRow = .Range("A" & .Rows.Count).End(xlUp).Row
            Set ColumnRange = .Range("A2:A" & LastRow)
        End With
        For Each cel In ColumnRange.Cells
            .WriteLine "    {"
            .WriteLine cel.Value
        Next cel
        .Close
    End With

End Sub

后来

' When you have learned you can use the following without variables
' and without a reference (wouldn't recommend it for now):
With CreateObject("Scripting.FileSystemObject")
    .CreateTextFile FilePath, True
    With .GetFile(FilePath).OpenAsTextStream(ForWriting)
        ' The code
    End with
End With