VBA code to assemble data spread across several rows.

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Antonio - 03 Jul 2005 09:26 GMT

I posted a question on 7/1/05 In the Access Import/Export Data group. One of
the three suggestions from a very kind person named John was to "write VBA
code to read the text file line by line, assemble records, and append them to
a table." But I am looking for example of such code (as I am new to the VBA
arena) Here is the problem, I have a linked table to a text file. When you
open the link the data displays like this:
Field1 Field2 Field3
Field4 Field5
M000443990 MORA,EDUARDO N M/30
07/05/05

1440

CARDIOLOGY
M000162333 GARCIA,HOPE J F/80
07/05/05

1000

CARDIOLOGY
The desired end result would be to bring all the data into one record by
Field1 and Field Two, like this.
Field1 Field2 Field3
Field4 Field5
M000999999 MORA,EDUARDO N M/30 07/05/05 1440 CARDIOLOGY
M000123456 GARCIA,HOPE J F/80 07/05/05 1000 CARDIOLOGY

Can anyone give examples of such code? OR...since I am not terribly familiar
with VBA, can anyone suggest code that would copy Field1 into all Null fields
below it until it reaches a "Not Is Null" field, and then copy the data that
is in that field to all Null Field1 fields below it, and then again, until it
reaches the last record in the table? (I saw something in the knowledge base
about something to do with Looping Structures? perhaps?)... Example:
From this:
Field1 Field2 Field3
Field4 Field5
M000999999 MORA,EDUARDO N M/30
07/05/05

1440

CARDIOLOGY
M000123456 GARCIA,HOPE J F/80
07/05/05

1000

CARDIOLOGY
To This:
Field1 Field2 Field3
Field4 Field5
M000999999 MORA,EDUARDO N M/30
M000999999 07/05/05
M000999999 1440
M000999999
CARDIOLOGY
M000123456 GARCIA,HOPE J F/80
M000123456 07/05/05
M000123456 1000
M000123456
CARDIOLOGY
What I am thinking (hoping) is that with the same information across the
corresponding rows, I can build a query that will bring all the info together
where Field1 is the same. (field 1 is never repeated..its unique to the
person.)

Any help would be Awsome!. Thank you.

Reply to this Message

Antonio - 03 Jul 2005 09:45 GMT

Sorry, the news group shifted the fields in my example...this is how it
should look:
Field1 Field2 Field3 Field4 Field5
M000999999 MORA,EDUARDO N M/30
07/05/05
1440

CARDIOLOGY
M000123456 GARCIA,HOPE J F/80
07/05/05
1000

CARDIOLOGY
To This:
Field1 Field2 Field3 Field4 Field5
M000999999 MORA,EDUARDO N M/30
M000999999 07/05/05
M000999999 1440
M000999999
CARDIOLOGY
M000123456 GARCIA,HOPE J F/80
M000123456 07/05/05
M000123456 1000
M000123456
CARDIOLOGY

> I posted a question on 7/1/05 In the Access Import/Export Data group. One of
> the three suggestions from a very kind person named John was to "write VBA
[quoted text clipped - 63 lines]
>
> Any help would be Awsome!. Thank you.

Reply to this Message

Antonio - 03 Jul 2005 10:48 GMT

In the clarification, the word Cardiology is actually under field 5...not in
field 1...
Sorry for the confusion.
Antonio

> Sorry, the news group shifted the fields in my example...this is how it
> should look:
[quoted text clipped - 89 lines]
> >
> > Any help would be Awsome!. Thank you.

Reply to this Message

John Nurick - 03 Jul 2005 19:23 GMT

Antonio, almost every message you have posted, both here and in
.externaldata, shows a different structure of the sample data. It now
seems that the [Field 1] value (which you previously described as [ID])
is repeated at the beginning of every line of a patient's record;
previously you said it was only present on the first line of each
patient's record.

See my message today in .externaldata for the wildcard search patterns
needed to do what I thought you wanted in Word. Meanwhile, I'm going to
stop chasing a moving target.

