With the help of Doug Robbins I was able to populate a table in Word from
Access. When I do this I brings over just the numbers no Formatting of the
numbers. It's importing Currency and Total Calculations from a qry. Is
there a way when importing from a rs to keep the formatting as well? If not
my thoughts are to insert a formula in the cell that needs formatting and
format it that way. I've looked at a few inserting formula's in vba but
nothing working as of yet.

Any thoughts or ideas would be greatly appreciated.

D

Below is the code to import data from the database into a table and paste
the data into the table

Set Db = OpenDatabase("C:\Data Bases\MyTC.mdb")
Set qry = Db.QueryDefs("qry_NewQuoteItems2")
qry.Parameters("QuoteNum") = Me.txtPrjNum & ".0"
Set rs = myActiveRecord.OpenRecordset

Set objWord = CreateObject("Word.Application")
With objWord
.Visible = True ' Make the application visible.
.Documents.Open (stPath) ' Open the document.
Set myDoc = objWord.ActiveDocument
Set oTable = objWord.ActiveDocument.Tables(1) 'Selects the Table
End With

Set drow = oTable.Rows(1)
'Loop through all the records in the table until the end-of-file marker is
reached
Do While Not rs.EOF
'Populate the cells in the Word table with the data from the current
record
For i = 1 To rs.Fields.Count
drow.Cells(i).Range.Text = rs.Fields(i - 1)
Next i 'Add a new row to the Word table and access the next record
Set drow = oTable.Rows.Add
rs.MoveNext
Loop

Access only stores the raw numbers so there are two ways you can go.

First way (probably easiest) is to create a query in Access that applies the
Format() to the fields in question to convert the information in them to
text with the desired formatting.

The other way would necessitate modifying the following lines of code

For i = 1 To rs.Fields.Count
drow.Cells(i).Range.Text = rs.Fields(i - 1)
Next i 'Add a new row to the Word table and access the next record

so that it applied the required formatting on the fly. the best way to do
this would be something like

For i = 1 To rs.Fields.Count
If i = 2 then 'Change the 2 to the field to which you need to apply
the formatting
drow.Cells(i).Range.Text = Format(rs.Fields(i - 1), "$#,##0.00")
Else
drow.Cells(i).Range.Text = rs.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

"MTechG" wrote in message
...
With the help of Doug Robbins I was able to populate a table in Word from
Access. When I do this I brings over just the numbers no Formatting of
the
numbers. It's importing Currency and Total Calculations from a qry. Is
there a way when importing from a rs to keep the formatting as well? If
not
my thoughts are to insert a formula in the cell that needs formatting and
format it that way. I've looked at a few inserting formula's in vba but
nothing working as of yet.

Any thoughts or ideas would be greatly appreciated.

D

Below is the code to import data from the database into a table and paste
the data into the table

Set Db = OpenDatabase("C:\Data Bases\MyTC.mdb")
Set qry = Db.QueryDefs("qry_NewQuoteItems2")
qry.Parameters("QuoteNum") = Me.txtPrjNum & ".0"
Set rs = myActiveRecord.OpenRecordset

Set objWord = CreateObject("Word.Application")
With objWord
.Visible = True ' Make the application visible.
.Documents.Open (stPath) ' Open the document.
Set myDoc = objWord.ActiveDocument
Set oTable = objWord.ActiveDocument.Tables(1) 'Selects the Table
End With

Set drow = oTable.Rows(1)
'Loop through all the records in the table until the end-of-file marker is
reached
Do While Not rs.EOF
'Populate the cells in the Word table with the data from the current
record
For i = 1 To rs.Fields.Count
drow.Cells(i).Range.Text = rs.Fields(i - 1)
Next i 'Add a new row to the Word table and access the next record
Set drow = oTable.Rows.Add
rs.MoveNext
Loop

I actually have the query formatted right but like I said it doesn't bring
any of the formatting with it... I'll give your other method a shot I like
the sounds of it just looking at it.

