Copy the contents of a listbox to the clipboard (1 Viewer)

[jpl458]

For either Pat or Doc, I know you think I'm, obsessing on this but I truly need it.
I found this code at loops through the list box and puts the contents in the clipboard:

Dim mySym As String
Dim i As Long
Dim strClipText As DataObject
Dim strInputText As String

For i = 0 To DisplayQrylb.ListCount - 1
mySym = mySym & DisplayQrylb.List(i)
mySym = mySym & vbCrLf
Next


Set strClipText = New DataObject
strInputText = mySym

strClipText.SetText strInputText
strClipText.PutInClipboard

When the 3rd line runs I get this

Dim strClipText As DataObject

IS the line that causes the error. Repairing that is outside the envelope of my experience.

 

[ebs17]

You need a reference to Microsoft Office X.0 Object Library or a variant with late binding.

Sub TXTtoCB()
    Const csText = "hallo welt"
    
    ' bei Verweis auf MS Office X.0 Object Library
    'Dim cb As New DataObject
    
    Dim cb As Object        ' Late Binding

    Set cb = CreateObject("new:{1C3B4210-F441-11CE-B9EA-00AA006B1A69}")
    With cb
        .SetText csText
        .PutInClipboard
    End With
End Sub

 

[Pat Hartman]

I guess the code doesn't actually work.

You are assuming that you KNOW the solution and we just don't understand. You just don't know how to code it. But chances are excellent that the clipboard is not the solution at all. You told us in the other thread that you wanted to put the data into a Word Doc and so I sent you a working example that loads a TABLE in several formats, which is essentially what you've got, into a bookmark. In one variation it also formats the data placed into the bookmark using one of the built in Word table formats. Did you bother to examine the example I posted? It shows you how to transfer both single fields and table data so I know it will solve your problem.

 

[jpl458]

I guess the code doesn't actually work.

You are assuming that you KNOW the solution and we just don't understand. You just don't know how to code it. But chances are excellent that the clipboard is not the solution at all. You told us in the other thread that you wanted to put the data into a Word Doc and so I sent you a working example that loads a TABLE in several formats, which is essentially what you've got, into a bookmark. In one variation it also formats the data placed into the bookmark using one of the built in Word table formats. Did you bother to examine the example I posted? It shows you how to transfer both single fields and table data so I know it will solve your problem.
View attachment 104707

Yes I did, and will go back and study again. I have then saved in special folder of their own. The problem is OLE, and some of my users have MACs with Office for MAC, and there is no ACCESS for the MAC, but one user has a work around for that. You are right that I don't know how to code it, but I am trying as hard as I can.

 

[Pat Hartman]

If they don't have an emulator that can run Access and connect to your network, or can connect to a Citrix or RDP server to run the Access app in a browser, you need to find a different solution. Access won't work for you.

PS, you might have thought to mention the "Mac" issue at the beginning.

 

[jpl458]

There is an emulator involved, which we will be testing shortly.

 

[Pat Hartman]

The emulator should be fine.

 

[jpl458]

If they don't have an emulator that can run Access and connect to your network, or can connect to a Citrix or RDP server to run the Access app in a browser, you need to find a different solution. Access won't work for you.

PS, you might have thought to mention the "Mac" issue at the beginning.

It was a surprise to me too. I was well into it when I found out. I have find if OLE will work with the emulator, native MAC does not support OLE.

 

[jpl458]

Pat, trying to understand your Automate Word example. Here is the contents of the folder I have it in:

Here is Pats 2:

I have Office 365. I was trying Pats2, but when I opened tool and references, I found no mention of Word to reference.
Sorry for asking, but I need some help getting it going. I've been reading the code.

Thanks again.

 

[jpl458]

Pat, got the sample working and I understand the flow. Really cool. Will probably have some questions, but let me play with it first.

Thanks again.

 

[Pat Hartman]

You're welcome.

 

[jpl458]

You're welcome.

Got a question. I am working back from the Word doc to the ACCESS data, so first I want to make sure I understand creating bookmarks. I have a test document that has the following:

I followed the instructions for creating bookmarks and when I open the bookmarks tab I get this

But, when I use the tool It can't find the bookmarks in my docx.

I clear tables first, then capture bookmarks.
The Document is docx