>In the clarification, the word Cardiology is actually under field 5...not in
>field 1...
[quoted text clipped - 94 lines]
>> >
>> > Any help would be Awsome!. Thank you.

--
John Nurick [Microsoft Access MVP]

Please respond in the newgroup and not by email.

Reply to this Message

Antonio - 04 Jul 2005 01:58 GMT

John, being relatively weak in VBA, no experience in Perl, and not having
success with Word (and needing to get this process as automated as possible
by tuesday morning) I posted a question in VBA Programming that would copy
values down a column until it hit the next column that was not null. There by
I would have a key that would link the data that was spread across the 4
rows. Whenever I attempted to provide an example, this news group would shift
the location "cardio" to a fifth row under the Patient Identifier. which is
not the case (and why I tried to post a couple of clarifications after the
fact)
So...while it was related to our original posting on Import/Export group, I
was asking for help for a VBA solution that I think I would be able to manage
given my level of experience. (ie...a button that would run code to copy the
Patient # and then loop until the end of the table)
If you look at the example I ask for VBA code that would go from the first
example to what I have in the second example... (From this...To This). The
secod example, where the Patient number is repeated across the rows is not
meant to indicate what the file looks like now..just where I want to go.
So...for example purposes for the easier VBA solution I thougt I could handle
, you can omit the Field 5 and the word Cardiology all together and take that
to mean what the current file looks like.
So, My VBA question example is going from this.....

Field1 Field2 Field3 Field4
M000999999 MORA,EDUARDO N M/30
07/05/05
1440
M000123456 GARCIA,HOPE J F/80
07/05/05
1000

To this........
Field1 Field2 Field3 Field4
M000999999 MORA,EDUARDO N M/30
M000999999 07/05/05
M000999999 1440
M000123456 GARCIA,HOPE J F/80
M000123456 07/05/05
M000123456 1000

Sorry for the confusion. Still would appreciate any suggestions.
Antonio

> Antonio, almost every message you have posted, both here and in
> ..externaldata, shows a different structure of the sample data. It now
[quoted text clipped - 110 lines]
>
> Please respond in the newgroup and not by email.

Reply to this Message

John Nurick - 04 Jul 2005 07:18 GMT

Here is an "air code" VBA procedure which shows one approach to the
problem. It assumes among other things that every record has the same
number of lines in the same order, and that the padding between fields
in the text file consists of spaces.

Function XX()
Dim lngFN As Long
Dim strLine As String
Dim strF1 As String
Dim strF2 As String
Dim strF3 As String
Dim strF4 As String
Dim strF5 As String
Dim rsR As DAO.Recordset

Set rsR = CurrentDb.OpenRecordset("My Table")

lngFN = FreeFile()
Open "C:\Folder\file.txt" For Input As lngFN

Do Until EOF(lngFN)
Line Input #lngFN, strLine 'read first line
strLine = Trim(strLine)
strF1 = Left(strLine, InStr(strLine, " ") - 1)
strF2 = Trim(Mid(strLine, InStr(strLine, " ")))

Line Input #lngFN, strLine 'read second line
strF3 = Trim(strLine)
'repeat for lines 3 & 4 (and any others)
'...

With rsR 'add record to table
.AddNew
.Fields(0).Value = strF1
.Fields(1).Value = strF2
'and so on
.Update
End With
Loop

rsR.Close
Close #lngFN

End Function

>John, being relatively weak in VBA, no experience in Perl, and not having
>success with Word (and needing to get this process as automated as possible
[quoted text clipped - 153 lines]
>>
>> Please respond in the newgroup and not by email.

--
John Nurick [Microsoft Access MVP]

Please respond in the newgroup and not by email.

Reply to this Message

Antonio - 05 Jul 2005 12:14 GMT

John, thank you for the help. It did the trick and I've met my deadline.
Again, sorry for the confusion. I appreciate all you have done.
Antonio

> Here is an "air code" VBA procedure which shows one approach to the
> problem. It assumes among other things that every record has the same
[quoted text clipped - 204 lines]
>
> Please respond in the newgroup and not by email.

Reply to this Message