Copying currency values from Access to paste into Word

[clarkcovey]

Our computer system has a variety of Word templates with prompts for currency values to be entered one at a time to populate a grid within the letter.

The users have been using an Excel spreadsheet to calculate the values and then selected the group of cells and pasted them into the Word document. The spreadsheet cells were formatted with the same font type and size and since they were also formatted as currency, they were right-justified.

I am designing an Access application and want to allow the same capability, but so far, my countless hours and Internet browsing have not produced what I need.

Data is filled in on a form with a button to copy the values to the clipboard for pasting into Word. The field labels are not required, just the values.

Access form:

Word grid:

I can copy the values and paste them into Word, but they appear as unformatted text, unable to maintain the Word font type and size.

I have tried:

1. creating a right-justified, Rich-Text field on the form, copying the values into that and then to the clipboard
2. inserting spaces based on the length of the longest entry
3. passing to HTML, but did not understand all the necessary steps
4. using API, but the code I found was only for 32-bit systems
5. copying query results or directly from the table, but it includes the query/table name and field name, neither of which are pasted into the letter

I cannot use Word's Mail Merge function nor export/import as I have no control over our computer system.

Unless someone has an answer, my next attempt is to try and pass the values to Excel, formatting and copying the range without displaying a workbook.

Thank you in advance!

Clark

 

[MajP]

Maybe I am missing something, not sure I understand why all the complexities (API, HTML, Rich Text). Why not add code to the template to read from Access or code in Access to write to the Word document? Should be plenty examples on the Internet of Word Automation.

In this example I do the harder thing and show how to read from Access to populate a highly formatted form.

Any new way to get Access to Word with formatting

I am well aware of the limitations of Access-> RTF. I always explain it to my customers. However, one customer now has required standards that just cannot be met. I thought maybe Total Access Memo would work, but it doesn't seem to be able to export what it creates to Word either. Are there any...

www.access-programmers.co.uk

But telling Access to write to a Word table should be much easier.

 

[clarkcovey]

Thank you for your response MajP; however, the Word templates are generated from a completely different system and I cannot add code to them to read from Access or any other source.

Regarding Access writing to Word, would it matter if the Word document has several paragraphs of other information, followed by the grid with currency values, and then more paragraphs?

 

[MajP]

Regarding Access writing to Word, would it matter if the Word document has several paragraphs of other information, followed by the grid with currency values, and then more paragraphs?

It is a lot easier in the cases where you can modify the template because then you can either use a bookmark or a named field. Then you tell it to go to that bookmark by name. So you would have an RCV, O&P named bookmark which would be a bookmark in those cells.

If you cannot do that ahead of time then it is a little more complicated. You have to figure out the relative placement. You tell it to go to Table 3, row 2, column 2 etc.

 

[MajP]

If you can provide a document and the form I could try to demo. For the document you can replace all paragraphs with a random sentence and other tables can be blank. The form can be a simple form with just a table to hold dummy values.

 

[The_Doc_Man]

Regarding Access writing to Word, would it matter if the Word document has several paragraphs of other information, followed by the grid with currency values, and then more paragraphs?

The last time I tried this, there was a shortcut (of sorts) available. The Word Object Model allows you to find extant tables by their ordinal index. It should be possible for you to reference cells via something similar to this

WordApp.Documents(1).Tables(2).Rows(3).Cells(4) - would be the 4th column of the 3rd row of the 2nd table of the 1st word document. Of course, if you have a Word.Document object set up, you can skip the reference to the word app itself.

If this still works the way it used to, you don't care how many paragraphs exist before or after the table. NOTE that if you must first CREATE the table/grid then it is trickier. But if it exists beforehand and you are just modifying it, what I suggested should work pretty simply.

 

[clarkcovey]

If you can provide a document and the form I could try to demo. For the document you can replace all paragraphs with a random sentence and other tables can be blank. The form can be a simple form with just a table to hold dummy values.

MajP,
Here is a sample letter.

There are approximately 12-15 different letters with payment grids and the rows per grid varies between them. Some may have 3-4 rows, whereas others could have up to 11 rows.

 

[clarkcovey]

The last time I tried this, there was a shortcut (of sorts) available. The Word Object Model allows you to find extant tables by their ordinal index. It should be possible for you to reference cells via something similar to this

WordApp.Documents(1).Tables(2).Rows(3).Cells(4) - would be the 4th column of the 3rd row of the 2nd table of the 1st word document. Of course, if you have a Word.Document object set up, you can skip the reference to the word app itself.

If this still works the way it used to, you don't care how many paragraphs exist before or after the table. NOTE that if you must first CREATE the table/grid then it is trickier. But if it exists beforehand and you are just modifying it, what I suggested should work pretty simply.

The_Doc_Man,
Thank you for your suggestion.

If the user has just one doc opened at the time, I can see how that might work, especially since there is usually just one grid per letter.

I'll explore this.

 

[MajP]

Doing this requires little code. The hardest thing for most people is getting a reference to the word document. I have 2 functions that make this easy.
1. returns a reference to the open word application and if the application is not already open it opens it.

Public Function GetWordApplication() As Word.Application
  Dim wdApp As Word.Application
  On Error Resume Next
  Set wdApp = GetObject(, "Word.Application")
  If Err.Number <> 0 Then 'Word isn't already running
    Set wdApp = CreateObject("Word.Application")
  End If
  wdApp.Visible = True
  Set GetWordApplication = wdApp
End Function

2. The second allows me to pass in the path to a word document and then gets a reference immediately to it

Public Function getWordDocument(strPath As String) As Word.Document
  Dim wdApp As Word.Application
  Dim wdDoc As Word.Document
  Dim response As Long

  On Error GoTo errlbl:
  'verify it is a word doc
  If Right(strPath, 4) = ".doc" Or Right(strPath, 4) = "docx" Or Right(strPath, 4) = ".rtf" Then
    'Create an instance of MS Word
     Set wdApp = GetWordApplication
     'wdApp.WindowState = wdWindowStateMaximize
     'Open the Document
     Set getWordDocument = wdApp.Documents.Open(strPath)
   Else
     MsgBox "No word document selected"
  End If
  Exit Function
errlbl:
  MsgBox Err.Number & " " & Err.Description & " in getWordDocument"
End Function

3. Your example is extremely simple, since I am not creating the table only populating a predefined table. I also do not do anything else like formatting the cells, but that would be simple since I reference each cell. I also left an example module where I dynamically create a table to show some additional code To show that this is not hard to do. Here is the entire new code I wrote.

Public Sub PopulateWordTable(wdDoc As Word.Document)
  Dim wdTable As Word.Table
  Dim cell As Word.cell
  Dim rs As DAO.Recordset
  Dim i As Integer
  Dim costLabel As String
  Dim Cost As String
  Dim rng As Word.Range

  Set wdTable = wdDoc.Tables(2) ' 1 based
  If Not CurrentProject.AllForms("frmTableData").IsLoaded Then DoCmd.OpenForm "frmTableData"
  Set rs = Forms("frmTableData").Recordset
  rs.MoveFirst
i = 1
  Do While Not rs.EOF
    costLabel = rs!costLabel
    Cost = Format(rs!Cost, "Currency")
    i = i + 1
    Set rng = wdTable.cell(i, 1).Range
    rng.Text = costLabel
    Set rng = wdTable.cell(i, 2).Range
    rng.Text = Cost
    rs.MoveNext
  Loop
  
End Sub

The only thing that is crazy confusing is that Word makes some of their collections 1 based and not 0 based like all other object models. So the first table in the document is 1 and not 0. Everywhere else collections indices are 0 based.

Now there is not much error checking, but if you pick the correct template this works. For demo purposes try it both ways. First time open the document and some other documents. Verify nothing in the table. Then launch the code. Your open documents should appear as a choice in the list. Pick the correct document and hit OK. Should populate.
Then clear the values of the table. Close word. This time use the file browser.

Once you can reference within the document the correct range (paragraph, sentence, word, table, figure, cell, row, column, section, etc), you can pretty easily do whatever you want. The trick is learning the object model to be able to return the correct range.

 

[The_Doc_Man]

Good point about Word collections being 1-based, MajP. It has been so long that I forgot that detail in my earlier post, but you are quite right.

 

[clarkcovey]

Doing this requires little code. The hardest thing for most people is getting a reference to the word document. I have 2 functions that make this easy.
1. returns a reference to the open word application and if the application is not already open it opens it.

Public Function GetWordApplication() As Word.Application
  Dim wdApp As Word.Application
  On Error Resume Next
  Set wdApp = GetObject(, "Word.Application")
  If Err.Number <> 0 Then 'Word isn't already running
    Set wdApp = CreateObject("Word.Application")
  End If
  wdApp.Visible = True
  Set GetWordApplication = wdApp
End Function

2. The second allows me to pass in the path to a word document and then gets a reference immediately to it

Public Function getWordDocument(strPath As String) As Word.Document
  Dim wdApp As Word.Application
  Dim wdDoc As Word.Document
  Dim response As Long

  On Error GoTo errlbl:
  'verify it is a word doc
  If Right(strPath, 4) = ".doc" Or Right(strPath, 4) = "docx" Or Right(strPath, 4) = ".rtf" Then
    'Create an instance of MS Word
     Set wdApp = GetWordApplication
     'wdApp.WindowState = wdWindowStateMaximize
     'Open the Document
     Set getWordDocument = wdApp.Documents.Open(strPath)
   Else
     MsgBox "No word document selected"
  End If
  Exit Function
errlbl:
  MsgBox Err.Number & " " & Err.Description & " in getWordDocument"
End Function

3. Your example is extremely simple, since I am not creating the table only populating a predefined table. I also do not do anything else like formatting the cells, but that would be simple since I reference each cell. I also left an example module where I dynamically create a table to show some additional code To show that this is not hard to do. Here is the entire new code I wrote.

Public Sub PopulateWordTable(wdDoc As Word.Document)
  Dim wdTable As Word.Table
  Dim cell As Word.cell
  Dim rs As DAO.Recordset
  Dim i As Integer
  Dim costLabel As String
  Dim Cost As String
  Dim rng As Word.Range

  Set wdTable = wdDoc.Tables(2) ' 1 based
  If Not CurrentProject.AllForms("frmTableData").IsLoaded Then DoCmd.OpenForm "frmTableData"
  Set rs = Forms("frmTableData").Recordset
  rs.MoveFirst
i = 1
  Do While Not rs.EOF
    costLabel = rs!costLabel
    Cost = Format(rs!Cost, "Currency")
    i = i + 1
    Set rng = wdTable.cell(i, 1).Range
    rng.Text = costLabel
    Set rng = wdTable.cell(i, 2).Range
    rng.Text = Cost
    rs.MoveNext
  Loop
 
End Sub

The only thing that is crazy confusing is that Word makes some of their collections 1 based and not 0 based like all other object models. So the first table in the document is 1 and not 0. Everywhere else collections indices are 0 based.

Now there is not much error checking, but if you pick the correct template this works. For demo purposes try it both ways. First time open the document and some other documents. Verify nothing in the table. Then launch the code. Your open documents should appear as a choice in the list. Pick the correct document and hit OK. Should populate.
Then clear the values of the table. Close word. This time use the file browser.

Once you can reference within the document the correct range (paragraph, sentence, word, table, figure, cell, row, column, section, etc), you can pretty easily do whatever you want. The trick is learning the object model to be able to return the correct range.

Thank you MajP!

I will add this and let you know how it turns out.

 

[clarkcovey]

Thank you MajP!

I will add this and let you know how it turns out.

Thank you again MajP!!

This gave me a big push in the right direction and with a few minor revisions, it is now working like a charm!

The two changes I made were regarding the file path/name and the data source for populating the form.

When our claim system generates a document from a template, the specific path and name is not known ahead of time as it is assigned a temporary file name until it is published or sent to the recipient.

Since there are roughly 15-20 letters with payment grids with different number of rows, I added a drop down for the user to select the letter template name and display only the fields applicable for that letter. When they are ready to populate the Word form, I pass the values to a dynamic array, which then becomes the data source.

Fortunately, when the values were passed to the form, the font remained the same. I added an additional bit to format the data as right-justified.

Thanks!

Clark

 

[MajP]

Going back to your original problem with copying and pasting. When you have a date field or a currency field what you see is not what is stored. So you are seeing a formatted result. For example the current date and time is stored as 44407.5902893519. The currency is not stored with $ it is just a numeric. However if you use a format function it converts the numeric to a string with the proper formatting. In your form if you added another set of calculated controls with something like
=Format([valueFieldName],"Currency")
then that control is holding a string. I bet you would be able to copy and paste that with the formatting. The font is another issue.

get data from word table

Hello friends I need to get a word table to access tables I can browse the open file dialog I need to import the data only from this file word the problem I face when importing the file Enter (Newline) between and above paragraph the application converts it to a new row my data is grouped...

www.access-programmers.co.uk