Microsoft Office Forums Populating a Word document with VBA\SQL

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 07-14-2015, 12:36 AM
shabbaranks shabbaranks is offline Populating a Word document with VBA\SQL Windows 7 64bit Populating a Word document with VBA\SQL Office 2007
Advanced Beginner
Populating a Word document with VBA\SQL
 
Join Date: Mar 2011
Posts: 87
shabbaranks is on a distinguished road
Default Populating a Word document with VBA\SQL

Hi,



Im probably going to get my words tied up but anyway here goes...

I have a document which holds a job number based on that job number I would to do an SQL lookup for the customer details and then populate another field\box with the returned information.

Im familiar with linking fields within the custom properties of a document as this is what populates the Job number. SO I was wondering how I use that to then get other information based on that job?

Hope this makes sense.. Thank you
Reply With Quote
  #2  
Old 07-14-2015, 12:59 AM
macropod's Avatar
macropod macropod is offline Populating a Word document with VBA\SQL Windows 7 64bit Populating a Word document with VBA\SQL Office 2010 32bit
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 19,525
macropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to behold
Default

Where are the data stored? If they're in a database, do you have a working SQL statement? Have you considered using Word's mailmerge process to generate the document?
__________________
Cheers,
Paul Edstein
[MS MVP - Word]
Reply With Quote
  #3  
Old 07-14-2015, 02:44 AM
shabbaranks shabbaranks is offline Populating a Word document with VBA\SQL Windows 7 64bit Populating a Word document with VBA\SQL Office 2007
Advanced Beginner
Populating a Word document with VBA\SQL
 
Join Date: Mar 2011
Posts: 87
shabbaranks is on a distinguished road
Default

Hi,

Thanks for the reply - the data is stored within an SQL DB to which I have written an SQL query to return the values. Does the mail merge feature use a "lookup" for example like a dlookup based on a particular field?

Thanks
Reply With Quote
  #4  
Old 07-14-2015, 03:04 AM
shabbaranks shabbaranks is offline Populating a Word document with VBA\SQL Windows 7 64bit Populating a Word document with VBA\SQL Office 2007
Advanced Beginner
Populating a Word document with VBA\SQL
 
Join Date: Mar 2011
Posts: 87
shabbaranks is on a distinguished road
Default

Ive got my mail merge setup - I think but what Im struggling to do is add a field code and then get the mail merge to pull the information based on that?
Reply With Quote
  #5  
Old 07-14-2015, 03:18 AM
macropod's Avatar
macropod macropod is offline Populating a Word document with VBA\SQL Windows 7 64bit Populating a Word document with VBA\SQL Office 2010 32bit
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 19,525
macropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to behold
Default

Assuming you've done the mail merge main document setup with a connection to the data source, you should be able to insert whatever mergefields you need via the 'Insert Merge Field' dropdown.
__________________
Cheers,
Paul Edstein
[MS MVP - Word]
Reply With Quote
  #6  
Old 07-14-2015, 04:40 AM
shabbaranks shabbaranks is offline Populating a Word document with VBA\SQL Windows 7 64bit Populating a Word document with VBA\SQL Office 2007
Advanced Beginner
Populating a Word document with VBA\SQL
 
Join Date: Mar 2011
Posts: 87
shabbaranks is on a distinguished road
Default

Excuse my ignorance but how do you insert a merge field drop down? I can get the result by using the find function but the drop down sounds better?

Its not within the mail merge wizard is it?
Reply With Quote
  #7  
Old 07-14-2015, 04:44 AM
shabbaranks shabbaranks is offline Populating a Word document with VBA\SQL Windows 7 64bit Populating a Word document with VBA\SQL Office 2007
Advanced Beginner
Populating a Word document with VBA\SQL
 
Join Date: Mar 2011
Posts: 87
shabbaranks is on a distinguished road
Default

Also just spotted another post by you (you know your stuff )

How do you trigger the document to complete the fields after an ask trigger?

I really appreciate your help
Reply With Quote
  #8  
Old 07-14-2015, 05:04 AM
macropod's Avatar
macropod macropod is offline Populating a Word document with VBA\SQL Windows 7 64bit Populating a Word document with VBA\SQL Office 2010 32bit
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 19,525
macropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to behold
Default

Quote:
Originally Posted by shabbaranks View Post
Excuse my ignorance but how do you insert a merge field drop down? ...
Its not within the mail merge wizard is it?
The 'Insert Merge Field' dropdown is on the Ribbon's 'Mailings' tab.

