Results 1 to 3 of 3
  1. #1
    Join Date
    Oct 2014
    Posts
    11

    Question Unanswered: Formatting messed up data for Access

    Hi, I have a lot of data that is in Word in the following format:

    Title
    entry
    entry
    entry
    entry

    Page Break

    Title
    entry
    entry
    entry
    entry
    entry
    entry
    entry
    entry
    entry

    THis goes on for over a hundred pages and each page has a different number of entries (some of them go on for more than a page), so there's no easy way to say write a macro to format it.

    If I want the Title to be in the parent table and the entries in child table, then I will need to format it like this:

    TitleFK Entry
    TitleFK Entry
    TItleFK Entry
    TitleFK Entry
    etc.

    Can anybody think of a way to get all this data formatted?

    I'm pretty good at using Word & Excel and Access itself to get weird data in the correct format, but I just can't figure this one out. Thanks for any help.

  2. #2
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    496
    Provided Answers: 24
    1. why is it in Word?
    2. If its in access you can produce any format, via reports, or spreadsheet.
    What is you end result?
    excel doc
    paper report

  3. #3
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Here's a function that returns an array of arrays. Each internal array inside the returned array corresponds to one page in the Word document while each element of each internal array contains one non-empty line of the related page.
    Code:
    Public Function GetLinesFromWordDoc(ByVal DocName As String) As Variant
    
        Dim appword As Word.Application
        Dim wDoc As Word.Document
        Dim str As String
        Dim i As Long
        Dim j As Long
        Dim k As Long
        Dim lng As Long
        Dim x As Variant
        Dim y As Variant
        Dim z As Variant
        Dim varLines As Variant
        
        If appword Is Nothing Then Set appword = New Word.Application
        With appword
            Set wDoc = .Documents.Open(DocName, , True)
            str = wDoc.Content.Text
            x = Split(str, Chr(12))
            ReDim varLines(0 To UBound(x))
            For i = 0 To UBound(x)
                x(i) = Replace(x(i), Chr(13), "")
                y = Split(x(i), Chr(11))
                lng = 0
                For j = 0 To UBound(y)
                    If Len(Trim(y(j))) > 0 Then lng = lng + 1
                Next j
                k = 0
                ReDim z(0 To lng - 1)
                For j = 0 To UBound(y)
                    If Len(Trim(y(j))) > 0 Then
                        z(k) = y(j)
                        k = k + 1
                    End If
                Next j
                varLines(i) = z
            Next i
            .Documents(1).Close
        End With
        appword.Quit
        Set appword = Nothing
        GetLinesFromWordDoc = varLines
        
    End Function
    Example:
    Code:
        Dim var As Variant
        
        var = GetLinesFromWordDoc("U:\NewDocWord.doc") ' U:\NewDocWord.doc contains the sample lines you posted.
    After calling GetLinesFromWordDoc, var contains this:
    Code:
    var(0)(0) --> "Title"
    var(0)(1) --> "entry"
    var(0)(2) --> "entry"
    var(0)(3) --> "entry"
    var(0)(4) --> "entry"
    var(1)(0) --> "Title"
    var(1)(1) --> "entry"
    var(1)(2) --> "entry"
    var(1)(3) --> "entry"
    var(1)(4) --> "entry"
    var(1)(5) --> "entry"
    var(1)(6) --> "entry"
    var(1)(7) --> "entry"
    var(1)(8) --> "entry"
    var(1)(9) --> "entry"
    So we can consider that each var(n)(0) contains a title while each var(n)(>0) contains an entry.

    From there, it should not be hard to write a procedure that stores the contents of the array into tables.

    Warning: Quick'n dirty code: no error handling and probably lots of unforseen cases that won't be handled properly.
    Have a nice day!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •