Copy the contents of a listbox to the clipboard

Attached is the Word doc.
 

Attachments

Forget the first part where I described the complex solution that allows users to define new fields. You don't need that. Read the second part where I described the simple solution.

Depending on whether you are printing one document at a time or multiple copies of the same document, you would open a recordset created from a query that either selects ONE record or many records. Then you would loop through the recordset. For each record, you would use GoSub to fill the field. The following code may be more complicated than you need. In my behind the scenes field definition table (which is used as the RowSource for the combo in the form I pasted above), it has a type code that says what type of data you have. I used a combination of bookmarks, formfields, and checkboxes and each is filled differently. The formfield and checkbox controls are the "old" style so I'm not sure the code works with the "new" style. I never checked it. You can try it and let me know. Anyway. It loops through the fields collection of the recordset so make sure you only include fields you want to use.

I think you can format dates and phone numbers in the query and not need to do it in the case below so you can get rid of most of the case.

I extracted this code from a very complicated procedure. I think this is all you need.
Code: 
    Dim db              As DAO.Database
    Dim rsFields        As DAO.Recordset
    Dim qdData          As DAO.QueryDef

    Set db = CurrentDb()
    Set qdFields = db.QueryDefs!qFetchFieldNames
        qdFields.Parameters![EnterDataSourceName] = "tblMembers"
        qdFields.Parameters![EnterDocID] = DocumentID
    Set rsFields = qdFields.OpenRecordset(dbOpenDynaset, dbSeeChanges)
''''' You may need a loop here if your query selects more than one record for printing.
    GoSub FillFields

ExitSub:
    Exit Sub

FillFields:
    Do While rsFields.EOF = False
        Select Case rsFields!FieldType
            Case "Memo"
                If IsNull(rsData.Fields(rsFields!FieldDescription)) Then
                    WordDoc.FormFields(rsFields!BookMarkName).Result = ""
                Else
                    WordDoc.Bookmarks(rsFields!BookMarkName).Range.Fields(1).Result.Text = rsData.Fields(rsFields!FieldDescription)
                End If
            Case "Ckbox"
                If IsNull(rsData.Fields(rsFields!FieldDescription)) Then
                Else
                    WordDoc.FormFields(rsFields!BookMarkName).CheckBox.Value = rsData.Fields(rsFields!FieldDescription)
                End If
            Case "Date"
                If IsDate(rsData.Fields(rsFields!FieldDescription)) Then
                    WordDoc.FormFields(rsFields!BookMarkName).Result = Format(rsData.Fields(rsFields!FieldDescription), "mmmm dd, yyyy")
                Else
                    WordDoc.FormFields(rsFields!BookMarkName).Result = ""
                End If
            Case "Phone"
                If IsNumeric(rsData.Fields(rsFields!FieldDescription)) Then
                    WordDoc.FormFields(rsFields!BookMarkName).Result = Format(rsData.Fields(rsFields!FieldDescription), "\(###\) 000\-0000")
                Else
                    WordDoc.FormFields(rsFields!BookMarkName).Result = ""
                End If
            Case Else
                If IsNull(rsData.Fields(rsFields!FieldDescription)) Then
                    WordDoc.FormFields(rsFields!BookMarkName).Result = ""
                Else
                    WordDoc.FormFields(rsFields!BookMarkName).Result = rsData.Fields(rsFields!FieldDescription)
                End If
        End Select
    rsFields.MoveNext
    Loop
Return
 
Pat Hartman said:
Forget the first part where I described the complex solution that allows users to define new fields. You don't need that. Read the second part where I described the simple solution.

Depending on whether you are printing one document at a time or multiple copies of the same document, you would open a recordset created from a query that either selects ONE record or many records. Then you would loop through the recordset. For each record, you would use GoSub to fill the field. The following code may be more complicated than you need. In my behind the scenes field definition table (which is used as the RowSource for the combo in the form I pasted above), it has a type code that says what type of data you have. I used a combination of bookmarks, formfields, and checkboxes and each is filled differently. The formfield and checkbox controls are the "old" style so I'm not sure the code works with the "new" style. I never checked it. You can try it and let me know. Anyway. It loops through the fields collection of the recordset so make sure you only include fields you want to use.

I think you can format dates and phone numbers in the query and not need to do it in the case below so you can get rid of most of the case.

I extracted this code from a very complicated procedure. I think this is all you need.
Code: 
    Dim db              As DAO.Database
    Dim rsFields        As DAO.Recordset
    Dim qdData          As DAO.QueryDef

    Set db = CurrentDb()
    Set qdFields = db.QueryDefs!qFetchFieldNames
        qdFields.Parameters![EnterDataSourceName] = "tblMembers"
        qdFields.Parameters![EnterDocID] = DocumentID
    Set rsFields = qdFields.OpenRecordset(dbOpenDynaset, dbSeeChanges)
''''' You may need a loop here if your query selects more than one record for printing.
    GoSub FillFields

