Run Excel Queries from a Word Document

[SkipVought]

I want to be able to run 2 dozen+ Excel queries from a Word document by clicking on a hyperlink or a button, the resultset to be displayed in a spreadsheet.
I have designed a pilot that functions as follows:
1. Word opens - in the Document_Open event, I start an Excel Workbook that has a GetData procedure that takes an argument that is the SQL and write the resultset to a worksheet.
2. From within Word, I select a hyperlink that opens a specific workbook with a Workbook_Open event code that calls GetData(ThisSQL). As a result, the query is run and the resultset displayed in the active workbook.
The problem with my design is that I have 2 dozen different queries and consequently as many separate workbooks, each one with unique SQL stored in a single cell. The VBA code is identical.
What other approaches would you suggest?
Thanx y'all
Skip,
metzgsk@voughtaircraft.com

 

[ide]

Dear SkipVought,
Why did you choose Word, to manage documents, if you display the result in an Excel table?

if i understand it clearly and right:
In this case if i were you:

-create a book (B1) with f.e. 3 sheet (S1, S2, S3)
-The S1 contains (In the first column) your links (format the text like a link, underlined ...)
S2 column "A" contains sqls, column "B" the default output paths
and fe. the S3 cell "A1" an option: 0 run autoExec macro to saves all the recordsets of qrys to the default path, 1 not to run qrys, just open workbook

in the code of S1 insert a code what manage double clicks in the first column. If the user double clicks on the cell the routine handles like:
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
Dim rngTypeRange As Range
Dim rngTemp As Range

Set rngTypeRange = Range("A1:A80&quot
Set rngTemp = Intersect(rngTypeRange, Target) 'if user clicked in the range above...
If rngTemp Is Nothing Then Exit Sub
Cancel = True 'cancel doubleclick
call sbOutPutRecordset(fnHandleDBClick(Sheets("S2&quot.Range(Target.Address).Value))
End Sub

the last line calls a function inserted into a separated module (fnHandleDBClick) what returns the recordset, and puts it into a new WorkBook (sbOutPutRecordset) and optionally saves it

function fnHandleDBClick(strMySql as string) as recordset
'this function runs the strMySql query, and returns with the recordset resulted
' strMySql: SQL string
'....
end sub
sub sbOutPutRecordset(rstOutTo as recordset, Optional strPath as string)
'this procedure outputs the parameter recordset into an new excel workbook,
'if the strPath path specified then saves to then saves it
end sub

- create an AutoExec macro (Autpen() in a module or a "Private Sub Workbook_Open()" in the code of "ThisWorkBook&quot what check the S3 "A1" value and run or not the outputing:
Private Sub Workbook_Open()
if sheets("S3&quot.range("A1&quot= 0 then
for each c in sheets("S3&quot.range("A1:A80&quot
call sbOutPutRecordset(fnHandleDBClick(Sheets("S2&quot.Range(c.Address).Value, Sheets("S2&quot.Range(c.offset(0,1).Value))
next c
end if
end sub

If you need more help write here or ide@altavista.net
Sorry if it was a wrong suggestion.

 

[SkipVought]

Maybe I should have explained the purpose as I perceive it.

My user is creating a dynamic Help document in Word, for database administration, regarding one of our mainframe systems. As the user reads the document, they can get current statistics regarding the structure of this very complex system, by executing queries at various places in the document. So I do not have the luxury of selecting the optimum. I must design a system within their requirements.

Really appreciate your contribution Skip,
metzgsk@voughtaircraft.com