I would like a Macro to run in Word that would then connect to an ODBC source, then an SQL script run to capture values from the database. Is this a pipe dream, or is it possible?
Thanks
Using Macro to connect to ODBC and run SQL script
-
- 4StarLounger
- Posts: 504
- Joined: 23 Jul 2010, 18:36
- Location: Westlake, OHIO
Using Macro to connect to ODBC and run SQL script
Who will you Inspire today?
-
- 4StarLounger
- Posts: 504
- Joined: 23 Jul 2010, 18:36
- Location: Westlake, OHIO
Re: Using Macro to connect to ODBC and run SQL script
Sorry, I have more info. I want those values then to fill fields on the Word document.
Who will you Inspire today?
-
- Administrator
- Posts: 78794
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Using Macro to connect to ODBC and run SQL script
That should be doable:
- Create an ADODB connection to the database.
- Open a recordset.
- Get data from the fields of the recordset and use them to populate fields in the Word document.
- Create an ADODB connection to the database.
- Open a recordset.
- Get data from the fields of the recordset and use them to populate fields in the Word document.
Best wishes,
Hans
Hans
-
- 4StarLounger
- Posts: 504
- Joined: 23 Jul 2010, 18:36
- Location: Westlake, OHIO
Re: Using Macro to connect to ODBC and run SQL script
The script that I want to run would include variables based upon fields captured on an html form. It would be something like:
select alphanum from jkl.running where primaryvalue = [variableprovided]
Then paste the alphanum into a Mail Merge field or some other way of telling the system where it belongs on the letter.
I cannot use a recordset as the values will be changing daily.
select alphanum from jkl.running where primaryvalue = [variableprovided]
Then paste the alphanum into a Mail Merge field or some other way of telling the system where it belongs on the letter.
I cannot use a recordset as the values will be changing daily.
Who will you Inspire today?
-
- Administrator
- Posts: 78794
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Using Macro to connect to ODBC and run SQL script
The idea would be to generate a SQL string dynamically in your VBA code, i.e. in runtime.
Then open a recordset on that dynamically generated SQL string.
Then open a recordset on that dynamically generated SQL string.
Best wishes,
Hans
Hans
-
- 4StarLounger
- Posts: 596
- Joined: 14 Nov 2012, 16:06
Re: Using Macro to connect to ODBC and run SQL script
You can link a Word merge master document to any database.
No Macro needed.
No Macro needed.
-
- 5StarLounger
- Posts: 640
- Joined: 10 Jan 2016, 15:56
- Location: Madison, Wisconsin
Re: Using Macro to connect to ODBC and run SQL script
I think Timeford wants this to run without user intervention at that point. Anything needed would be in the macro or the html form.
![Cheers :cheers:](./images/smilies/cheers.gif)
Templates in Microsoft Word
Modifying the QAT
The Importance of Styles in Microsoft Word
-
- Lounger
- Posts: 43
- Joined: 07 Jun 2023, 15:34
Re: Using Macro to connect to ODBC and run SQL script
I use this for invoices, with data from SQL Server (see the macro: "Consulta_Factura"):
invoice_template.docm
invoice_template.docm
-
- 4StarLounger
- Posts: 596
- Joined: 14 Nov 2012, 16:06
Re: Using Macro to connect to ODBC and run SQL script
Nothing simpler without user intervention than a macro:Charles Kenyon wrote: ↑01 Oct 2023, 20:50I think Timeford wants this to run without user intervention at that point. Anything needed would be in the macro or the html form.
Code: Select all
With documents.open(G:\OF\master.docx")
.mailmerge.execute
.close -1
end with
activedocument.printout
-
- 4StarLounger
- Posts: 504
- Joined: 23 Jul 2010, 18:36
- Location: Westlake, OHIO
Re: Using Macro to connect to ODBC and run SQL script
Thank you all. This is all good information.
Who will you Inspire today?