How can I grab text between quotes in a text file using VBA?

To extract text between quotes in a text file using VBA (Visual Basic for Applications), you can use a combination of file handling and string manipulation techniques. Here's a step-by-step guide:

1. Open the Text File:

- Use VBA's `FileSystemObject` to open the text file for reading.

2. Read the File Line by Line:

- Loop through each line of the file.

3. Extract Text Between Quotes:

- For each line, use VBA's string functions to find the positions of the opening and closing quotes. Then, extract the substring between these positions.

4. Handle Multiple Quotes:

- If a line contains multiple sets of quotes, you'll need to loop through the line to extract each set of quoted text.

5. Store or Display the Extracted Text:

- You can store the extracted text in an array, a collection, or display it directly in the Immediate window or a worksheet.

6. Example VBA Code:

Sub ExtractTextBetweenQuotes()
  Dim fso As Object, ts As Object, filePath As String, line As String
  Dim startPos As Long, endPos As Long, extractedText As String
  Dim results As Collection

  Set results = New Collection
  filePath = "C:\path\to\your\file.txt" ' Replace with your file path

  Set fso = CreateObject("Scripting.FileSystemObject")
  Set ts = fso.OpenTextFile(filePath, 1) ' 1 for reading

  Do While Not ts.AtEndOfStream
    line = ts.ReadLine
    startPos = InStr(1, line, Chr(34)) ' Chr(34) is the double quote character
    Do While startPos > 0
      endPos = InStr(startPos + 1, line, Chr(34))
      If endPos > 0 Then
        extractedText = Mid(line, startPos + 1, endPos - startPos - 1)
        results.Add extractedText
        startPos = InStr(endPos + 1, line, Chr(34))
      Else
        Exit Do
      End If
    Loop
  Loop

  ts.Close

  ' Display the results (example: in Immediate window)
  Dim item As Variant
  For Each item In results
    Debug.Print item
  Next item

  Set fso = Nothing
  Set ts = Nothing
  Set results = Nothing
End Sub

7. Explanation:

- The code uses `FileSystemObject` to open the file. It reads the file line by line using a `Do While` loop.

- `InStr` is used to find the positions of the double quotes. `Chr(34)` represents the double quote character.

- `Mid` extracts the text between the quotes.

- The extracted text is added to a `Collection` named `results`.

- Finally, the code iterates through the `results` collection and prints each extracted string to the Immediate window.

Remember to replace `"C:\path\to\your\file.txt"` with the actual path to your text file. This VBA code provides a robust way to extract text between quotes from a text file, handling multiple sets of quotes on a single line.

Learn more about this