Question about Word and Excel Behving together...

[PatrickJohnson]

So i have an rtf file with some tables within it. There is also extraneous data. This file is generated from an application written by Quilogy and I have the task of taking this file and putting it through access to create a payroll file. There will generally be 3 to 4 lines of heading information, a couple blank lines, then a large table, with all this being repeated 3 to 8 times in a file.

My thought to get it into access tables works, but only by hand. I've run into considerable problems coding it. So here goes...

The process i figured out to get it from rtf to access is to:
1) open the rtf in word
2) copy all
3) open excel
4) paste with cursor at cell A1
This pastes the data in the table format
5) save the excel file
6) import the excel spreadsheet as a table into access
7) use delete and update queries to remove the extraneous lines and filter down to true data

So, I've gotten my vba code to open word, open the appropriate rtf file, copy all, then i'm stuck. I can't get excel to open and paste. here is the snippet I'm attempting to use:

    Dim ExcApp As Excel.Application 
    Dim ExcBook As Excel.Workbook 
    Set ExcApp = New Excel.Application 
    With ExcApp 
        .Visible = True 
        Set ExcBook = ExcApp.Workbooks.Add
    ExcApp.ActiveSheet.Paste

I can get the save part after that i think, but this part won't work. i generally get the "workbook paste method failed" error, though the errors have changed from time to time.

basically what i'm after is either how to make this paste into the sheet so i can let the clipboard reformat or how to get the rtf file into access some other way. i tried saving it as text, and was successful, but when i open it all the table fields each start a new line and i can't get it not to.

 

[chergh]

I would do it something like this

Option Base 1

Sub bleh()

Dim wrdapp As Word.Application
Dim wrdDoc As Word.Document

Dim path As String

Dim i As Integer
Dim j As Integer
Dim x As Integer
Dim y As Integer

Dim strArray() As String

Set wrdapp = New Word.Application
Set wrdDoc = wrdapp.Documents.Open(path)

For Each tbl In wrdDoc.Tables

    i = ActiveDocument.tbl.Rows.Count
    j = ActiveDocument.tbl.Columns.Count
    
    ReDim strArray(1 To i, 1 To j)
    
    For x = 1 To i
        For y = 1 To j
        
             strArray(x, y) = ActiveDocument.Tables(1).Cell(x, y)
        
        Next y
    Next x
    
    Set rs = db.openrecordset("TableName")
    
    For i = 2 To UBound(strArray)
        rs.addnew
            rs!Field1 = strArray(i, 1)
            rs!field2 = strArray(i, 2)
            rs!fieldn = strArray(i, n)
        rs.Update
    Next i

Next tbl

End Sub

This code loops through each table in the open document. For each table it reads the values in the able into an array and then uses the 'addnew' method to add the data to a table. I've added an 'option base 1' so the arrays start at 1 and not 0.

Where I use the addnew method I have started with the second row of the array as I would expect the first row of the array to contain column headers.

 

[PatrickJohnson]

so just so i'm clear, this is meant to add the data from the rtf directly to an access table, correct?

If so, that would certainly reduce the headaches.Arrays are pretty foreign to me though, so while I will likely implement this solution, could you explain in a little more detail what exactly is going on in this example?

I've also run into another problem. The file I am processing is a list of entertainers who play throughout the night. as you can see from the example file attached, there is a time above each table. I need this time as we have to cost what we pay the entertainers by what segment they play. Is there a way i can append the starting time of the section into a field for each record in the new table? something like:

Field1 Field2 Field3
John Doe 123.00 06:30PM

 

[chergh]

so just so i'm clear, this is meant to add the data from the rtf directly to an access table, correct?

Yes that is correct

could you explain in a little more detail what exactly is going on in this example?

I'll repost the code with more comments

I've also run into another problem. The file I am processing is a list of entertainers who play throughout the night. as you can see from the example file attached, there is a time above each table. I need this time as we have to cost what we pay the entertainers by what segment they play. Is there a way i can append the starting time of the section into a field for each record in the new table? something like:

Field1 Field2 Field3
John Doe 123.00 06:30PM

It is possible but it's also a pain in the ass. While it wouldn't be to tricky to get a time from the document it's a lot harder to be certain you are getting the correct time. Working with data in tables in word docs is a lot easier than working with data in free text in the document.

My first suggestion for this is go back to how the word doc is being produced and put the time in the table as this will make life much easier.

 

[PatrickJohnson]

Yes that is correct



I'll repost the code with more comments



It is possible but it's also a pain in the ass. While it wouldn't be to tricky to get a time from the document it's a lot harder to be certain you are getting the correct time. Working with data in tables in word docs is a lot easier than working with data in free text in the document.

My first suggestion for this is go back to how the word doc is being produced and put the time in the table as this will make life much easier.

Well, if it were an option to change the formatting of the output i'm receiving, I would have just made it come out in a csv

