Thank you Dave, your suspicion was justified; here is the code that
works for me (except that the timer for a static loading of the workbook
is only used somewhat differently to pace the polling for whether the
user has closed the workbook, which PowerPoint can see by
ExcelApp.Workbooks.Count=0):
Private Sub CommandButton5_Click() 'Cross-Check
'On optionally clicking, displays for a few preview seconds the Slope
Variability vs. Autocorrelation graph of moloTables&Graphs.xls
Dim ExcelApp As Object, ExcelwBook As Object, wBook As String, wSheet As
Object, Start As Single
Set ExcelApp = CreateObject("Excel.Application")
ExcelApp.Visible = True
wBook =
"D:\moloBackgroundPackageForDriveD\Mishmash\moloTables&Graphs.xls"
ExcelApp.Workbooks.Open (wBook)
ExcelApp.ActiveWorkbook.Sheets("MethodsCompare2B").Activate
ExcelApp.Goto
Reference:=ExcelApp.Workbooks("moloTables&Graphs.xls").Sheets("MethodsCo\
mpare2B").Range("A62:Q96"), Scroll:=True 'works
ExcelApp.Workbooks("moloTables&Graphs.xls").Sheets("MethodsCompare2B").R\
ange("I77").Select 'works
Start = Timer
Do While Timer < Start + 2 'Allow 2 seconds of preview dwell
time and return to Slide 1 automatically
DoEvents ' Yield to other processes.
Loop
ExcelApp.Workbooks(1).Close SaveChanges:=False
ExcelApp.Quit
Set ExcelApp = Nothing
End Sub
I appreciate the hints about object-oriented programming style - I am
only a weekend programmer and avoid any levels of complexity that make
my applications more difficult to maintain, from my point of view.
Writing out the fullblown qualifications on each use is sort of
self-documenting, rather than having to remember or research which
variable means what each time one deals with them; and I am over a dozen
years beyond retirement age besides, with palpable short term memory
deficits.
I still have some trouble getting the Excel workbook to show on top,
rather than having to step to it with some delicate Alt-Tabs. I
speculate that changing the ZOrder of my applications would fix that.
Do I have to go to API for that? I don't see any function for setting
the Z-order of a window in Dan Appleman's old book on Win32 API, which
worked for me under Windows 95 long ago - now I have Vista. Are API
functions independent of the operating system?
Best regards, Roger Belling
--- In ExcelVBA@yahoogroups.com, "David Smart"
<smartware.consulting@...> wrote:
>
> I suspect it is the Range() call in the Reference parameter that is
giving
> you grief.
>
> PowerPoint will be looking in its own DOM for a Range() function, that
might
> not exist.
>
> Try qualifying the Range() call. I would recommend creating a
reference to
> the active sheet
>
> Set ActiveWorksheet = ExcelApp.ActiveWorkbook.ActiveSheet
>
> (untested, but I think that's the syntax) and qualifying the Range()
and
> other on-sheet references to that.
>
> Actually you should have created a reference to the Excel workbook
first, so
> that you don't need to keep referencing through the ExcelApp and
> ActiveWorkbook.
>
> Hmmm ... you do have these variables declared: ExcelwBook and wSheet,
but
> you don't seem to have given them any values.
>
>
> Regards, Dave S
>
> ----- Original Message -----
> From: "Roger" rogerbelling@...
> To: ExcelVBA@yahoogroups.com
> Sent: Sunday, January 15, 2012 6:55 AM
> Subject: [ExcelVBA] How get full live Excel workbook access from
PowerPoint
> slide?
>
>
> >
> > Of the following 2 PowerPoint VBA codes, the first works for me but
the
> > second, a shade more ambitious in trying the .Goto method, doesn't.
> > Can anyone tell why? What is holding up the .Goto? Are there any
holes
> > in MS Office "interoperability"?
> >
> >
> >
> > 'mySlideShow.ppt Slide1
> >
> > Option Base 1
> >
> > Option Explicit
> >
> >
> >
> > Private Sub CommandButton1_Click()
> >
> > Dim ExcelApp As Object, ExcelwBook1 As Object, ExcelwBook2 As
Object,
> > WBook2 As String, MySheet3 As Object
> >
> > Set ExcelApp = CreateObject("Excel.Application")
> >
> > ExcelApp.Visible = True
> >
> > WBook2 =
> >
"D:\Projects\Housing\HousingInflation\ResamplingForUnbiasedTimeSeriesReg\
\
> > ression\MyProtoFolder\MyWorkbook2.xls"
> >
> > ExcelApp.Workbooks.Open (WBook2)
> >
> > ExcelApp.Workbooks(1).Sheets(3).Activate
> >
> > Stop
> >
> > ExcelApp.Workbooks(1).Close SaveChanges:=False
> >
> > ExcelApp.Quit
> >
> > Set ExcelApp = Nothing
> >
> > End Sub
> >
> >
> >
> > Private Sub CommandButton5_Click() 'Cross-Check
> >
> > 'On optionally clicking, displays for a few preview seconds the
Slope
> > Variability vs. Autocorrelation graph of moloTables&Graphs.xls
> >
> > Dim ExcelApp As Object, ExcelwBook As Object, wBook As String,
wSheet As
> > Object, Start As Single
> >
> > Set ExcelApp = CreateObject("Excel.Application")
> >
> > ExcelApp.Visible = True
> >
> > wBook =
> > "D:\moloBackgroundPackageForDriveD\Mishmash\moloTables&Graphs.xls"
> >
> > ExcelApp.Workbooks.Open (wBook)
> >
> > ExcelApp.ActiveWorkbook.Sheets("MethodsCompare2B").Activate
> >
> > Stop 'how make PowerPoint interpreter accept the following Excel
VBA?
> >
> >
> >
> > 'ExcelApp.Goto Reference:=Range("A62:Q96"), Scroll:=True ' "Sub or
> > function not defined"
> >
> > 'Range("I77").Select 'desired cursor resting position
> >
> >
> >
> > Start = Timer
> >
> > Do While Timer < Start + 2 'Allow 2 seconds of preview dwell
> > time
> >
> > 'and return to Slide 1 automatically
> >
> > DoEvents ' Yield to other processes.
> >
> > Loop
> >
> > ExcelApp.Workbooks(1).Close SaveChanges:=False
> >
> > ExcelApp.Quit
> >
> > Set ExcelApp = Nothing
> >
> > End Sub
> >
> >
> >
> >
> >
> > [Non-text portions of this message have been removed]
> >
> >
> >
> > ------------------------------------
> >
> > ----------------------------------
> > Be sure to check out TechTrax Ezine for many, free Excel VBA
articles! Go
> > here: http://www.mousetrax.com/techtrax to enter the ezine, then
search
> > the ARCHIVES for EXCEL VBA.
> >
> > ----------------------------------
> > Visit our ExcelVBA group home page for more info and support files:
> > http://groups.yahoo.com/group/ExcelVBA
> >
> > ----------------------------------
> > More free tutorials and resources available at:
> > http://www.mousetrax.com
> >
> > ----------------------------------Yahoo! Groups Links
> >
> >
> >
> >
> >
> > -----
> > No virus found in this message.
> > Checked by AVG - www.avg.com
> > Version: 10.0.1416 / Virus Database: 2109/4141 - Release Date:
01/13/12
> >
>
[Non-text portions of this message have been removed]
Be sure to check out TechTrax Ezine for many, free Excel VBA articles! Go here: http://www.mousetrax.com/techtrax to enter the ezine, then search the ARCHIVES for EXCEL VBA.
----------------------------------
Visit our ExcelVBA group home page for more info and support files:
http://groups.yahoo.com/group/ExcelVBA
----------------------------------
More free tutorials and resources available at:
http://www.mousetrax.com
----------------------------------
Tidak ada komentar:
Posting Komentar