ExitSub:
    Exit Sub

FillFields:
    Do While rsFields.EOF = False
        Select Case rsFields!FieldType
            Case "Memo"
                If IsNull(rsData.Fields(rsFields!FieldDescription)) Then
                    WordDoc.FormFields(rsFields!BookMarkName).Result = ""
                Else
                    WordDoc.Bookmarks(rsFields!BookMarkName).Range.Fields(1).Result.Text = rsData.Fields(rsFields!FieldDescription)
                End If
            Case "Ckbox"
                If IsNull(rsData.Fields(rsFields!FieldDescription)) Then
                Else
                    WordDoc.FormFields(rsFields!BookMarkName).CheckBox.Value = rsData.Fields(rsFields!FieldDescription)
                End If
            Case "Date"
                If IsDate(rsData.Fields(rsFields!FieldDescription)) Then
                    WordDoc.FormFields(rsFields!BookMarkName).Result = Format(rsData.Fields(rsFields!FieldDescription), "mmmm dd, yyyy")
                Else
                    WordDoc.FormFields(rsFields!BookMarkName).Result = ""
                End If
            Case "Phone"
                If IsNumeric(rsData.Fields(rsFields!FieldDescription)) Then
                    WordDoc.FormFields(rsFields!BookMarkName).Result = Format(rsData.Fields(rsFields!FieldDescription), "\(###\) 000\-0000")
                Else
                    WordDoc.FormFields(rsFields!BookMarkName).Result = ""
                End If
            Case Else
                If IsNull(rsData.Fields(rsFields!FieldDescription)) Then
                    WordDoc.FormFields(rsFields!BookMarkName).Result = ""
                Else
                    WordDoc.FormFields(rsFields!BookMarkName).Result = rsData.Fields(rsFields!FieldDescription)
                End If
        End Select
    rsFields.MoveNext
    Loop
Return
Click to expand...
I will have to puzzle on this for a while. A lot for me to chew on. From reading the code it seems I have to deletes some case and add for name, address, etc.

I will chew, then probably get back with a question or +.

Thanks again
 
I fixed the problem with the tool. See if this works for your document now.

Word Automation Sample

This database presents an example of using VBA to fill bookmarked fields in a Word document. The Database is A2003 format. However, the only version of Word installed on my PC is W2007 so before using the example, you may need to open any code module and change the Word reference to whatever...
www.access-programmers.co.uk
 
I was going to get some other things done first, but I couldn't wait.

1669050916494.png


Worked like a champ. You brows to a directory and get all the bookmarks in all the docs in that directory. Just wondering if there would ever be a need to point at a single document to get it's bookmarks. Just an idle thought. What I did was move my test doc to new folder then pointed the tool at it and pulled the trigger, and out popped the above.

Very cool, indeed

Thanks, yet again
 
If you want only a single document to be analyzed, then do what you did.

