Access or Excel table into word

[Renee Voice]

Dear All

I have another problem.

I have to import a table (it can be in an Excel spreadsheet) into a table in
Word. The table in Word has a lot of formatting. I want to preserve the
formatting when I bring in the table. Is it at all possible?

I do appreciate any help I can get.

Thanks
Renee

 

[Jezebel]

Is this a one-off import, or does the Word document have to be updated wach
time the source changes? If it's a one-off, the simple answer is no. You
*could* do it by bringing in the values one at a time; but that's likely to
much slower than importing the whole table in one step and re-formatting.

 

[Renee Voice]

Hi Jezebel

Thanks for your reply.

I have managed to get the data to be imported and have sorted out the format
in the Word table (used the table style). The only problem I have now is
that it brings in numbers instead of percentages. I am importing an excel
file which is formatted as percentages but when I bring it in, it removes the
format and imports the decimal point number. It has to be a percentage. Do
you know how to do this?

Your help is much appreciated.

Regards
Renee

 

[Jezebel]

Numbers in Excel are always just numbers; 'percentage' is simply one of the
formats a number can take. If you've already imported the numbers, then
you'll need to convert them yourself at the Word end. If you're importing
them individually as fields, you can specify the display format within the
field.

 

[Renee Voice]

Thanks for the reply. The data is imported through the insert database
option in Word. How would I then convert them to percentage? I can probably
do it through vba in Access before it is imported but I was wondering whether
there is a way in Word.

Thanks
Renee

 

[Doug Robbins - Word MVP]

Here is some code that imports the data from an Access table into a table in
a Word document

Dim myDataBase As Database
Dim myActiveRecord As Recordset
Dim i As Long
Dim dtable As Table, drow As Row
'Open a database
Set myDataBase = OpenDatabase("c:\Access\Procurement Plan.mdb")
'Access the first record from a particular table
Set myActiveRecord = myDataBase.OpenRecordset("Currencies",
dbOpenForwardOnly)
'Add a table to the document with one row and as many fields as there are in
the database table
Set dtable = ActiveDocument.Tables.Add(Range:=Selection.Range, NumRows:=1,
numcolumns:=myActiveRecord.Fields.Count)
Set drow = dtable.Rows(1)
'Loop through all the records in the table until the end-of-file marker is
reached
Do While Not myActiveRecord.EOF
'Populate the cells in the Word table with the data from the current
record
For i = 1 To myActiveRecord.Fields.Count
drow.Cells(i).Range.Text = myActiveRecord.Fields(i - 1)
Next i
'Add a new row to the Word table and access the next record
Set drow = dtable.Rows.Add
myActiveRecord.MoveNext
Loop
'The last row will be empty, so delete it
drow.Delete
'Then close the database
myActiveRecord.Close
myDataBase.Close

If you know which field contains the percentage, say it is the third field,
the in place of

For i = 1 To myActiveRecord.Fields.Count
drow.Cells(i).Range.Text = myActiveRecord.Fields(i - 1)
Next i

in the above, you could use

For i = 1 To myActiveRecord.Fields.Count
If i = 3 then
drow.Cells(i).Range.Text = Format((100 *
myActiveRecord.Fields(i - 1)), "#.00%")
Else
drow.Cells(i).Range.Text = myActiveRecord.Fields(i - 1)
End If
Next i



--
Hope this helps.

Please reply to the newsgroup unless you wish to avail yourself of my
services on a paid consulting basis.

Doug Robbins - Word MVP

 

[Renee Voice]

Dear Doug

Thanks for your help once again.

I am unfortunately bringing in the data from Excel into Word. I have tried
to manipulate the data as you suggested but to no avail.

Here is the sample of the code:
strExcelFle = strPth & "Investper.xls"
.Goto What:=wdGoToBookmark, Name:="AppendixA"
.Range.InsertDatabase Format:=0, Style:=0, LinkToSource:=False, _
Connection:= _
"Provider=Microsoft.Jet.OLEDB.4.0;Password="""";User ID=Admin;Data
Source=strExcelFle;Mode=Read;Extended Properties=""HDR=YES;IMEX=1;"";Jet
OLEDB:System database="""";Jet OLEDB:Registry Path="""";Jet OLEDBatabase
Password="""";Jet OLEDB:Engine Type=35;Je" _
, SQLStatement:="SELECT * FROM `tblInvest$`" & "", PasswordDocument:="", _
PasswordTemplate:="", WritePasswordDocument:="", WritePasswordTemplate:= _
"", DataSource:=strExcelFle, From:=-1, To:=-1, IncludeFields _
:=True
.Style = ActiveDocument.Styles("TableFormat")

Your help will be greatly appreciated.

Thanks
Renee

 

[Doug Robbins - Word MVP]

If you already have the table in Word, you could run a macro over the cells
that uses the Format() function to convert the data in them to percentages.

Dim atable As Table
Dim acell As Range
Dim i As Long
Set atable = ActiveDocument.Bookmarks("Appendix1").Range.Tables(1)
For i = 2 To atable.Rows.Count
Set acell = atable.Cell(2, n).Range
acell.End = acell.End - 1
acell.Text = Format(Val(acell) * 100, "#.0%")
Next i

where n is the column containing the data that you wish to appear as
percentages and assuming that the first row containing such data is the
second row of the table.

--
Hope this helps.

Please reply to the newsgroup unless you wish to avail yourself of my
services on a paid consulting basis.

Doug Robbins - Word MVP

 

[Renee Voice]

Dear Doug

Yes Yes this is great. I was trying to do something like this myself.

Thank you - I will now go and give it a try.

Regards
Renee

 

[j]

Re. your response to Renee Voice about "insertdatabase"

I have a similar problem - I have used "insertdatabase" very
successfully, with code in Access to put a table directly into Word.
However, I can only use it with a second (or parallel) database - not
the actual database where the vba code resides. I would like to
continue to use this code in Access but to copy a table from the
currentdatabase directly into Word. (I found too many problems using
other techniques for moving data from Access to Word -particularly with
large numbers of fields and memo fields holding large amounts of text).
How do I access the current database (no success with currentdb). If
you could give me a clue about how to achieve this I would be very
grateful.

Thanks

 

[Doug Robbins - Word MVP]

DoCmd.OutputTo acOutputTable, "[tablename]", "RichTextFormat(*.rtf)",
"[filedrive:\path\name]"

--
Hope this helps.

Please reply to the newsgroup unless you wish to avail yourself of my
services on a paid consulting basis.

Doug Robbins - Word MVP