open second wordbook from Word

[[email protected]]

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

[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

<[email protected]> wrote in message
news:[email protected]...
>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
>

[[email protected]]

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

[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

<[email protected]> wrote in message
news:[email protected]...
> 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
>

[[email protected]]

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

[[email protected]]

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

[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

"Ed" <ed_millis@NO_SPAM.yahoo.com> wrote in message
news:[email protected]...
>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
>
> <[email protected]> wrote in message
> news:[email protected]...
>> 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
>>
>
>