With more agressive trial-and-error experimenting on my remaining
shortfall, I stumbled into a seemingly elegant solution by using the
Shell() function with a vbMaximizedFocus as WindowState parameter, and
demoting the CreateObject() function to a GetObject() function, coming
after the Shell(), to get an object variable for versatile access to the
Excel application.
R.B.
--- In ExcelVBA@yahoogroups.com, "Roger" <rogerbelling@...> wrote:
>
>
> 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