Help with VBA

[dickep]

OK, I am trying to set up a VBA script to read a text file and process it. However, I am getting an error when I try to pass the split up, read in data. Can you figure out what is wrong here?

The problem is in the red text area. Everything appears to be reading and parsing ok until it needs to be passed. Oh, this is trying to pass a string array


If you need an input file I can provide that.

THanks
E

Sub StartProcess()
   
    ' first find the file to read from
    FileToOpen = Application.GetOpenFilename()
    
    ' open file for input only
    Open FileToOpen For Input As 1
        
    ' now, read a line until the "Meter Number" value is in the first array item
    Do
        Line Input #1, ItemsRead
        If ItemsRead <> "" Then
            Headings = Split(ItemsRead, ",")
        End If
    Loop While Headings(0) <> "Meter Number"
    
    ' now pass the next read line to a subroutine to process the data
    
    Do Until EOF(1)
        Line Input #1, ItemsRead
        ParsedDataRead = Split(ItemsRead, ",")
        
        ' now pass this to routine to process the data
        UpdateDataInSpreadSheet ParsedDataRead
    
    Loop
    
End Sub
Sub UpdateDataInSpreadSheet(ByRef myArray() As Variant)
    Worksheets("Sheet1").Select
    Cells(1, 8).EntireRow.Insert
    
End Sub

[SamT]

There was a compile error found in the UpdateDataInSpreadSheet.Sub's parameters

These at least compile. Obviously, they aren't tested

Option Explicit

Sub StartProcess()
Dim Headings
Dim FileToOpen
Dim ItemsRead
Dim ParsedDataRead

     ' first find the file to read from
    FileToOpen = Application.GetOpenFilename()
     
     ' open file for input only
    Open FileToOpen For Input As 1
     
     ' now, read a line until the "Meter Number" value is in the first array item
    Do
        Line Input #1, ItemsRead
        If ItemsRead <> "" Then
            Headings = Split(ItemsRead, ",")
        End If
    Loop While Headings(0) <> "Meter Number"
     
     ' now pass the next read line to a subroutine to process the data
     
    Do Until EOF(1)
        Line Input #1, ItemsRead
        ParsedDataRead = Split(ItemsRead, ",")
         
         ' now pass this to routine to process the data
    UpdateDataInSpreadSheet ParsedDataRead
         
    Loop
     
End Sub

Sub UpdateDataInSpreadSheet(ByRef myArray As Variant)
    Worksheets("Sheet1").Select
    Cells(1, 8).EntireRow.Insert
     
End Sub

You might try this code structure to see if it any faster

     ' now, read a line until the "Meter Number" value is in the first array item
    Do: Line Input #1, ItemsRead
    Loop While Left(ItemsRead, 13) <> "Meter Number"