Copy to a Cell

  • Thread starter Thread starter Striker
  • Start date Start date
S

Striker

Given the following code posted by Kaak, how would I copy data from
different places in this 500 Byte record to different cells in the
spreadsheet. The file I am working with may have up to 1000 records in it
each day, and each record is 500 bytes. I was thinking that sLineOfText
already contains the data that I need, so why copy from one variable to
another as below.

I was thinking about doing something like


Cell A2.value = Trim(Mid(sLineOfText, 38, 15))
Cell A2.value = Trim(Mid(sLineOfText, 10, 10))

'After copying date to this row in Excel I need to move down one row to be
ready for the next record.

Thank You

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~




~~~~~~~FROM AN EARLIER POST~~~~~~~~~~~~~~~~~~~

Dim sLineOfText, sFirstName as String

Open "C:\Temp\TextFle.txt" For Input As #1

Do Until EOF(1)

Line Input #1, sLineOfText

sFirstName = Mid(sLineOfText, 38, 15)

msgbox sFirstName

Loop

Close #1
 
Hi, Striker

of course you might also copy the data into the cells directly from the
variable sLineOfText. I modified your posted code so that it will cut
the records into 5 parts of 100 bytes each and copy them row by row to
columns A through E of the active worksheet.

Dim sLineOfText As String
Dim rng As Range
Set rng = Range("A2")
Open "C:\Temp\TextFle.txt" For Input As #1
Do Until EOF(1)
Line Input #1, sLineOfText
rng = Left(sLineOfText, 100)
rng.Offset(0, 1) = Mid(sLineOfText, 101, 100)
rng.Offset(0, 2) = Mid(sLineOfText, 201, 100)
rng.Offset(0, 3) = Mid(sLineOfText, 301, 100)
rng.Offset(0, 4) = Mid(sLineOfText, 401, 100)
Set rng = rng.Offset(1, 0)
Loop
Close #1

Regards
Ingolf
 
Back
Top