Reading from a file
 

Hello,

I have a little routine that saves data from a spreadsheet to a data file.
What I want to do now is load that data back into the spreadsheet.

After Opening the file how would I go about reading each line into a
variable line by line?

Thanks,
Ernst.

Reading from a file
 

Why not do it all in one shot? For example, something like:

Dim vTest As Variant, vTest2 As Variant ' Arrays
to save spreadsheet data
vTest = ActiveSheet.Range("E3:F5") ' Loads
spreadsheet data into array
'-------------------------------------------------------* Writes
entire array to file
Open "VBA-Test-Array.txt" For Binary As #1
Put #1, , vTest
Close
'-------------------------------------------------------* Reads
entire array from file
Open "VBA-Test-Array.txt" For Binary As #1
Get #1, , vTest2
Close

On Mar 20, 2:02 pm, Ernst Guckel
wrote:

I have a little routine that saves data from a spreadsheet to a data file.
What I want to do now is load that data back into the spreadsheet.

After Opening the file how would I go about reading each line into a
variable line by line?

Reading from a file
 

ok... My data file is text based for starters... This might matter... Also
Excel generated an error "Variable uses an automation type not supported in
Visual Basic"

Not sure... This is what I tried:

Dim strFileName As String
Dim vtest As Variant

strFileName = "C:\MenuItem.Dat"

Open strFileName For Binary As #1 ' Open file for input
Get #1, , vtest
Range("dump") = vtest
Close #1


"Randy Harmelink" wrote:

Why not do it all in one shot? For example, something like:

Dim vTest As Variant, vTest2 As Variant ' Arrays
to save spreadsheet data
vTest = ActiveSheet.Range("E3:F5") ' Loads
spreadsheet data into array
'-------------------------------------------------------* Writes
entire array to file
Open "VBA-Test-Array.txt" For Binary As #1
Put #1, , vTest
Close
'-------------------------------------------------------* Reads
entire array from file
Open "VBA-Test-Array.txt" For Binary As #1
Get #1, , vTest2
Close

On Mar 20, 2:02 pm, Ernst Guckel
wrote:

I have a little routine that saves data from a spreadsheet to a data file.
What I want to do now is load that data back into the spreadsheet.

After Opening the file how would I go about reading each line into a
variable line by line?

Reading from a file
 

This worked fine for me when I added it to the code I sent previously:

Range("A1:B3") = vTest2

Was your "Dump" range the same dimensions (rows x columns) of what you
saved into the file, as well as your vTest array?

If the data was previously saved in a different matter, this method
won't work. You method of reading will need to be similar to your
method of writing.

On Mar 20, 8:19 pm, Ernst Guckel
wrote:
ok... My data file is text based for starters... This might matter... Also
Excel generated an error "Variable uses an automation type not supported in
Visual Basic"

Not sure... This is what I tried:

Dim strFileName As String
Dim vtest As Variant

strFileName = "C:\MenuItem.Dat"

Open strFileName For Binary As #1 ' Open file for input
Get #1, , vtest
Range("dump") = vtest
Close #1

Reading from a file
 

This is what I have to write the data...

Sub MenuItem()

Dim c As Range
Dim strTemp As String
Dim strFileName As String

strFileName = "C:\MenuItem.Dat"
Open strFileName For Output As #1 ' Open file for output.

For Each c In Range("ItemRange")

strTemp = c.Cells(1, 6).Value
If c.Cells(1, 6).Value = "" Then GoTo NextItem
Print #1, strTemp

NextItem:

Next

Close #1 ' Close file.
End Sub

and an the data file looks like this:

000Whop Jr. Whop Jr 000010000000001100000010101
001Whop Jr./Chz Whop Jr/C000011900000001100000010101
003Whopper Whopper 000029900000001100000010101
004Whopper/Chz Whopper/C000033900000001100000010101

I cannot load the data as binary because it was not writen that way. Nor
can I write it at binary because the POS system that uses the file needs it
in a text format...

I actually don't want to dump the array to a range. I would prefer to step
through it and write it each line at a time... I can handle this... I
cannot figure out how to load the array...

Thanks,
Ernst.

Reading from a file
 

Something like this should work:

Dim strTemp As String
Dim strFileName As String
strFileName = "C:\MenuItem.Dat"
Open strFileName For Input As #1 ' Open file for output.
Do While Not EOF(1) ' Loop until end of file.
Line Input #1, strTemp ' Read line into variable.
Debug.Print strTemp ' Print to the Immediate window.
Loop
Close #1

Just replace the "Debug.Print" line with whatever you want to do with
the line of data that was read from the file.

On Mar 21, 5:58 am, Ernst Guckel
wrote:
This is what I have to write the data...

Sub MenuItem()

Dim c As Range
Dim strTemp As String
Dim strFileName As String

strFileName = "C:\MenuItem.Dat"
Open strFileName For Output As #1 ' Open file for output.

For Each c In Range("ItemRange")

strTemp = c.Cells(1, 6).Value
If c.Cells(1, 6).Value = "" Then GoTo NextItem
Print #1, strTemp

NextItem:

Next

Close #1 ' Close file.
End Sub

and an the data file looks like this:

000Whop Jr. Whop Jr 000010000000001100000010101
001Whop Jr./Chz Whop Jr/C000011900000001100000010101
003Whopper Whopper 000029900000001100000010101
004Whopper/Chz Whopper/C000033900000001100000010101

I cannot load the data as binary because it was not writen that way. Nor
can I write it at binary because the POS system that uses the file needs it
in a text format...

I actually don't want to dump the array to a range. I would prefer to step
through it and write it each line at a time... I can handle this... I
cannot figure out how to load the array...

Reading from a file
 

Works great... thanks for the help...

Ernst.


"Randy Harmelink" wrote:

Something like this should work:

Dim strTemp As String
Dim strFileName As String
strFileName = "C:\MenuItem.Dat"
Open strFileName For Input As #1 ' Open file for output.
Do While Not EOF(1) ' Loop until end of file.
Line Input #1, strTemp ' Read line into variable.
Debug.Print strTemp ' Print to the Immediate window.
Loop
Close #1

Just replace the "Debug.Print" line with whatever you want to do with
the line of data that was read from the file.

On Mar 21, 5:58 am, Ernst Guckel
wrote:
This is what I have to write the data...

Sub MenuItem()

Dim c As Range
Dim strTemp As String
Dim strFileName As String

strFileName = "C:\MenuItem.Dat"
Open strFileName For Output As #1 ' Open file for output.

For Each c In Range("ItemRange")

strTemp = c.Cells(1, 6).Value
If c.Cells(1, 6).Value = "" Then GoTo NextItem
Print #1, strTemp

NextItem:

Next

Close #1 ' Close file.
End Sub

and an the data file looks like this:

000Whop Jr. Whop Jr 000010000000001100000010101
001Whop Jr./Chz Whop Jr/C000011900000001100000010101
003Whopper Whopper 000029900000001100000010101
004Whopper/Chz Whopper/C000033900000001100000010101

I cannot load the data as binary because it was not writen that way. Nor
can I write it at binary because the POS system that uses the file needs it
in a text format...

I actually don't want to dump the array to a range. I would prefer to step
through it and write it each line at a time... I can handle this... I
cannot figure out how to load the array...