Re: ClarificationRE: VBA code to assemble data spread across sever

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

"John Nurick" wrote:

>
> 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
>
>
>
> On Sun, 3 Jul 2005 17:58:02 -0700, "Antonio"
> <Antonio@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote:
>
> >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
> >
> >
> >
> >
> >"John Nurick" wrote:
> >
> >> 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.
> >>
> >> On Sun, 3 Jul 2005 02:48:01 -0700, "Antonio"
> >> <Antonio@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote:
> >>
> >> >In the clarification, the word Cardiology is actually under field 5...not in
> >> >field 1...
> >> >Sorry for the confusion.
> >> >Antonio
> >> >
> >> >"Antonio" wrote:
> >> >
> >> >> 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
> >> >>
> >> >>
> >> >> "Antonio" wrote:
> >> >>
> >> >> > 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.
> >>
> >> --
> >> John Nurick [Microsoft Access MVP]
> >>
> >> Please respond in the newgroup and not by email.
> >>
>
> --
> John Nurick [Microsoft Access MVP]
>
> Please respond in the newgroup and not by email.
>
.