Home | Contact Us | FAQ | Search & Site Map | Link to Us
Sign In | Join | Other 45 Sites in Network
Home
Discussion GroupsFormsForms ProgrammingQueriesModules / DAO / VBAReports / PrintingMacrosDatabase DesignSecurityConversionImporting / LinkingSQL Server / ADPMultiuser / NetworkingReplicationSetup / ConfigurationDeveloper ToolkitsActiveX ControlsNew UsersGeneral 1General 2
Access DirectoryToolsTutorialsUser Groups
Related Topics
SQL ServerOther DB ProductsMS OfficeMore Topics ...

MS Access Forum / Forms / November 2006

Tip: Looking for answers? Try searching our database.

Returning focus to Access form after opening excel file through ole

Thread view: 
Enable EMail Alerts  Start New Thread
Thread rating: 
John T Ingato - 29 Nov 2006 21:26 GMT
I have a form the opens an Excel file by use of a filedialog object. Excel
opens up, the file is opened also. some formatting is performed and the file
is resaved under a different name and close.  One problem is, after the file
is closed, how do I close Excel?

Also , prior to performing any functions on the Excel file, I make sure the
file is a valid type of file.  If its formatting is different then expected,
a meesagebox alert is sent. But since the excel application is active or has
the focus, the Access user cannot see the message without switching over.
Is there a way to return focus to the access window?

I threw in a quick fix at the bottom: excelApp.visible = False.  But that
just hide it, right? And once the routine completes, I have lost any handles
to the Excel App, correct?

Code:
   Dim fd As FileDialog   'Declare a variable as a FileDialog object.
   Dim FileToOpen As Variant
   Dim ExcelApp as Object

   'Create a FileDialog object as a File Picker dialog box.
   Set fd = Application.FileDialog(msoFileDialogOpen)

   With fd
       .AllowMultiSelect = False
       If .Show = -1 Then
           FileToOpen = .SelectedItems.item(1)
       Else
       End If
   End With

   Set fd = Nothing

'   Open The selected file
   Set ExcelApp = CreateObject("Excel.Application")
   With ExcelApp
       .Visible = True
'       Only XL 97 supports UserControl Property
       On Error Resume Next
       .UserControl = False
   End With

   Set OpenNew852 = ExcelApp.Workbooks.Open(FileToOpen)

   ExcelApp.Visible = False
Douglas J. Steele - 29 Nov 2006 22:01 GMT
Scrap all that Excel stuff, and use the code in
http://www.mvps.org/access/api/api0001.htm to give you the file dialog.

Take a look at my July, 2005 "Access Answers" column in Pinnacle
Publication's "Smart Access" for sample code to terminate an automated
session of Excel. You can download the column (and sample database) for free
at http://www.accessmvp.com/DJSteele/SmartAccess.html

Signature

Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no private e-mails, please)

>I have a form the opens an Excel file by use of a filedialog object. Excel
>opens up, the file is opened also. some formatting is performed and the
[quoted text clipped - 41 lines]
>
>    ExcelApp.Visible = False
John T Ingato - 30 Nov 2006 02:49 GMT
Yes,... I do like that code better then fileDialog.  Cleanly return a
string.  What is ahtAddfilterItem() & ahtCommonFileOpenSave().  There is no
reference to these functions in access/help.  which library?   What is the
meaning of life....  so many questions..

Also, I do need to open Excel.  I receive a CSV file from a retailer that
need to be parse and cleaned up prior to importing/appending into a table.
I had originally wrote the procedures in Excel.  So I would open up excel,
run the macros save the file, open access and import the file.
I moved all my code over into Access, but still need to open excel thru
automation so I can process the file.  My question is this, if I do open
excel with the code:

Set obj = CreateObject("Excel.Application")

is there a way to close Excel down when I am through with the file?

> Scrap all that Excel stuff, and use the code in
> http://www.mvps.org/access/api/api0001.htm to give you the file dialog.
[quoted text clipped - 49 lines]
>>
>>    ExcelApp.Visible = False
Douglas J. Steele - 30 Nov 2006 11:43 GMT
ahtAddfilterItem() & ahtCommonFileOpenSave() are functions within the code
presented on that page.

You need to copy everything in the shaded area (between Code Start and Code
End) into a new module and save that module (make sure you don't name the
module the same as any of the routines within it.) Once you've done that,
the 2 or 3 line example at the top of the page is all you need to invoke the
dialog.

The other article to which I pointed you has the code for dealing with
Excel. You haven't shown me enough of your code. In a nutshell, you need:

obj.Application.Quit
Set obj = Nothing

but you have to make sure that you've closed any open workbooks before you
can do that. If all you've done is open the one workbook, you should be able
to do that using

obj.Workbooks(1).Close SaveChange:=False

(or =True if you want changes to be saved)

Signature

Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)

> Yes,... I do like that code better then fileDialog.  Cleanly return a
> string.  What is ahtAddfilterItem() & ahtCommonFileOpenSave().  There is
[quoted text clipped - 66 lines]
>>>
>>>    ExcelApp.Visible = False
 
Sign In
Join
My Latest Posts
My Monitored Threads
My Blog
My Photo Gallery
My Profile
My Homepage

Start New Thread
Enable EMail Alerts
Rate this Thread



©2008 Advenet LLC   Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.