Unfortunately the tool that generates the report was written by third party and the company is not interested in the huge fees to have it rewritten when some manipulation on our part will make it work. I think I've figured it out, because the segment times are always the same and always listed in order on the file, so i can set the time of each data set as i'm pulling each table. I just have to brush up on manipulating record sets, as it is still a bit hazy to me.

So here is what i am thinking:

set a variable to the first segment time
hit first table on the rtf file
load data to access table that contains an extra column
run an update query to update the empty field to the segment time value
change segment time value to next segment time
repeat until done

i think i can do this with the code above and just tag the update query and the variable reassignment to the end. thanks so much!

 

[PatrickJohnson]

i'm getting a compile error (type mismatch) on the following part:

strArray(x, y) = ActiveDocument.Tables(1).Cell(x, y)

any ideas?

 

[chergh]

Going to need a bit more info than that. Did this work before? has anything in your table changed? do you know which part of the table it errors on? have you cahnged anything in the code?

 

[PatrickJohnson]

Sorry, i should have been more clear. I haven't gotten around to testing the code you posted until now. Here is how i have it written:

Dim wrdapp As Word.Application
Dim wrdDoc As Word.Document
Set wrdapp = New Word.Application
    wrdapp.Visible = True
Dim path As String
Dim i As Integer
Dim j As Integer
Dim x As Integer
Dim y As Integer
Dim strArray() As String
Set wrdapp = New Word.Application
Set wrdDoc = wrdapp.Documents.Open(strRtfFileString)
For Each tbl In wrdDoc.Tables
    i = ActiveDocument.tbl.Rows.Count
    j = ActiveDocument.tbl.Columns.Count
 
    ReDim strArray(1 To i, 1 To j)
 
    For x = 1 To i
        For y = 1 To j
 
             strArray(x, y) = ActiveDocument.Tables(1).Cell(x, y)
 
        Next y
    Next x
 
    Set rs = Db.OpenRecordset("tblTempShowData")
 
    For i = 1 To UBound(strArray)
        rs.AddNew
            rs!Name = strArray(i, 1)
            rs!SSN = strArray(i, 2)
            rs!DummyField1 = strArray(i, 3)
            rs!DummyField2 = strArray(i, 4)
            rs!DummyField3 = strArray(i, 5)
            rs!Amount = strArray(i, 6)
            rs!ShowDate = strShowDate
            rs!ShowNumber = CStr(intShowNumber)
            rs.Update
    Next i
Next tbl

The only changes i have made are variable names.
Also, the table I am loading into will also need two other fields, the show number and the date of the show, so i have added them in the field assignments section. Since the Table i will be pulling data from in the word document has 6 columns, i took it as assumed that i would need to change this:

rs!Field3 = strArray(i, n)

to this:

rs!DummyField1 = strArray(i, 3)

and i updated the record set field names to reflect the names of the fields in the table this is being loaded to.

EDIT: also, the code was inserted into an existing routine, and the variables strShowDate and intShowNumber are declared at the beginning of the routine.

 

[chergh]

Don't have access to office atm so try:

Dim strArray() As Variant

Not best practice but should work,

 

[PatrickJohnson]

yeah, i did that and that solved my compile error. when you have opportunity though, i would love a better understanding of the best paractice.

 

[PatrickJohnson]

Okay, i got an error now when running the above code. When it gets to this line:

  i = ActiveDocument.tbl.Rows.Count

I get an error saying "Object doesn't support this property or method."

Any ideas?

 

[chergh]

Not a clue to be honest.

 

[chergh]

In the declarations add the line:

dim tbl as word.table

 

[PatrickJohnson]

I got it to work!

I put in the declaration you mentioned, but was still getting some object errors. Here is the code i ended up with, look it over for efficiency if you don't mind. Also, I've got some carriage returns or spaces or something in each field with the data, how do i get rid of those? The data is in the table like this:

Name

here is the working code:

Dim wrdapp As Word.Application
Dim wrdDoc As Word.Document
Dim tbl As Word.Table
Dim rs As Recordset
Dim Db As Database

Set Db = CurrentDb

Set wrdapp = New Word.Application
    wrdapp.Visible = True
Set wrdDoc = wrdapp.Documents.Open("Location of File")
For Each tbl In wrdDoc.Tables
  i = tbl.Rows.Count
  J = tbl.Columns.Count
    ReDim strArray(1 To i, 1 To J)
    For x = 1 To i
        For y = 1 To J
             strArray(x, y) = tbl.Cell(x, y)
        Next y
    Next x
    Set rs = Db.OpenRecordset("Destination Table")
    For i = 1 To UBound(strArray)
        rs.AddNew
            rs!Field1 = strArray(i, 1)
            rs!Field2 = strArray(i, 2)
            rs!Fieldn = strArray(i, n)
            rs.Update
    Next i
Next tbl
wrdapp.Quit (No)

I'm so much closer now! Thanks for your help!