I built the tool 15 years ago because the users already had thousands of documents built and in order to ensure the app I built would work correctly, I needed to validate it against the existing documents. So, I processed all the documents for a single LOB (Line of Business) at a time. This was the first step. Later steps used the data to create mapping tables. For each bookmark, the user picked the query field if the names were not identical. This saved the users a lot of work getting the initial mapping data into the application. Luckily for me/them/us the users were pretty conscientious about their naming standards.
 
Pat Hartman said:
Forget the first part where I described the complex solution that allows users to define new fields. You don't need that. Read the second part where I described the simple solution.

Depending on whether you are printing one document at a time or multiple copies of the same document, you would open a recordset created from a query that either selects ONE record or many records. Then you would loop through the recordset. For each record, you would use GoSub to fill the field. The following code may be more complicated than you need. In my behind the scenes field definition table (which is used as the RowSource for the combo in the form I pasted above), it has a type code that says what type of data you have. I used a combination of bookmarks, formfields, and checkboxes and each is filled differently. The formfield and checkbox controls are the "old" style so I'm not sure the code works with the "new" style. I never checked it. You can try it and let me know. Anyway. It loops through the fields collection of the recordset so make sure you only include fields you want to use.

I think you can format dates and phone numbers in the query and not need to do it in the case below so you can get rid of most of the case.

I extracted this code from a very complicated procedure. I think this is all you need.
Code: 
    Dim db              As DAO.Database
    Dim rsFields        As DAO.Recordset
    Dim qdData          As DAO.QueryDef

    Set db = CurrentDb()
    Set qdFields = db.QueryDefs!qFetchFieldNames
        qdFields.Parameters![EnterDataSourceName] = "tblMembers"
        qdFields.Parameters![EnterDocID] = DocumentID
    Set rsFields = qdFields.OpenRecordset(dbOpenDynaset, dbSeeChanges)
''''' You may need a loop here if your query selects more than one record for printing.
    GoSub FillFields

ExitSub:
    Exit Sub

FillFields:
    Do While rsFields.EOF = False
        Select Case rsFields!FieldType
            Case "Memo"
                If IsNull(rsData.Fields(rsFields!FieldDescription)) Then
                    WordDoc.FormFields(rsFields!BookMarkName).Result = ""
                Else
                    WordDoc.Bookmarks(rsFields!BookMarkName).Range.Fields(1).Result.Text = rsData.Fields(rsFields!FieldDescription)
                End If
            Case "Ckbox"
                If IsNull(rsData.Fields(rsFields!FieldDescription)) Then
                Else
                    WordDoc.FormFields(rsFields!BookMarkName).CheckBox.Value = rsData.Fields(rsFields!FieldDescription)
                End If
            Case "Date"
                If IsDate(rsData.Fields(rsFields!FieldDescription)) Then
                    WordDoc.FormFields(rsFields!BookMarkName).Result = Format(rsData.Fields(rsFields!FieldDescription), "mmmm dd, yyyy")
                Else
                    WordDoc.FormFields(rsFields!BookMarkName).Result = ""
                End If
            Case "Phone"
                If IsNumeric(rsData.Fields(rsFields!FieldDescription)) Then
                    WordDoc.FormFields(rsFields!BookMarkName).Result = Format(rsData.Fields(rsFields!FieldDescription), "\(###\) 000\-0000")
                Else
                    WordDoc.FormFields(rsFields!BookMarkName).Result = ""
                End If
            Case Else
                If IsNull(rsData.Fields(rsFields!FieldDescription)) Then
                    WordDoc.FormFields(rsFields!BookMarkName).Result = ""
                Else
                    WordDoc.FormFields(rsFields!BookMarkName).Result = rsData.Fields(rsFields!FieldDescription)
                End If
        End Select
    rsFields.MoveNext
    Loop
Return
Click to expand...
Working on code you sent, and will ask one question at a time, trying not to waste your time. Stepping through code slowly.

In this section of the code I have entered names where you say to enter names:

Code: 
 Set db = CurrentDb()
    Set qdFields = db.QueryDefs!qFetchFieldNames
        qdFields.Parameters![QryGetDLData] = "tblMembers"
        qdFields.Parameters![Templete1.docx] = DocumentID
    Set rsFields = qdFields.OpenRecordset(dbOpenDynaset, dbSeeChanges)

When it gets to Set qdFields command I get this error

1669065668591.png


Was I correct in adding what I did? Doing a lot of reading re VBA Set and Parameters. But, for me, it's still like looking into a barrel of eels.
In VBA does Set a reference to an object mean get the objects address? (I still think in assembler)
 
Last edited:
Pat Hartman said:
do you have a query named qFetchFieldNames?
Click to expand...
I am trying to figure out how to create a query that only returns the column names. I think that is what I need. I understand all the code except the setup.

Code: 
 Set db = CurrentDb()
    Set qdFields = db.QueryDefs!qFetchFieldNames
        qdFields.Parameters![Suspects] = "tblMembers"
        qdFields.Parameters![TemplateTest] = DocumentID
    Set rsFields = qdFields.OpenRecordset(dbOpenDynaset, dbSeeChanges)
''''' You may need a loop here if your query selects more than one record for printing.
    GoSub FillFields

Suspects is a table with names and addresses, etc. TemplateTest is a docx in Word. I've tried to find what the .Parameters does, but it's vague on the web. I am in unexplored territory here, but trying to find my way through.

Thanks
 
One step at a time. Make the procedure work for a query that selects a single record. Once that works, add the loop. This would have gone a lot easier if you had just used the sample and hardcoded the fields to make your document work since you are a long way from understanding the process. I do not have a working sample I can post so you are getting this piecemeal and essentially I am working privately for you to make this work which was not my intention. I was trying to show you how OLE automation works and tell you how to proceed from there if you had more than the most basic of requirements.

The query here is the one that pulls the list of bookmarks for a specific document. The two tables in the query are the list of all the defined fields and the bookmarks mapped to a specific document. The Parameters provide the criteria. [EnterDocID] is the parameter that specifies which DocID we want from the bookmarks table and [EnterDataSource] is the parameter that specifies which fields we want from the fields table. The two tables are joined on FieldID. this is a partially complete sample so it isn't quite how the next sample database will work but for now it will be OK. The "tblMembers" is hardcoded in the sample code. Replace this with the name of the table or query that will pull the data you want. I think it actually isn't used. I think the recordsource is also created from a hardcoded query. You should see that later in the FillFields GoSub.

You can take a short cut for now and add the FieldName, FieldDescription, and FieldType to tblBookmarks. This will duplicate the data but it simplifies the process until I can build the whole thing in a coherent sample. There is a case statement in some code I posted that shows what the FieldType is used for (formatting the field for Excel). You may even be able to avoid that if you format the field in your query. The problem arises with fields like phone numbers. They are ( or should be) stored as 10 digit strings in the table. but you want them displayed as (xxx) xxx-xxxxx and you do that on forms/reports with a mask. That doesn't apply here so you actually need to format the field to export it to Word or Excel.

As you follow the code in the FillFields you'll see that this is the query that controls the filling because it has the field names.
xxxQuery.JPG
 
Pat Hartman said:
One step at a time. Make the procedure work for a query that selects a single record. Once that works, add the loop. This would have gone a lot easier if you had just used the sample and hardcoded the fields to make your document work since you are a long way from understanding the process. I do not have a working sample I can post so you are getting this piecemeal and essentially I am working privately for you to make this work which was not my intention. I was trying to show you how OLE automation works and tell you how to proceed from there if you had more than the most basic of requirements.