When I run it against your docx, it worked fine. Can't see what I am doing wrong. I notice that you have< >inside the brackets. Is that important?
Doing this a step at a time. First I want to be able to have the tool find bookmarks in a docx that I have. In the same Preview Letter brings up my document with this note "5941-The requested member of the collection does not exist."
Also you get the data from tables, can the data come from queries? I assume that is true.

Thanks

 

[Pat Hartman]

I haven't worked with this in quite a while. If you could send me the document, Either do it here if it is not secret or pm me. I'll try it and step through the code to see why the tool has a problem. If you create a document and use the names in the table the sample is set up for, do you get the same error? can you run the app to fill your document? In theory,the tool should be able to read any document. That was it's point

Or, you could try to step through the code to see what line of code is causing the problem.

 

[jpl458]

I haven't worked with this in quite a while. If you could send me the document, Either do it here if it is not secret or pm me. I'll try it and step through the code to see why the tool has a problem. If you create a document and use the names in the table the sample is set up for, do you get the same error? can you run the app to fill your document? In theory,the tool should be able to read any document. That was it's point

Or, you could try to step through the code to see what line of code is causing the problem.

If you create a document and use the names in the table the sample is set up for, do you get the same error?

I think that line may show me the way. I just put my own names into the Word document bookmarks.


Once again, thanks, Pat.

 

[Pat Hartman]

OK I feel better now At some point, I may build a version that is closer to what I use for my client apps because this sample is very inflexible even though it does show the technique. Rather than hard-code field names and bookmark names, it uses tables to map them as well as define what queries/tables to use. I have an insurance app that manages thousands of word documents and the users don't need me to do anything for them to add a new document or even for them to define a new line of business with new data fields. Instead of waiting 3-6 months for IT to come up with modifications, they now use my app and can enter the data to set up a new LOB in a few hours and then as long as it takes to create the word documents that will be used.

 

[jpl458]

OK I feel better now At some point, I may build a version that is closer to what I use for my client apps because this sample is very inflexible even though it does show the technique. Rather than hard-code field names and bookmark names, it uses tables to map them as well as define what queries/tables to use. I have an insurance app that manages thousands of word documents and the users don't need me to do anything for them to add a new document or even for them to define a new line of business with new data fields. Instead of waiting 3-6 months for IT to come up with modifications, they now use my app and can enter the data to set up a new LOB in a few hours and then as long as it takes to create the word documents that will be used.

I had to spend the AM fixing little, not bugs, productizing issues, while watching football(I'm retired, I'm allowed to do that). I am preparing a document to PM to you that has the guts of what's needed per bookmarks. This is my first time trying this, and I have divorce myself from my preconceived notions about how things Should work.

I have data in tables and I want to have some of that data end up in the proper place in any one of a few different letters. Should be very simple, but I have to mentally sort out the steps in that process. (Learned a log time ago to think before you code ,and in this case before you ask questions). Your explanation above made me feel better as well. I will send you a note when I have everything together.

I can't thank you enough.

 

[Pat Hartman]

If you have multiple documents and you don't want to make hard-coding the document filling code your life's work, then you absolutely need to tablize the data to handle the filling.

I have two different methods I use in applications. One is very complicated and gives the user the ability to actually define new data fields. This is still within limits though. the application is used to fill the documents used to create an insurance policy. The company was a startup and was rapidly expanding the types of policies they would write. Policies had common data and they had unique data. It was the unique data fields that the users could define. This data was stored in an ultra-normalized table that stored each data field as a separate row. So the table had the following fields:
RecID (autonumber, PK)
PolicyID (FK to tblPolicies)
FieldID (FK to tblFields)
TextValue

The tblFields contains the definition for a field as well as its name. To provide flexibility, ALL data is entered as text. The tblFields species the data type and even provides a query name if the data should displayed as a combo.

Then there's other tables to connect fields to policyTypes and documents.

The simpler method provides a fixed set of data so if you need to change the data that is used to fill the forms, you need to modify the program since the tables are standard rather than Name/value lists like the complex example.

I've posted pictures of the complex solution multiple times. But here is a picture of the form where you define a new Document and then map fields to bookmarks. For the sake of sanity, I use the fieldname as the bookmark name but sometimes, you need to use the same field in a document multiple times. Notice that AmnestyEndDate is used three times and so has three bookmarks. Bookmark names in a document MUST be unique so I use a numeric suffix.

