Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.word.tables
|
|||
|
|||
Formulas w/Imported Data
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 |
#2
Posted to microsoft.public.word.tables
|
|||
|
|||
Formulas w/Imported Data
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 |
#3
Posted to microsoft.public.word.tables
|
|||
|
|||
Formulas w/Imported Data
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 |
#4
Posted to microsoft.public.word.tables
|
|||
|
|||
Formulas w/Imported Data
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 |
#5
Posted to microsoft.public.word.tables
|
|||
|
|||
Formulas w/Imported Data
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 |
Reply |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do I create a read-only mail merge data source? | Mailmerge | |||
How do I consolidate multiple mail merge data files into one file | Mailmerge | |||
DotNet Data Table as Mail Merge Data Source | Mailmerge | |||
View Merged Data Button not Working | Mailmerge | |||
Specific Email Merge w/ Specific Attachements | Mailmerge |