Thanks
D

"Doug Robbins - Word MVP" wrote:

Access only stores the raw numbers so there are two ways you can go.

First way (probably easiest) is to create a query in Access that applies the
Format() to the fields in question to convert the information in them to
text with the desired formatting.

The other way would necessitate modifying the following lines of code

For i = 1 To rs.Fields.Count
drow.Cells(i).Range.Text = rs.Fields(i - 1)
Next i 'Add a new row to the Word table and access the next record

so that it applied the required formatting on the fly. the best way to do
this would be something like

For i = 1 To rs.Fields.Count
If i = 2 then 'Change the 2 to the field to which you need to apply
the formatting
drow.Cells(i).Range.Text = Format(rs.Fields(i - 1), "$#,##0.00")
Else
drow.Cells(i).Range.Text = rs.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

"MTechG" wrote in message
...
With the help of Doug Robbins I was able to populate a table in Word from
Access. When I do this I brings over just the numbers no Formatting of
the
numbers. It's importing Currency and Total Calculations from a qry. Is
there a way when importing from a rs to keep the formatting as well? If
not
my thoughts are to insert a formula in the cell that needs formatting and
format it that way. I've looked at a few inserting formula's in vba but
nothing working as of yet.

Any thoughts or ideas would be greatly appreciated.

D

Below is the code to import data from the database into a table and paste
the data into the table

Set Db = OpenDatabase("C:\Data Bases\MyTC.mdb")
Set qry = Db.QueryDefs("qry_NewQuoteItems2")
qry.Parameters("QuoteNum") = Me.txtPrjNum & ".0"
Set rs = myActiveRecord.OpenRecordset

Set objWord = CreateObject("Word.Application")
With objWord
.Visible = True ' Make the application visible.
.Documents.Open (stPath) ' Open the document.
Set myDoc = objWord.ActiveDocument
Set oTable = objWord.ActiveDocument.Tables(1) 'Selects the Table
End With

Set drow = oTable.Rows(1)
'Loop through all the records in the table until the end-of-file marker is
reached
Do While Not rs.EOF
'Populate the cells in the Word table with the data from the current
record
For i = 1 To rs.Fields.Count
drow.Cells(i).Range.Text = rs.Fields(i - 1)
Next i 'Add a new row to the Word table and access the next record
Set drow = oTable.Rows.Add
rs.MoveNext
Loop

You need to use the Format() function in and expression in the Query, not
have the formatting applied by use of the Properties

--
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

"MTechG" wrote in message
...
I actually have the query formatted right but like I said it doesn't bring
any of the formatting with it... I'll give your other method a shot I
like
the sounds of it just looking at it.

Thanks
D

"Doug Robbins - Word MVP" wrote:

Access only stores the raw numbers so there are two ways you can go.

First way (probably easiest) is to create a query in Access that applies
the
Format() to the fields in question to convert the information in them to
text with the desired formatting.

The other way would necessitate modifying the following lines of code

For i = 1 To rs.Fields.Count
drow.Cells(i).Range.Text = rs.Fields(i - 1)
Next i 'Add a new row to the Word table and access the next record

so that it applied the required formatting on the fly. the best way to
do
this would be something like

For i = 1 To rs.Fields.Count
If i = 2 then 'Change the 2 to the field to which you need to
apply
the formatting
drow.Cells(i).Range.Text = Format(rs.Fields(i - 1),
"$#,##0.00")
Else
drow.Cells(i).Range.Text = rs.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

"MTechG" wrote in message
...
With the help of Doug Robbins I was able to populate a table in Word
from
Access. When I do this I brings over just the numbers no Formatting of
the
numbers. It's importing Currency and Total Calculations from a qry.
Is
there a way when importing from a rs to keep the formatting as well?
If
not
my thoughts are to insert a formula in the cell that needs formatting
and
format it that way. I've looked at a few inserting formula's in vba
but
nothing working as of yet.