The query here is the one that pulls the list of bookmarks for a specific document. The two tables in the query are the list of all the defined fields and the bookmarks mapped to a specific document. The Parameters provide the criteria. [EnterDocID] is the parameter that specifies which DocID we want from the bookmarks table and [EnterDataSource] is the parameter that specifies which fields we want from the fields table. The two tables are joined on FieldID. this is a partially complete sample so it isn't quite how the next sample database will work but for now it will be OK. The "tblMembers" is hardcoded in the sample code. Replace this with the name of the table or query that will pull the data you want. I think it actually isn't used. I think the recordsource is also created from a hardcoded query. You should see that later in the FillFields GoSub.

You can take a short cut for now and add the FieldName, FieldDescription, and FieldType to tblBookmarks. This will duplicate the data but it simplifies the process until I can build the whole thing in a coherent sample. There is a case statement in some code I posted that shows what the FieldType is used for (formatting the field for Excel). You may even be able to avoid that if you format the field in your query. The problem arises with fields like phone numbers. They are ( or should be) stored as 10 digit strings in the table. but you want them displayed as (xxx) xxx-xxxxx and you do that on forms/reports with a mask. That doesn't apply here so you actually need to format the field to export it to Word or Excel.

As you follow the code in the FillFields you'll see that this is the query that controls the filling because it has the field names.
View attachment 104808
Click to expand...
Pat, let me play with this on my own for a while. I've taken too much of your time, and I really appreciate it. I have learned a lot, or, at least something. Let me see if I can get something going on my own, but this last post cleared up things a lot.
Pat Hartman said:
One step at a time. Make the procedure work for a query that selects a single record. Once that works, add the loop. This would have gone a lot easier if you had just used the sample and hardcoded the fields to make your document work since you are a long way from understanding the process. I do not have a working sample I can post so you are getting this piecemeal and essentially I am working privately for you to make this work which was not my intention. I was trying to show you how OLE automation works and tell you how to proceed from there if you had more than the most basic of requirements.

The query here is the one that pulls the list of bookmarks for a specific document. The two tables in the query are the list of all the defined fields and the bookmarks mapped to a specific document. The Parameters provide the criteria. [EnterDocID] is the parameter that specifies which DocID we want from the bookmarks table and [EnterDataSource] is the parameter that specifies which fields we want from the fields table. The two tables are joined on FieldID. this is a partially complete sample so it isn't quite how the next sample database will work but for now it will be OK. The "tblMembers" is hardcoded in the sample code. Replace this with the name of the table or query that will pull the data you want. I think it actually isn't used. I think the recordsource is also created from a hardcoded query. You should see that later in the FillFields GoSub.

You can take a short cut for now and add the FieldName, FieldDescription, and FieldType to tblBookmarks. This will duplicate the data but it simplifies the process until I can build the whole thing in a coherent sample. There is a case statement in some code I posted that shows what the FieldType is used for (formatting the field for Excel). You may even be able to avoid that if you format the field in your query. The problem arises with fields like phone numbers. They are ( or should be) stored as 10 digit strings in the table. but you want them displayed as (xxx) xxx-xxxxx and you do that on forms/reports with a mask. That doesn't apply here so you actually need to format the field to export it to Word or Excel.

As you follow the code in the FillFields you'll see that this is the query that controls the filling because it has the field names.
View attachment 104808
Click to expand...
Let me try and work things out, based on this last post. I've taken too much of your time already. Thanks again for all your help, I really appreciate it.
 
The rsData references are to the query that pulls the data. The rsData query controls the outer loop which at the moment, isn't a loop. It just reads one record.
 
You must log in or register to reply here.

Similar threads

oxicottin
Solved Out Of Memory error when emailing
Replies
8
Views
252
oxicottin
oxicottin
T
Can someone please assist with the VBA code Below - Error Message 3075
Replies
16
Views
387
CJ_London
C
M
Refresh Listbox Query
Replies
6
Views
191
monika_V
M
N
Solved How to grab the last updated record in a table
Replies
10
Views
332
June7
June7
D
Calendar displaying autonumber from a combo box..
Replies
5
Views
172
Pat Hartman
P

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Back
Top Bottom