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

 

[Michael H]

Hi there.

You are most likely not going to be able to resolve this, as it is not an
Access issue, but just the way Excel works. You cannot open another workbook
while a non-blank cell in the current workbook is (to use your terminology)
active.

To illustrate this, open a workbook, and double-click in a non-blank cell to
make it active (or, double-click in a blank cell and type a few characters).
Then attempt to open another workbook by, for example, double-clicking on an
Excel file from Windows Explorer, or on one that is on your desktop (you
won't be able to access Excel's File/Open menu option). You should notice
that the workbook will not open until the cell in the current workbook is
made inactive. The same thing is happening when you try to open the workbook
from Access.

The best solution is probably to educate your users about this situation.

-Michael