If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Open Excel from within Access
Hi
I would like to know how to open Excel from within Access. I don't need to open a file - as my Auto-Open macro does that - and it quits Excel too. An overnight scheduled task in Access creates an unformatted sheet, which I would like my Auto_Open macro to format and I'd just like Access to initiate the opening of Excel. Thanks. |
#2
|
|||
|
|||
Open Excel from within Access
Create a new module and paste this code into it. I call mine
modExcelRoutines. Look at the Sub GetExcel for info on how to use this. Option Compare Database Option Explicit ' 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 Sub GetExcel() Dim MyXL As Object ' Variable to hold reference ' to Microsoft Excel. Dim ExcelWasNotRunning As Boolean ' Flag for final release. ' 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 ' Set the object variable to reference the file you want to see. Set MyXL = GetObject("c:\vb4\MYTEST.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. MyXL.Application.Visible = True MyXL.Parent.Windows(1).Visible = True ' Do manipulations of your file here. ' ... ' 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. If ExcelWasNotRunning = True Then MyXL.Application.Quit End If Set MyXL = Nothing ' Release reference to the ' application and spreadsheet. End Sub 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 "Andy" wrote: Hi I would like to know how to open Excel from within Access. I don't need to open a file - as my Auto-Open macro does that - and it quits Excel too. An overnight scheduled task in Access creates an unformatted sheet, which I would like my Auto_Open macro to format and I'd just like Access to initiate the opening of Excel. Thanks. |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
how do i do this? | what were they thinking | Database Design | 8 | January 16th, 2006 11:00 PM |
How do I find a file/spreadsheet that Excel says is Already open but I can't find it? | nwtrader8 | General Discussion | 5 | June 21st, 2005 02:16 PM |
Why is a French Spellchecker a "required" update for English speak | French Spellcheck Required? | General Discussion | 23 | April 26th, 2005 01:17 AM |
transpose | john | Using Forms | 1 | November 24th, 2004 06:16 PM |
Open Excel in different windows | Wardo | General Discussion | 2 | September 24th, 2004 01:24 AM |