Problem with SendKeys and Access 2002 1

[murad5]

Hi Everyone,

I have a function which I have written which allows a user to direct an Access database to an Excel file using the Dialogs(xlDialogOpen).show method. It returns the path to the file. Originally when I wrote it I had the problem that although the instance of Excel would show the dialog box, it would not be activated. I got around this by using the SendKeys command to send {ALT} {SHIFT} {TAB}, which moved the focus to the last application opened. This worked fine in Access 2000. I've just had 2002 installed on my machine, and it's suddenly stopped working! Here's my code, all suggestions gratefully received!

Function getExcel(strName As String) As Boolean

Dim objExcel As Excel.Application
Set objExcel = New Excel.Application
Dim wkbOpen As Workbook
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
SendKeys "%+{TAB}"
Set wkbOpen = objExcel.ActiveWorkbook
strName = wkbOpen.Path & "\" & wkbOpen.Name
On Error GoTo 0
wkbOpen.Close savechanges:=False
Set wkbOpen = Nothing
objExcel.Quit
Set objExcel = Nothing
getExcel = True
Exit Function

Err_Trap:

getExcel = False

End Function

 

[MakeItSo]

If I'm not very mistaken, your
SendKeys "%+{TAB}"
will send the keys one after the other. In order to get the combination of the keys, you must enclose it in brackets:
SendKeys "(%+{TAB})"

But I don't think that SendKeys is a good way to activate. Have you already tried objExcel.WindowState=vbmaximized or similar to activate excel?

MakeItSo

Andreas Galambos
EDP / Technical Support Specialist
(andreas.galambos@bowneglobal.de)
HP:

http://home.arcor.de/andygalambos

 

[murad5]

Thanks for your suggestion Andreas, unfortunately it's still not working. I think what I really need is an Access equivalent to the Excel ActivateMicrosoftApp method. Unfortunately that will activate any Microsoft application except Excel!

 

[MakeItSo]

Just seen something:
Maybe you don't need to focus Excel at all. What are actually trying to do? Looks to me, as if you simply store the path and name of the excel file you chose in Excel's open file dialog and then immediately close that file without saving. What's the meaning of this?

Is this just some test for a more complex interaction with Excel?

MakeitSo

Andreas Galambos
EDP / Technical Support Specialist
(andreas.galambos@bowneglobal.de)
HP:

http://home.arcor.de/andygalambos

 

[murad5]

What I want to be able to do is allow the user to direct the database towards the excel file from which they want to extract data. Unfortunately, I can't tell before time where the users will save the files, or even what they will call them, although I do know the data I want to extract will be in a named range with a certain name. The idea of this function is to allow them to select the file through in the normal way, and then to return the path and filename of the Excel which can be used in a TransferSpreadsheet command in conjunction with the named range...

 

[MakeItSo]

I see...
Try this: add a common dialog to your form (you need to choose it via the "more tools" option)
If you name it e.g. "cd", you can add this to the click event of your command button, that calls GetExcel:

Sub Command1_Click()
    Dim strName 
    cd.CancelError = False
    cd.Filter = "Microsoft Excel (*.xls)|*.xls"
    cd.ShowOpen
    strName  = cd.FileName
save your strName wherever you want...
End Sub

This would make activating Excel superfluous, wouldn't it?
Have a try and let me know if it worked...


Andreas Galambos
EDP / Technical Support Specialist
(andreas.galambos@bowneglobal.de)
HP:

http://home.arcor.de/andygalambos

 

[murad5]

Andreas, thanks for all your help, this has proved the right approach! Unfortunately, I couldn't use the built in "Common Dialog" control, seemingly due to the way in which my MIS department had installed Access, but I was able to find the following website which features code which allows you to use API calls to achieve the same end:

http://www.mvps.org/access/api/api0001.htm

Thanks again...