VBA字符串无法置空及Excel文本转记事本格式需求

Hey there! Let's tackle both your VBA issues—setting a string to empty and processing that Excel text into your desired format. Here's a step-by-step solution:

1. Fixing the "Cannot Set String to Empty" Issue in VBA #

In VBA, setting a string to empty is straightforward—you just need to use either:

• myString = "" (a literal empty string), or
• myString = vbNullString (a built-in constant for an empty string, which is slightly more efficient).

Avoid using myString = Nothing—that’s for object variables, not strings, which is probably where you ran into trouble.

2. VBA Script to Process Excel Text & Output to Notepad #

Here’s a complete script that handles splitting your input text into blocks, transforming each block into your target format, and writing the result to a text file. I’ll break down how it works step by step.

Full VBA Code #

Sub ProcessTextAndExport()
    Dim inputText As String
    Dim blocks() As String
    Dim processedLines As Collection
    Dim i As Integer, j As Integer, k As Integer
    Dim cleanWords() As String
    Dim currentBlock As String
    Dim processedSegments As Collection
    Dim freeTextStart As Boolean
    
    ' Replace this with your actual input text (or read from a cell, e.g., Range("A1").Value)
    inputText = "AN201 BOS 306=1234 035=Yes 102=Yes 100=70 097=Yes Sometext shouldcome after a longspace thankyou AN201 BOS 306=1235 035=No 102=No 100=71 097=No This is second scenario thankyou"
    
    ' Split input into blocks starting with AN201
    blocks = Split(inputText, "AN201")
    Set processedLines = New Collection
    
    ' Process each block
    For i = 1 To UBound(blocks)
        currentBlock = "AN201" & Trim(blocks(i))
        If currentBlock <> "AN201" Then ' Skip empty blocks
            Set processedSegments = New Collection
            
            ' Split block into words, removing empty entries from multiple spaces
            cleanWords = Split(currentBlock, " ")
            freeTextStart = False
            
            For j = LBound(cleanWords) To UBound(cleanWords)
                If Trim(cleanWords(j)) <> "" Then
                    If Not freeTextStart Then
                        If InStr(cleanWords(j), "=") > 0 Then
                            ' Replace = with empty string for key-value pairs
                            processedSegments.Add Replace(cleanWords(j), "=", "")
                        Else
                            ' Check if this is the start of free text (after AN201, BOS, and key-value pairs)
                            If processedSegments.Count >= 2 Then
                                freeTextStart = True
                                ' Join all remaining words into free text
                                Dim freeText As String
                                freeText = cleanWords(j)
                                For k = j + 1 To UBound(cleanWords)
                                    If Trim(cleanWords(k)) <> "" Then
                                        freeText = freeText & " " & cleanWords(k)
                                    End If
                                Next k
                                processedSegments.Add freeText
                                Exit For ' Exit loop since we've added the free text
                            Else
                                ' Add AN201 or BOS as-is
                                processedSegments.Add cleanWords(j)
                            End If
                        End If
                    End If
                End If
            Next j
            
            ' Join segments with @# and add to processed lines
            processedLines.Add JoinCollection(processedSegments, "@#")
        End If
    Next i
    
    ' Write processed lines to a text file
    Dim filePath As String
    filePath = Environ("USERPROFILE") & "\Desktop\ProcessedOutput.txt" ' Saves to desktop
    Dim fileNum As Integer
    fileNum = FreeFile()
    
    Open filePath For Output As #fileNum
    For Each line In processedLines
        Print #fileNum, line
    Next line
    Close #fileNum
    
    MsgBox "Processing complete! File saved to: " & filePath, vbInformation
End Sub

' Helper function to join a collection into a string with a delimiter
Function JoinCollection(col As Collection, delimiter As String) As String
    Dim result As String
    Dim item As Variant
    
    For Each item In col
        If result <> "" Then result = result & delimiter
        result = result & item
    Next item
    
    JoinCollection = result
End Function

How It Works #

Let’s break down the key parts:

• Splitting into Blocks: We split the input text using "AN201" as the delimiter, then reattach "AN201" to each valid block to get individual records.
• Cleaning Words: We split each block into words and filter out empty entries caused by multiple spaces.
• Processing Segments:
  • Add AN201 and BOS as-is.
  • For key-value pairs (like 306=1234), remove the = sign and add to the segment list.
  • When we hit a word without an = sign after the initial two segments, we join all remaining words into the free text segment and add it to the list.
• Joining & Exporting: We join each block’s segments with @#, then write all processed lines to a text file on your desktop.

Usage Notes #

• Replace the inputText variable with your actual text (or read it directly from an Excel cell, e.g., inputText = Range("A1").Value).
• The output file is saved to your desktop by default—you can change the filePath variable to any location you prefer.

内容的提问来源于stack exchange，提问作者user6314105