Open an Excel File from Access 1

[murad5]

Hi Everyone,

I have a slight problem with an Access function I've written. The intention is to use the Excel Dialogs(xlDialogOpen).show method to allow the user to select the excel file from which they want to import data. Here is the code:

Function getExcel(strName As String) As Boolean

Dim objExcel As Excel.Application
Set objExcel = New Excel.Application
Dim intResponse As Integer
intResponse = MsgBox("Please select the correct Excel " _
& "File to access", vbOKCancel)
If intResponse = vbCancel Then GoTo Err_Trap
On Error GoTo Err_Trap
objExcel.Dialogs(xlDialogOpen).Show
strName = objExcel.ActiveWorkbook.Path & "\" _
& objExcel.ActiveWorkbook.Name
On Error GoTo 0
objExcel.Quit
Set objExcel = Nothing
getExcel = True
Exit Function

Err_Trap:

getExcel = False

End Function

The problem arises because the excel object which is created does not have the focus by default, so when the dialog box is shown, it does not appear on the screen. The workaround I have while developing the code is to hold down {ctrl} + {shift} + {tab} which moves to the last application opened, but that's probably not going to fly with the users. Does anyone have any ideas of how to set the focus to this dialog box?

Thanks...

 

[vbajock]

Look at Sendkeys in the help file. Send the {ctrl} + {shift} + {tab} combo at the appropriate moment.

 

[murad5]

Pure genius!

Thanks...