open second wordbook from Word

G

gw.boswell

I have a MS Word macro that opens an Excel file then copies certain
information from the Word document and places in in specified locations
in the Excel spreadsheet. No problem there. What I would now like to
do is extend the macro to open an additional Excel file and copy
certain data from the second file into the first file.

I would list the macro here but it is long and I am having no problems
with it. The key is to be able to open the second Excel file while
keeping the first Excel file open.

Any suggestions?

Garry
 
E

Ed

Can't you open two Excel files at once? You should be able to set an object
to your second Excel file and open it with no problem. If you _are_ running
into errors, the at least post the portion of the code that's throwing the
error and tell us what the message is.

Ed
 
G

gw.boswell

Ed,
I can manually open a second Excel file then copy data into the first
Excel file. What I can't seem to do is open the second Excel file from
the Word macro. Here is the coding I use to open the first Excel file:

Dim xlApp as object, BSParameters as object, WorkingSheet as object

ExcelWasNotRunning = False
On Error Resume Next
Set xlApp = GetObject(, "Excel.Application")
If Err.Number <> 0 Then
Set xlApp = CreateObject("Excel.Application")
ExcelWasNotRunning = True
Err.Clear
End If
On Error GoTo 0

DetectExcel ' call to sub DetectExcel
*******************************************************************
Sub DetectExcel()
' Procedure dectects a running Excel and registers it.
Const WM_USER = 1024
Dim hWnd As Long
' If Excel is running this API call returns its handle.
hWnd = FindWindow("XLMAIN", 0)
If hWnd = 0 Then ' 0 means Excel not running.
Exit Sub
Else
SendMessage hWnd, WM_USER + 18, 0, 0
End If
End Sub
******************************************************************
SummaryFile = "c:\Overview.xls"
xlApp.Application.Visible = True
xlApp.Parent.Windows(1).Visible = True

Set BSParameters = GetObject(SummaryFile)
Set WorkingSheet = BSParameters.Worksheets("SUMMARY")


When I try to open a second Excel file with the same type of code, I
get the macro warning message (which I get when I open the second file
manually) but it does not seem to open - it doesn't appear in the Task
Bar.

Garry
 
E

Ed

I don't understand what you're doing with the DetectExcel procedure or the
BSParameters object, so you may be out of my league and my comments
irrelevant. But have you tried either
xlApp.Dialogs(xlDialogOpen).Show
or
xlApp.Workbooks.Open(FilePathAndName)
to open the second workbook?

Ed
 
G

gw.boswell

Ed,

I barely understand what that code does, I copied it from a posting
that explained what it did :)
I'll give your suggestion a shot. Thanks for the help.

Garry
 
G

gw.boswell

Ed,

I barely understand what that code does, I copied it from a posting
that explained what it did :)
I'll give your suggestion a shot. Thanks for the help.

Garry
 
E

Ed

All I have ever used to open an Excel file from Word is stuff like

Dim objXL As New Excel.Application
Dim objWkb As Excel.Workbook
Dim objWks As Excel.Worksheet

Set objXL = New Excel.Application
objXL.Visible = True
objXL.Dialogs(xlDialogOpen).Show
Set objWkb = objXL.ActiveWorkbook

or

Dim objXL As Excel.Application
Dim objWB As Excel.Workbook
Dim strPath As String

strPath = "C:\Documents and Settings\YourUserName\Desktop\Book1.xls"

Set objXL = New Excel.Application
objXL.Visible = True
Set objWB = objXL.Workbooks.Open(strPath)

Speaking from hard experience as a Newbie, Level 2, you might want to ask
someone what the code does, so if it ever breaks (like now) you can better
understand how to find out what's going wrong. I copied a lot of stuff in
my beginnings (about 2 years ago), and even had whole macros written for me
by the good people here. Unfortunately, when I needed to tweak or refine, I
had no idea what was going on and no idea how to change it. Most people
will gladly answer questions about "what does this do?"

Ed
 
Top