Pulling info from Excel to PPT using VBA (while running a presentation)

[Eric B]

Problem:
How do I use VBA to pull information from an Excel worksheet into a
powerpoint presentation without the Excel page becoming the active
application.

Background:
I am creating a Jeopardy game that I want to be more user friendly
for other teachers to use. My goal is to have them write questions and
answers on an excel sheet and have the powerpoint ask the user which
game to open. I have searched and hunted and found out how to open
Excel, read the correct cell, and place the value in my powerpoint
presentation. However, as soon as the code runs, Excel pops up and
becomes the active application.

I think this is my problem area:

With MyXL
.Application.Visible = True
.Parent.Windows(1).Visible = True
' .Application.ActiveWindow.WindowState = xlwindowminimized
(the above line doesn't minimize excel)
End With

I tried changing Visible = False, but then PPT is not able to read the
values from the cell and Excel is left running and the only way to close
it down is to go into the Task Manager. I thought about trying to set
the Excel file to automatically minimize when opening but am afraid that
might make it hard to create new questions or modify old questions.

Specifics:
MS Office 2000 (both excel and ppt)

Thanks,
Eric

Whole Code for opening and getting info from Excel (if needed):
' Declare necessary API routines:
Declare Function FindWindow Lib "user32" Alias _
"FindWindowA" (ByVal lpClassName As String, _
ByVal lpWindowName As Long) As Long

Declare Function SendMessage Lib "user32" Alias _
"SendMessageA" (ByVal hWnd As Long, ByVal wMsg As Long, _
ByVal wParam As Long, _
ByVal lParam As Long) As Long

Function Load_Q_and_A(row, column As Integer) As String


Dim MyXL As Object ' Variable to hold reference to Microsoft Excel.
Dim ExcelWasNotRunning As Boolean ' Flag for final release.
Dim temp2 As String

' Test to see if there is a copy of Microsoft Excel already running.
On Error Resume Next ' Defer error trapping.
' Getobject function called without the first argument returns a
reference to an instance
' of the application. If the application isn't running, an error occurs.
Set MyXL = GetObject(, "Excel.Application")
If Err.Number <> 0 Then ExcelWasNotRunning = True
Err.Clear ' Clear Err object in case error occurred.

' Check for Microsoft Excel. If Microsoft Excel is running, enter it
into the Running Object table.
DetectExcel

' Get name of the file to load the questions from
Read_Game_Data

' Set the object variable to reference the file you want to see.
Set MyXL = GetObject(ActivePresentation.Path + "\Questions\" +
GameName + ".xls")

' "Show" Microsoft Excel through its Application property. Then "show"
the actual window
' containing the file using the Windows collection of the MyXL object
reference. Trying
' to activate the Excel window without making it visible at this time.
With MyXL
.Application.Visible = True
.Parent.Windows(1).Visible = True
' .Application.ActiveWindow.WindowState = xlwindowminimized
End With


' Do manipulations of your file here.

Load_Q_and_A = MyXL.Application.Cells(row, column).value


' If this copy of Microsoft Excel was not running when you started,
close it using
' the Application property's Quit method. Note that when you try to
quit Microsoft
' Excel, the title bar blinks and a message is displayed asking if you
want to save
' any loaded files.


' Show Microsoft Excel through its Application property. Then show the
actual window
' containing the file using the Windows collection of the MyXL object
reference.

' x Reminder = InputBox(prompt:="Remember: push [ALT]+[TAB] to switch
back and forth from Excel to Powerpoint.", Title:="Reminder",
Default:="Push enter to conitnue.")



Set MyXL = Nothing ' Release reference to the
' application and spreadsheet.
End Function

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
' Excel is running so use the SendMessage API
' function to enter it in the Running Object Table.
SendMessage hWnd, WM_USER + 18, 0, 0
End If
End Sub

 

[Steve Rindsberg]

Problem:
How do I use VBA to pull information from an Excel worksheet into a
powerpoint presentation without the Excel page becoming the active
application.

If you have a few spare minutes, have a look here and poke at the code in the
example file.

Controlling Office Applications from PowerPoint (by Access MVP Naresh Nichani)
http://www.pptfaq.com/FAQ00795.htm

It's been a while since I looked at it but I think Naresh included an example
of getting data directly from an Excel file w/o even opening Excel.