Any thoughts or ideas would be greatly appreciated.

D

Below is the code to import data from the database into a table and
paste
the data into the table

Set Db = OpenDatabase("C:\Data Bases\MyTC.mdb")
Set qry = Db.QueryDefs("qry_NewQuoteItems2")
qry.Parameters("QuoteNum") = Me.txtPrjNum & ".0"
Set rs = myActiveRecord.OpenRecordset

Set objWord = CreateObject("Word.Application")
With objWord
.Visible = True ' Make the application visible.
.Documents.Open (stPath) ' Open the document.
Set myDoc = objWord.ActiveDocument
Set oTable = objWord.ActiveDocument.Tables(1) 'Selects the Table
End With

Set drow = oTable.Rows(1)
'Loop through all the records in the table until the end-of-file marker
is
reached
Do While Not rs.EOF
'Populate the cells in the Word table with the data from the current
record
For i = 1 To rs.Fields.Count
drow.Cells(i).Range.Text = rs.Fields(i - 1)
Next i 'Add a new row to the Word table and access the next record
Set drow = oTable.Rows.Add
rs.MoveNext
Loop

Doug

I figured the Query Formatting out this morning. Thanks again for all your
help.

Dennis

"Doug Robbins - Word MVP" wrote:

You need to use the Format() function in and expression in the Query, not
have the formatting applied by use of the Properties

--
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

"MTechG" wrote in message
...
I actually have the query formatted right but like I said it doesn't bring
any of the formatting with it... I'll give your other method a shot I
like
the sounds of it just looking at it.

Thanks
D

"Doug Robbins - Word MVP" wrote:

Access only stores the raw numbers so there are two ways you can go.

First way (probably easiest) is to create a query in Access that applies
the
Format() to the fields in question to convert the information in them to
text with the desired formatting.

The other way would necessitate modifying the following lines of code

For i = 1 To rs.Fields.Count
drow.Cells(i).Range.Text = rs.Fields(i - 1)
Next i 'Add a new row to the Word table and access the next record

so that it applied the required formatting on the fly. the best way to
do
this would be something like

For i = 1 To rs.Fields.Count
If i = 2 then 'Change the 2 to the field to which you need to
apply
the formatting
drow.Cells(i).Range.Text = Format(rs.Fields(i - 1),
"$#,##0.00")
Else
drow.Cells(i).Range.Text = rs.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

"MTechG" wrote in message
...
With the help of Doug Robbins I was able to populate a table in Word
from
Access. When I do this I brings over just the numbers no Formatting of
the
numbers. It's importing Currency and Total Calculations from a qry.
Is
there a way when importing from a rs to keep the formatting as well?
If
not
my thoughts are to insert a formula in the cell that needs formatting
and
format it that way. I've looked at a few inserting formula's in vba
but
nothing working as of yet.

Any thoughts or ideas would be greatly appreciated.

D

Below is the code to import data from the database into a table and
paste
the data into the table

Set Db = OpenDatabase("C:\Data Bases\MyTC.mdb")
Set qry = Db.QueryDefs("qry_NewQuoteItems2")
qry.Parameters("QuoteNum") = Me.txtPrjNum & ".0"
Set rs = myActiveRecord.OpenRecordset

Set objWord = CreateObject("Word.Application")
With objWord
.Visible = True ' Make the application visible.
.Documents.Open (stPath) ' Open the document.
Set myDoc = objWord.ActiveDocument
Set oTable = objWord.ActiveDocument.Tables(1) 'Selects the Table
End With

Set drow = oTable.Rows(1)
'Loop through all the records in the table until the end-of-file marker
is
reached
Do While Not rs.EOF
'Populate the cells in the Word table with the data from the current
record
For i = 1 To rs.Fields.Count
drow.Cells(i).Range.Text = rs.Fields(i - 1)
Next i 'Add a new row to the Word table and access the next record
Set drow = oTable.Rows.Add
rs.MoveNext
Loop