PDA

View Full Version : Help with VBA



dickep
10-26-2015, 12:29 PM
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
10-26-2015, 02:38 PM
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"