Parsing text files from Excel with string manipulation

If you know the literal structure of each "record type", then you can declare them as VBA User Defined Type structures for reading (and writing). Further, it looks like you can simplify your efforts with a slightly different code design and improve your error handling.

Consider how I would approach this problem using UDFs, which makes the code so much more readable and therefore, maintainable:-

'Always set this to ensure you have all variables declared
Option Explicit

'User Defined Types for each record format
Private Type AccountInfoType
  OpenText As String * 18           'Absorb all text and prefixes up to data
  OpenDate As String * 8           'Contains the data
  AccTypeText As String * 24        'Absorb all text and prefixes up to data
  AccType As String * 7             'Contains the data
  'Add additional fields here
  CRLF As String * 2                'CR/LF character
End Type

Private Type CustomerNameType
  NameText As String * 18           'Absorb all text and prefixes up to data
  Name As String * 20               'Contains the data
  CsaRepText As String * 12         'Absorb all text and prefixes up to data
  CsaRep As String * 6              'Contains the data
  'Add additional fields here
  CRLF As String * 2                'CR/LF character
End Type

Private Type AddressType
  AddressText As String * 18        'Absorb all text and prefixes up to data
  AddressData As String * 20        'Contains the data
  SomethingHereText As String * 17  'Absorb remaining text
  'Add additional fields here
  CRLF As String * 2                'CR/LF character
End Type

Private Type LastOrderType
  LastOrderText As String * 18      'Absorb all text and prefixes up to data
  LastOrderDate As String * 10      'Contains the data
  CountryText As String * 27        'Absorb all text and prefixes up to data
  Country As String * 13            'Contains the data
  'Add additional fields here
  CRLF As String * 2                'CR/LF character
End Type

Private Type InvoiceType
  InvoiceText As String * 18        'Absorb all text and prefixes up to data
  InvoiceNumber As String * 9       'Contains the data
  StateText As String * 28          'Absorb all text and prefixes up to data
  State As String * 10              'Contains the data
  'Add additional fields here
  CRLF As String * 2                'CR/LF character
End Type

Sub ParseFile()

  Dim wb As Workbook
  Dim ws As Worksheet

  Dim row As Long

  Dim dataRecord As String

  Dim accountNumber As String
  Dim accountInfo As AccountInfoType
  Dim customerName As CustomerNameType
  Dim address As AddressType
  Dim lastOrder As LastOrderType
  Dim invoice As InvoiceType

  Dim myFile As Variant


  'Consider using proper error handling
  On Error GoTo ParseFileZ

  myFile = Application.GetOpenFilename()

  If myFile = False Then
    'Not a fan of GoTo but better than running the whole method inside if/then block
    GoTo ParseFileX
  End If

  'I started with a new workbook. Change this to open an exsting workbook if desired
  Set wb = Application.Workbooks.Add

  'Set this handle to your desired worksheet
  Set ws = wb.Worksheets(1)

  'Set up column headers here. I chose row 3 to allow for a heading in row 1. Choose your own...
  ws.Range("A3").Value = "Acc Number"
  ws.Range("B3").Value = "Acc Opened"
  ws.Range("C3").Value = "Region"
  ws.Range("D3").Value = "Name"

  'Base output row in the worksheet
  row = 3

  'Open the file in binary mode so that you can use User Defined Types to read each record
  Open CStr(myFile) For Binary As #1

  While Not EOF(1)

    'Read next record
    Input #1, dataRecord

    'Find the first record of the next account - otherwise, skip until you get one
    If Left(dataRecord, 7) = "ACCOUNT" And Len(dataRecord) = 16 Then

      'Found the Account Number record. This is the start of the next account
      accountNumber = Mid(dataRecord, 9, 8)

      Get #1, , accountInfo  'Read the Account info record
      Get #1, , customerName 'Read the Customer Name record
      Get #1, , address      'Read the Address record
      Get #1, , lastOrder    'Read the Last Order record
      Get #1, , invoice      'read the Invoice record

      'Ignore the remaining records unless you want to get more data. The "Read Next Record" loop will skip them

      'Get the next row number on the output worksheet to write values to
      row = row + 1

      'Assign the values from the various records
      ws.Cells(row, 1).Value = Trim(accountNumber)
      ws.Cells(row, 2).Value = Trim(accountInfo.OpenDate)
      ws.Cells(row, 3).Value = Trim(invoice.State)  '(you talk about "region" but no region in data sample)
      ws.Cells(row, 4).Value = Trim(customerName.Name)
      'Add more cells for additional records you want to extra fields from here

    End If

  Wend

  'We're finished. Close the file
  Close #1

  'Resize the cells for readibilty
  ws.Cells.EntireColumn.AutoFit

ParseFileX:

  'Disable error handling
  On Error GoTo 0

  'Be a good memory citizen
  Set ws = Nothing
  Set wb = Nothing

  Exit Sub

ParseFileZ:
  MsgBox Err.Number & " - " & Err.Description, "Error occurred"
  Resume ParseFileX

End Sub