Excel invoked from Word 2010

[sts023]

Hi….
I’m having some fun with an application which initiates a Word 2010 Mailmerge (yes, I know this is MrExcel, but please bear with me).
The app creates a Document from a Template.
What I need to do is, post Mailmerge, read a line from the document, and then EITHER

1. Include the data I’ve just read in SQL created by VBA in the Word document, then run the SQL and reformat the results into lines in the document, or
2. Allow the User to close the Word document, and in the Close routines invoke Word VBA to create a new Excel Spreadsheet, with just the data recovered from the Word document in Cell A1 of Sheet1.
   Then I should be able to put VBA in one of the Excel standard routines (Open / Close) to create SQL to get data from a Sybase database using the data in cell A1, in order to populate the spreadsheet with further data.

Now I’ve spent quite a while looking for “accessing Sybase data in SQL inWord”. There seems to be little help out there: everything seems to assume you’re using SQL in Excel (which explains the tortuous route in option b) above.

(As an aside, does anyone know how to allow the code to be paused, then stepped through when the original app invokes Word for the Mailmerge?

Call MsgBox

doesn’t allow access to the code, neither does

Debug.Assert False

)

In Word, I have the following code, which seems to loop. In CreateNewExcelWB. Stepping through from the Close routine, I get to the line

  Set xlWB = xlApp.Workbooks.Add

And control goes back to the start of the Sub.
Can anyone help? Please!

Code follows:
:-

‘ This is in a Module called Globals

Option Explicit
Public strPath                      As String
Public strSiteId                    As String
Public strXLNameIn                  As String
Public strXLNameOut                 As String

‘This is in the standard Word Close module.

Private Sub Document_Close()
Dim intLines                        As Integer
Dim parLine                         As Paragraph
'*
'** We need to pick up the contents of the FINAL LINE
'** in the document, as this should be the Site Id
'** from Cash4W's Mailmerge.
'** Note: Lines are Paragraphs in Word.
'*
  intLines = ActiveDocument.Paragraphs.Count    'Total line count.
  Set parLine = ActiveDocument.Paragraphs(intLines)
  strSiteId = parLine.Range.Text
  Debug.Assert False
 
  Set parLine = Nothing                         'Tidy up.
'*
'** If the Mailmerge didn't happen, we will still have the
'** original Mailmerge field "<<site_siteid aria-dolphinuid="85c:b:1c88">>".
'** If so, just exit.
'*
  If strSiteId = "" Or _
     InStr(1, strSiteId, "Site_SiteID", vbTextCompare) Then Exit Sub
'*
'** We are post Mailmerge.
'** set up some basic values, then call a routine to
'** Create the Excel Spreadsheet in its basic form,
'** with just the Site Id in Cell A1 of
'** the Site Name Worksheet.
'*
 
  strPath = "C:\Dropbox\Phoenix\Steve\"
  strXLNameOut = strPath & "TestXFromW.xlsx"
  Call CreateNewExcelWB
End Sub

‘This is in a Module called ControlExcel

Option Explicit
 
Sub CreateNewExcelWB()
'*
'** Needs a reference to the Microsoft Excel Object Library
'*
Dim xlApp                   As Excel.Application
Dim xlWB                    As Excel.Workbook
Dim i                       As Integer
'*
'** Create an instance of Excel.
'*
  Set xlApp = CreateObject("Excel.Application")
  xlApp.Visible = False
'*
'** Create a new Workbook.
'*
  Set xlWB = xlApp.Workbooks.Add
  With xlWB.Worksheets(1)
    .Range("$A$1").Value = strSiteId
    If Dir(strXLNameOut) <> "" Then
      Kill strXLNameOut
    End If
    .SaveAs (strXLNameOut)
  End With
  xlWB.Close 
  xlApp.Quit  
  Set xlWB = Nothing
  Set xlApp = Nothing
End Sub

</site_siteid>