Since many documents have a similar set of fields, there is a copy option. For this process, you create the new doc header and then use the combo to pick the "from" document. You can obviously do this the other way if you prefer. But I think this ends up being more flexible if you want to merge documents. You can copy the bookmarks from multiple documents. Due to the indexes, only one instance of the book mark will be added. Duplicates will be discarded.

Since this is a small project and all the data can be selected by a single query, there isn't an option to pick a query to associate with the document but you could do that. Some of these documents use those little tables you see in the example. Since there were only three of them and there was little chance of having to make more due to the type of data, I went the hardcode route. If the FieldName is one of the "special" ones, the app opens a query to select the dependent data to build the table and then insert it into the document. As you can see by the example, this process is separate from the main line of filling the form fields.

This application, since it was intended to create letters to employees offered the option of a language preference. All the user needed to do was to create whatever versions of the document he wanted and use "_English", "_Spanish", whatever as the suffix. You could of course just create duplicate documents with hard coded language in the names but this seemed cleaner.

 

[jpl458]

If you have multiple documents and you don't want to make hard-coding the document filling code your life's work, then you absolutely need to tablize the data to handle the filling.

I have two different methods I use in applications. One is very complicated and gives the user the ability to actually define new data fields. This is still within limits though. the application is used to fill the documents used to create an insurance policy. The company was a startup and was rapidly expanding the types of policies they would write. Policies had common data and they had unique data. It was the unique data fields that the users could define. This data was stored in an ultra-normalized table that stored each data field as a separate row. So the table had the following fields:
RecID (autonumber, PK)
PolicyID (FK to tblPolicies)
FieldID (FK to tblFields)
TextValue

The tblFields contains the definition for a field as well as its name. To provide flexibility, ALL data is entered as text. The tblFields species the data type and even provides a query name if the data should displayed as a combo.

Then there's other tables to connect fields to policyTypes and documents.

The simpler method provides a fixed set of data so if you need to change the data that is used to fill the forms, you need to modify the program since the tables are standard rather than Name/value lists like the complex example.

I've posted pictures of the complex solution multiple times. But here is a picture of the form where you define a new Document and then map fields to bookmarks. For the sake of sanity, I use the fieldname as the bookmark name but sometimes, you need to use the same field in a document multiple times. Notice that AmnestyEndDate is used three times and so has three bookmarks. Bookmark names in a document MUST be unique so I use a numeric suffix.

Since many documents have a similar set of fields, there is a copy option. For this process, you create the new doc header and then use the combo to pick the "from" document. You can obviously do this the other way if you prefer. But I think this ends up being more flexible if you want to merge documents. You can copy the bookmarks from multiple documents. Due to the indexes, only one instance of the book mark will be added. Duplicates will be discarded.

Since this is a small project and all the data can be selected by a single query, there isn't an option to pick a query to associate with the document but you could do that. Some of these documents use those little tables you see in the example. Since there were only three of them and there was little chance of having to make more due to the type of data, I went the hardcode route. If the FieldName is one of the "special" ones, the app opens a query to select the dependent data to build the table and then insert it into the document. As you can see by the example, this process is separate from the main line of filling the form fields.
View attachment 104739

This application, since it was intended to create letters to employees offered the option of a language preference. All the user needed to do was to create whatever versions of the document he wanted and use "_English", "_Spanish", whatever as the suffix. You could of course just create duplicate documents with hard coded language in the names but this seemed cleaner.

Sent you a PM, but it seems that I can't attach the Word doc in the PM. If there is a way to do that let me know. I created a table that looks like your, except the right and left side are switched.

 

[jpl458]

Just reread your note (several times, slowly), and I believe you are saying just put the data into a table with one item per line? My instincts tell me that greatly simplifies things, if I am correct.

 

[jpl458]

Just reread your note (several times, slowly), and I believe you are saying just put the data into a table with one item per line? My instincts tell me that greatly simplifies things, if I am correct.

since all the dat is different each time it's used, would an array work as well, or would you just delete the table after it is used. Also, if you use tables, how do you create a table with one entry per line with Make Table query? Trying that now.