R
RWN
wd2k/off2kPro
What I want to do is (from a Word Macro) create an instance or use an
existing instance, of Excel and copy a range of cells.
I figured I'd want this in case the user already had Excel running (I
didn't want to shut them down).
This was made necessary because I can't use the InsertFile function in
Off 2003 so I thought I'd use the "PasteFromExcel" function.
Sometimes it would work, other times it wouldn't so I decided to do some
learning at home (hence my testing is in Off 2k).
This is the 1st time I've delved into this so I'm way over my head here
(a little bit of knowledge is a dangerous thing!).
Here is my test macro (I've added comments with *'s);
Note that it locates/opens the Wb and sheet ok (so I've deleted some
logic for "readability").
Sub GetThePSTable()
Dim XLap As Excel.Application
Dim XLwb As Excel.Workbook
Dim XLSht As Excel.Worksheet
Dim XLPSnm As String
xx = ""
On Error Resume Next
Set XLap = GetObject(, "Excel.Application")
If XLap Is Nothing Then
Set XLap = New Excel.Application
xx = "Using New Object"
Else
xx = "Using Existing Object"
End If
XLap.Visible = True
Set XLwb = XLap.Workbooks.Open(PSDir & PSName)
Set XLSht = XLwb.Worksheets(XLPSnm)
XLSht.Activate
lro = 0
XLSht.Range("a65536").Select
XLap.ActiveCell.End(xlUp).Select
lro = XLap.ActiveCell.Row *******Works OK to
here********
*** The following three statements are what I've been testing (by
uncommenting one at a time)
'XLSht.Cells(3, 1) = "This is inserted" ** This works
'XLSht.Range(Cells(1, 1), Cells(lro, 4)).Select ** This will work
"once in a while"
'XLSht.Range(Cells(1, 1), Cells(lro, 4)).Copy ** This doesn't work
(doesn't copy to the clipboard)
Set rngSect = Ad.Sections(1).Range.Paragraphs(3).Range **N/A to
problem - I set breakpoint here
rngSect.Select
'Selection.PasteExcelTable False, False, True '** Not applicable
to 2k**
XLap.DisplayAlerts = True
XLwb.Close
If xx = "Using New Object" Then
XLap.Quit
Set XLap = Nothing
End If
Set XLwb = Nothing
Set XLSht = Nothing
End Sub
In addition - At the breakpoint I'll get "<Remote server machine does
not exist or is unavailable>"
as a hover message on the "XLSht.range.......Select"
It's right, "the remote server does not exist" - this machines sitting
all alone.
What started as an interesting educational exercise and had turned into
a nightmare as the results are unpredictable.
Can anyone give me hints?
What I want to do is (from a Word Macro) create an instance or use an
existing instance, of Excel and copy a range of cells.
I figured I'd want this in case the user already had Excel running (I
didn't want to shut them down).
This was made necessary because I can't use the InsertFile function in
Off 2003 so I thought I'd use the "PasteFromExcel" function.
Sometimes it would work, other times it wouldn't so I decided to do some
learning at home (hence my testing is in Off 2k).
This is the 1st time I've delved into this so I'm way over my head here
(a little bit of knowledge is a dangerous thing!).
Here is my test macro (I've added comments with *'s);
Note that it locates/opens the Wb and sheet ok (so I've deleted some
logic for "readability").
Sub GetThePSTable()
Dim XLap As Excel.Application
Dim XLwb As Excel.Workbook
Dim XLSht As Excel.Worksheet
Dim XLPSnm As String
xx = ""
On Error Resume Next
Set XLap = GetObject(, "Excel.Application")
If XLap Is Nothing Then
Set XLap = New Excel.Application
xx = "Using New Object"
Else
xx = "Using Existing Object"
End If
XLap.Visible = True
Set XLwb = XLap.Workbooks.Open(PSDir & PSName)
Set XLSht = XLwb.Worksheets(XLPSnm)
XLSht.Activate
lro = 0
XLSht.Range("a65536").Select
XLap.ActiveCell.End(xlUp).Select
lro = XLap.ActiveCell.Row *******Works OK to
here********
*** The following three statements are what I've been testing (by
uncommenting one at a time)
'XLSht.Cells(3, 1) = "This is inserted" ** This works
'XLSht.Range(Cells(1, 1), Cells(lro, 4)).Select ** This will work
"once in a while"
'XLSht.Range(Cells(1, 1), Cells(lro, 4)).Copy ** This doesn't work
(doesn't copy to the clipboard)
Set rngSect = Ad.Sections(1).Range.Paragraphs(3).Range **N/A to
problem - I set breakpoint here
rngSect.Select
'Selection.PasteExcelTable False, False, True '** Not applicable
to 2k**
XLap.DisplayAlerts = True
XLwb.Close
If xx = "Using New Object" Then
XLap.Quit
Set XLap = Nothing
End If
Set XLwb = Nothing
Set XLSht = Nothing
End Sub
In addition - At the breakpoint I'll get "<Remote server machine does
not exist or is unavailable>"
as a hover message on the "XLSht.range.......Select"
It's right, "the remote server does not exist" - this machines sitting
all alone.
What started as an interesting educational exercise and had turned into
a nightmare as the results are unpredictable.
Can anyone give me hints?