Returning focus to Access form after opening excel file through ole

[John T Ingato]

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]

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

 

[John T Ingato]

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?

 

[Douglas J. Steele]

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)