仅当设置断点时，导出到文本文件的 EXCEL VBA 代码才有效

Question

我有一些代码可以将数据从电子表格导出到逗号分隔文件。如果我在代码中的任何位置设置了断点，数据将按预期导出到文件中。如果我没有断点，则创建的文件不包含任何数据。我认为这是一个计时问题，因此在代码中尝试了等待周期，但这并没有解决问题。这是代码:

Private Sub WriteDataToFile()  
Dim i As Long
Dim iLastRow As Integer
Dim strFile As String
Dim FSO As FileSystemObject
Dim FSOFile As TextStream
Dim strData As String
Dim s As String

strFile = "C:\Temp\DSGELIG.txt"
'  Delete the file if it already exists
DeleteFile (strFile)

'  Determine the last row
iLastRow = 50
For i = 2 To 65000
    strData = Range("B" + CStr(i))
    If Len(strData) < 1 Then
        iLastRow = i - 1
        Exit For
    End If
Next i


'  Create the file system object
Set FSO = New FileSystemObject
Set FSOFile = FSO.OpenTextFile(strFile, ForWriting, True)




For i = 2 To iLastRow
    strData = ""
    With Worksheets(1)
        'Debug.Print Range("B" + CStr(i))
        strData = """"
        '  Patient Name
        strData = strData + Range("B" + CStr(i))
        strData = strData + """" + "," + """"
        '  SSN / Policy #
        strData = strData + Range("C" + CStr(i))
        strData = strData + """" + "," + """"
        '  Birthdate
        strData = strData + CStr(Range("D" + CStr(i)))
        strData = strData + """" + "," + """"
        '  Admit Date
        strData = strData + CStr(Range("E" + CStr(i)))
        strData = strData + """" + "," + """"
        '  Admit Date - 2
        strData = strData + CStr(Range("F" + CStr(i)))
        strData = strData + """" + "," + """"
        '  Account Number
        strData = strData + CStr(Range("G" + CStr(i)))
        strData = strData + """" + "," + """"
        '  Insurance Code
        strData = strData + Range("H" + CStr(i))
        strData = strData + """" + "," + """"
        '  Financial Class
        strData = strData + Range("I" + CStr(i))
        strData = strData + """" + "," + """"
        '  Location
        strData = strData + Range("J" + CStr(i))
        strData = strData + """"

        '  Write the record to the file
        FSOFile.WriteLine (strData)
    End With


Next i

FSOFile.Close

End Sub

提前谢谢您 - 我已经有一段时间没有完成任何 VBA 了。

Answer 1

我修改了一些 Kittoes 代码，如下所示:

Sub Test()

    Dim FSO As New FileSystemObject
    Dim ts As TextStream
    Dim strFile As String, strData As String
    Dim iLastRow As Integer, i As Long, c As Long

    strFile = "C:\Temp\DSGELIG.txt"

    '  Determine the last row
    iLastRow = Cells(Rows.Count, 2).End(xlUp).Row

    '  Create the file system object
    Set ts = FSO.CreateTextFile(strFile, True)
    With Worksheets(1)
        For i = 2 To iLastRow
            strData = ""
            For c = 2 To 10
                strData = "'" & strData & .Cells(i, c) & "',"
            Next c

            '  Write the record to the file without the last comma
            ts.WriteLine Left(strData, Len(strData) - 1)
        Next i
    End With

    ts.Close

    Set ts = Nothing
    Set FSO = Nothing

End Sub

我必须说我从未使用过 FSO.CreateFile 和其余的。我通常使用旧的 Print #1 语法。如果可以的话，我通常会尽量避免任何额外的引用。