Problem Opening Excel from Access through VBA when Excel has active cell

[kerry_ja]

Hi all,

I have a problem opening Excel from MS Access through VBA in certain
situations, that I hope someone can help me with. The procedure works
correctly if Excel is not open. It also works correctly if Excel is
open, and no cell is active (by active, I mean a cell in Excel is
double clicked on, not just selected). It does not work when Excel is
open and a cell is active.

Here is a sample of the code:

Private Sub cmdOpenPasteToolAdj_Click()
Rem Opens an excel workbook, and transfers any current data to it
Rem After user clicks button in Excel, it transfers the information
from Excel to access

Rem Early Binding has to have reference set to Excel object library
Dim objExcel As Excel.Application
Dim objWorkBook As Excel.Workbook
Dim blnExcelAlreadyOpen As Boolean

On Error GoTo ErrorHandler

Set objExcel = GetObject(, "Excel.Application") ' reference an
existing application instance
If objExcel Is Nothing Then ' no existing application is running
Set objExcel = New Excel.Application ' create a new application
instance
Let blnExcelAlreadyOpen = False
Else
Let blnExcelAlreadyOpen = True
End If

Rem Open the Workbook
Set objWorkBook =
objExcel.Workbooks.Open(Application.CurrentProject.Path &
"\ExcelFile.xls", , True)

Rem Set the values in excel to be what is in the database
originally (so as not to lose data)
With objWorkBook.Worksheets(1)
Let .Range("D2").Value = txtTemp.Value
'etc...
End With

objExcel.Visible = True
objWorkBook.Worksheets(1).Cells(1, 1).Value = "WAIT"
Do While (objWorkBook.Worksheets(1).Cells(1, 1).Value = "WAIT")
DoEvents
Loop

With objWorkBook.Worksheets(1)
Let txttemp.Value = .Range("D2").Value
'etc...
End With

Rem Close the workbook and excel
objWorkBook.Close False
Set objWorkBook = Nothing
If Not (blnExcelAlreadyOpen) Then
objExcel.Application.Quit
End If
Set objExcel = Nothing

Exit Sub

ErrorHandler:
If Err.Number = 429 Then 'Excel is not already open, this is okay
Err.Clear
Resume Next
ElseIf Err.Number = -2147417848 Or Err.Number = 424 Then 'Closed
Excel instead of clicking transfer button
Err.Clear
Else
MsgBox "An unexpected error occurred." & vbCrLf & _
"Please note the error, and the circumstances, and inform the
Database Programmer" _
& vbCrLf & "Error #" & Err.Number & " : " & Err.Description,
vbCritical, _
"Unexepcted Error"
End If
End Sub

 

[Guest]

Yes there are always problems running any code if you're editing a cell in
Excel - it seems to put everything on hold. You could try a SendKeys
statement (which behaves as if you've actually hit the keyboard, in this case
Ctrl-Enter):
SendKeys ^~

 

[kerry_ja]

Hi Martin,

I actually tried this already, but it didn't seem to have any effect.
The send keys sends the keys to the currently active application, so
doesn't seem to have any effect, because Excel is not active. If I try
to activate it objExcel.ActivateMicrosoftApp xlMicrosoftExcel, then it
crashes at that point.

 

[Guest]

How about objExcel.Activate?

 

[kerry_ja]

Hi Martin,

If I try objExcel.Activate I get Error # 438 Object Doesn't support
this property or method

 

[Guest]

Sorry - I was using the other way of doing automation:
Dim objExcel As New Excel.Application
(having ticked M/soft Excel Object Library in Tools, References)

 

[kerry_ja]

I discovered the correct way to activate it is:

AppActivate "Microsoft Excel"

If I use this to activate it, then send keys works