Copy the contents of a listbox to the clipboard (1 Viewer)
- Thread starter jpl458
- Start date
- Local time
- Today, 00:50
- Joined
- Feb 19, 2002
- Messages
- 43,768
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.
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
ReturnI 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
- Local time
- Today, 00:50
- Joined
- Feb 19, 2002
- Messages
- 43,768
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.
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
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
- Local time
- Today, 00:50
- Joined
- Feb 19, 2002
- Messages
- 43,768
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.
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.
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
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:
- Local time
- Today, 00:50
- Joined
- Feb 19, 2002
- Messages
- 43,768
do you have a query named qFetchFieldNames?
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 FillFieldsSuspects 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
- Local time
- Today, 00:50
- Joined
- Feb 19, 2002
- Messages
- 43,768
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.
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.
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.
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.
- Local time
- Today, 00:50
- Joined
- Feb 19, 2002
- Messages
- 43,768
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.
Got it. Thanks.
