Selecting Data Sources For Variables By User Form - Word2007

[TonyK]

After studying the various help texts and sample scripts etc. I wonder if the following is possible

I have created a Word doc which contains many "variables" such as addresses, various dates and times and a travel itinerary for several days with changing text parts.

For data storage I could use an Excel workbook "mydata.xls", that contains several tables, such as "Addresses", "Dates" etc.

Each row would have several Data Fields.

I wanted to use the first column of each table as an unique Key = identifying title for that variable so user could select the right one from a selection field in the macro while in Word.

My idea the user is presented several user forms, one after the other, which show the user a selection field in which each row contains the identifying title for that specific data. After user made his choice, the macro returns the information from the respective Excel table.

With addresses the macro returns and inserts let's say 4 lines of address data (company_name, street, zip_city, email_address)

With other data for example just one line, that is built from 3 data fields of one selected row (title_employee,fname,lname)

Is that possible and could you make a code proposal of how to do it?

Thanks so much

Tony

[adroege]

Yes, It is possible


This does require *some* programming skill/experience as this is not just a simple "move mouse/send keystroke" type of macro.

This forum is a wealth of code samples and ideas. Try dividing your problem up into smaller parts, and then trying to make the small parts work first - before combining it all into one big project.

For example, Search the forum on how to access data in Excel spreadsheets.... you will find many examples to cut/paste

Search the help/forum on how to code custom dialogs.

Try putting something together yourself, and then if you have problems, upload your sample code here, and we will be glad to assist and troubleshoot your code.

Enjoy!

[TonyK]

Thank you for your support!

In searching this forum I could not find a solution for the following task:

I want to get the value of an Excel cell and post it in a Word doc.

Excel table sample:

ID ........... Value
cgn01 ...... Dec 15th 2009
cgn02 ...... Jan 10th 2010
cgn03 ...... Jan 19th 2010
...
cgn99 ...... Mar 05th, 2010


Let's assume, that I created a user form in Word which asks the user for input of one specific ID, which will be used to get the correct Value from the Excel table and inserts that value in active Word doc at cursor position.

Please give me a sample for such a macro - that would help me a lot.

Thank you
Tony

[adroege]

Based upon your definition of the Excel data file from your previous
post, here is a little sample of code which should return the corresponding date when given the ID.

I created an XLS data file according to your sample, and tested the following code. When I entered "cgn02" as the ID it correctly returned "Jan 10th 2010" as the value.

Now it's up to you to extend/enhance this code to send the results back and forth between Word. Most likely you would use various mousemove and send commands to simulate a user typing into the Word fields.

There also exists the possibility of using VBScript to interact directly with the running Word process - Save this for something advanced to try once you get the simple stuff working!

Enjoy.

[code]

Input>FileName,Enter path to XLS data file
//Let>FileName=c:\Data1.xls

Input>MyID,Enter ID value to Look For

Let>connStr1=Driver={Microsoft Excel Driver (*.xls)};DriverId=790;Dbq=%FileName%;DefaultDir=c:\;

DBConnect>connStr1,dbH1
//Let>SQL1=select * from [Sheet1$] where [ID] = 'cgn02'
Let>SQL1=select * from [Sheet1$] where [ID] = '%MyID%'
DBQuery>dbH1,SQL1,rsSheet1,nR,nF
DBClose>dbH1
//MessageModal>nR
//MessageModal>nF
MessageModal>rsSheet1_1_2
[/code]

[TonyK]

That script works really fine.

What I tried without success was to write the variable into a MS Word 2007 document

a) at cursor position
b) bookmark

I succeeded to open Word2007 but could not write the variable in the document with using the commands

Send> rsSheet1_1_2
Press> Enter

And I get a "Macro" Warning by Word2007 and the macro is deactivated - I have to activate it by command.

Is there a function to have the Macro do it by itself.

Thank you so much!

Best, Tony

[adroege]

You have to make sure that the Word window has the focus before sending keystrokes using Send>

SetFocus>Put Your Window Title Here
Wait>1
Send>rsSheet1_1_2
Press Enter

Notice that the Press command does NOT have a greaterthan symbol after it.

Read the online help to see how to use the * (wildcard) character
in the Window Title

[TonyK]

Thank You!

That worked really fine!

May I ask you some more questions? I am learning a lot from your examples!

1) What is the code if I want to use a bookmark in Word to post the variable too?

2) Does that also work for a matrix such as a range of cells in Excel? What do I need to do if I want to get a range of cells instead of the date variable only?

Thanks a lot for your help !

Best, Tony

[adroege]

What is a bookmark?
A bookmark is an item or location in a document that you identify and name for future reference. You can use bookmarks to quickly jump to a specific location, create cross-references, mark page ranges for index entries, and so on.

I don't understand your use of a bookmark according to the quote above which I found in my Word2000 help.

1) What is the code if I want to use a bookmark in Word to post the variable too?

What do I need to do if I want to get a range of cells instead of the date variable only?

The DBQuery in the sample code I provided is already returning the entire row of data. Reference the array which is returned to gain access to the other columns. i.e. Array_1_2 is row 1 column 2

See the on-line help for DBQuery in Macro Scheduler help file.

[TonyK]

Thank you for your explanation.

I have a 6 page Word document and I have create several bookmarks in order to mark the exact positions in the document for the various variables to be inserted to.

variable01 should be inserted at position of bookmark01
variable02 should be inserted at position of bookmark02
etc.

If that is possible, what would be the macro code for it.

If that task cannot be fulfilled by using a macro, then I could use placeholders such as %bookmark01% written into the document at the correct position and have the macro replace these with the value of the correct variable. What would be the macro code for just doing that?

Best, Tony

[adroege]

Most automation in Macro Scheduler is done by just simulating what a real live human would do when moving the mouse and sending keys from the keyboard.

So you should do *exactly* what you want your script to do, and make note of what keys you press, or what pixel coordinates you move the mouse to. Then code these commands into a script, and they should simulate what actions you just did. Use keyboard shortcuts where you can, they are more robust than mouse clicks. For example, to open the File menu usually in most applications you can press Alt-F on the keyboard.
In Macro Code this would be:

Press Alt
Send>f
Release Alt


You can try the "record" feature of Macro Scheduler. This will write the script for you as you do the task you want to automate manually. I usually go back and cleanup/tweak the resulting code, but at least it gives you a starting point.