Note: This is not a dropdown mergefield (which doesn't exist)...
__________________
Cheers,
Paul Edstein
[MS MVP - Word]
Reply With Quote
  #9  
Old 07-14-2015, 05:08 AM
macropod's Avatar
macropod macropod is offline Populating a Word document with VBA\SQL Windows 7 64bit Populating a Word document with VBA\SQL Office 2010 32bit
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 19,525
macropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to behold
Default

Quote:
Originally Posted by shabbaranks View Post
How do you trigger the document to complete the fields after an ask trigger?
You wouldn't use an ASK field for this. Instead, once you've set up your mailmerge main document with all the merge fields you'll need, insert a SKIPIF field coded along the lines of:
{SKIPIF{MERGEFIELD Job_Number}<> {FILLIN "What is the customer job number to use?" \o}}
where 'Job_Number' is the name of the mergefield holding the customer job number.

Note: The field brace pairs (i.e. '{ }') for the above example are all created in the document itself, via Ctrl-F9 (Cmd-F9 on a Mac); you can't simply type them or copy & paste them from this message. Nor is it practicable to add them via any of the standard Word dialogues.
__________________
Cheers,
Paul Edstein
[MS MVP - Word]
Reply With Quote
  #10  
Old 07-14-2015, 05:10 AM
shabbaranks shabbaranks is offline Populating a Word document with VBA\SQL Windows 7 64bit Populating a Word document with VBA\SQL Office 2007
Advanced Beginner
Populating a Word document with VBA\SQL
 
Join Date: Mar 2011
Posts: 87
shabbaranks is on a distinguished road
Default

Ah yeah sorry (lets start again).

So I have inserted all the fields that are required on this document. Obviously the database will have hundreds of records\jobs. What I am trying to achieve is populate the document I am working on (based on your advice from a mail merge) which works fine. Apart from the only way I can get the document populated with the correct job is to go to mailings and then find recipient and then enter the job number.

Is there a better way to do it?

Thanks
Reply With Quote
  #11  
Old 07-14-2015, 05:15 AM
shabbaranks shabbaranks is offline Populating a Word document with VBA\SQL Windows 7 64bit Populating a Word document with VBA\SQL Office 2007
Advanced Beginner
Populating a Word document with VBA\SQL
 
Join Date: Mar 2011
Posts: 87
shabbaranks is on a distinguished road
Default

I think we are crossing replies

Anyway I have done as per your example - how do I initiate the mail merge? If I press start mail merge and then use the wizard it runs through everything again.
Reply With Quote
  #12  
Old 07-14-2015, 05:36 AM
macropod's Avatar
macropod macropod is offline Populating a Word document with VBA\SQL Windows 7 64bit Populating a Word document with VBA\SQL Office 2010 32bit
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 19,525
macropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to behold
Default

Quote:
Originally Posted by shabbaranks View Post
I have done as per your example - how do I initiate the mail merge? If I press start mail merge and then use the wizard it runs through everything again.
If you've implemented the SKIPIF field as indicated, you will be prompted for the customer job number to use. When you input that, only the corresponding record will be output.
__________________
Cheers,
Paul Edstein
[MS MVP - Word]
Reply With Quote
  #13  
Old 07-14-2015, 06:09 AM
shabbaranks shabbaranks is offline Populating a Word document with VBA\SQL Windows 7 64bit Populating a Word document with VBA\SQL Office 2007
Advanced Beginner
Populating a Word document with VBA\SQL
 
Join Date: Mar 2011
Posts: 87
shabbaranks is on a distinguished road
Default

Call me stupid (it wouldn't be the first time) but Ive done it correctly as far as I can tell



if I close the document it prompts for the SQL connection - other than that I cant see how to make the boxes populate?
Reply With Quote
  #14  
Old 07-14-2015, 06:14 AM
macropod's Avatar
macropod macropod is offline Populating a Word document with VBA\SQL Windows 7 64bit Populating a Word document with VBA\SQL Office 2010 32bit
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 19,525
macropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to behold
Default

It's hard to tell from your images (an actual document would have been better), but it looks to me like your braces for '{MERGEFIELD Job}' and '{FILLIN "Please enter Job number" \o}' are just ordinary text braces, not field braces as I said you'd need.
__________________
Cheers,
Paul Edstein
[MS MVP - Word]
Reply With Quote
  #15  
Old 07-14-2015, 06:43 AM
shabbaranks shabbaranks is offline Populating a Word document with VBA\SQL Windows 7 64bit Populating a Word document with VBA\SQL Office 2007
Advanced Beginner
Populating a Word document with VBA\SQL
 
Join Date: Mar 2011
Posts: 87
shabbaranks is on a distinguished road
Default

Im slowly getting there (and slowly learning more about Word so thanks...!!)

I have used the ctrl f9 function to create the { and written the code in between as per the image below. Whats happening now is if I open the document I am prompted to insert the job number (it asks me a few times) and then when it stops the job number is empty and the other fields relate to a completely different job number... humph

Reply With Quote
Reply

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
Populating Data in New TABs? Sueade Excel 2 06-02-2015 09:12 PM
Mail Merge Field Not Populating JennEx Mail Merge 3 05-10-2015 09:30 PM
Populating a table cell wit document properties milena Word VBA 2 04-15-2015 07:28 AM
Reapting/Populating the same text in Word 2007 akwjaw14 Word 0 06-03-2014 03:40 PM
Populating a Word document with VBA\SQL Need help populating dropdown box antztaylor Word 3 11-06-2012 05:46 PM


All times are GMT -7. The time now is 11:34 PM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2019, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2019 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft