Excel invoked from Word 2010


Board Regular
Sep 1, 2008
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
  Set xlWB = Nothing
  Set xlApp = Nothing
End Sub

Excel Facts

Excel motto
Not everything I do at work revolves around Excel. Only the fun parts.

Forum statistics

Latest member
Vasant bangalore

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back