Problem with Importing Excel Data (1 Viewer)

[poporacer]

I have an Access database with a button on a form that when the user presses the button, there is some VBA code that checks to see if Excel Is running, opens Excel, get the data from the spreadsheet and then closes excel if it wasn't open before. The problem is that when this process is completed, Excel (which is minimized) has the focus. I want the focus to return to the textbox on the form that the user was working with. Is there another method I should use or a method that will return the focus to Excel? :banghead:
Here is the code for the button:

Private Sub cmdUpdate_Click()
Dim oXLApp As Object
Dim oXLBook As Object
Dim ImportDate as Date
Dim oXLSheet As Object
Dim lastRow As Integer
Dim ValidData As Boolean
Dim isExcelOpen As Boolean
Dim lngHandle As Long
Dim lngRet As Long
  
isExcelOpen = True
On Error GoTo PROC_ERR
 If FileExists(gSalesDataPath) = False Then
    MsgBox "You are missing the SalesDataReport.xls file!" & _
        vbCrLf & "It needs to exported and saved in the" & _
        vbCrLf & CurrentProject.Path & " folder", vbExclamation, "Missing file"
    Exit Sub
End If
 If isCorrectFormat(gSalesDataPath) = False Then
    MsgBox "The SalesData.xls file is in the wrong format!" & _
    vbCrLf & "Make sure you select the Microsoft Excel 97-2000-Data Only format" & _
    vbCrLf & "and select the Minimal option", vbOKOnly, "Wrong File Format"
    Exit Sub
End If

 StartDateInput:
ImportDate = Me.txtImportDate
 
If IsDate(ImportDate) = False Or Day(ImportDate) <> 1 Then
    MsgBox "You did not enter a valid date" & _
    vbCrLf & "Make sure you select the first day of the month", vbOKOnly, "Bad Data"
    GoTo StartDateInput
End If
    
If ImportDate >= DateSerial(Year(Date), Month(Date) + 1, 1) Then 'trying to import the future
    MsgBox "You cannot import the future! The progam is not that smart yet", vbExclamation, "Future date"
    GoTo StartDateInput
End If
 If ImportDate >= DateSerial(Year(Date), Month(Date), 1) Then 'Importing current month
    MsgBox "You are importing the current month. You will not have a complete" & _
    vbCrLf & "month of data. Make sure you import the data again after the month is complete.", _
    vbInformation, "Incomplete Month"
End If
 ImportDate = CDate(Month(ImportDate) & "/1/" & Year(ImportDate))

 On Error Resume Next
Set oXLApp = GetObject(, "Excel.Application")    'Bind to existing instance of Excel
 If Err.Number <> 0 Then    'Could not get instance of Excel, so create a new one
    isExcelOpen = False
    Err.Clear
    Set oXLApp = CreateObject("Excel.Application")
End If
 On Error GoTo PROC_ERR
 Set oXLBook = oXLApp.Workbooks.Open(gSickLeavePath)
oXLApp.Visible = False
Set oXLSheet = oXLBook.Sheets(1)
  
 'Import Excel Data code here
     
oXLApp.Visible = True
 lngHandle = apiFindWindow(CStr("XLMain"), 0&)
lngRet = ShowWindow(lngHandle, SW_SHOWMINIMIZED)
  
 oXLBook.Saved = True
oXLBook.Close
If isExcelOpen = False Then
    oXLApp.Quit
End If
Set oXLBook = Nothing
Set oXLSheet = Nothing
Set oXLApp = Nothing
 

Me.txtImportDate.SetFocus

 Exit_UpdateClick:
Exit Sub
 PROC_ERR:
 MsgBox "Error: (" & Err.Number & ") " & Err.Description, vbCritical
If Not (oXLApp Is Nothing) Then
    oXLApp.Visible = True
    lngHandle = apiFindWindow(CStr("XLMain"), 0&)
    lngRet = ShowWindow(lngHandle, SW_SHOWMINIMIZED)
    
    
    oXLBook.Saved = True
    oXLBook.Close
    If isExcelOpen = False Then
        oXLApp.Quit
    End If
    Set oXLBook = Nothing
    Set oXLSheet = Nothing
    Set oXLApp = Nothing
    
End If
Resume Exit_UpdateClick
 End Sub

And in a Module, I have this code in case you wondered what it did:

Function isCorrectFormat(filePath As String) As Boolean
Dim oExcel          As Object
Dim oExcelWrkBk     As Object
Dim oExcelWrSht     As Object
Dim isExcelOpen As Boolean
Dim lngHandle As Long
Dim lngRet As Long
 isExcelOpen = True
'Start Excel
    On Error Resume Next
    Set oExcel = GetObject(, "Excel.Application")    'Bind to existing instance of Excel
     If Err.Number <> 0 Then    'Could not get instance of Excel, so create a new one
        isExcelOpen = False
        Err.Clear
        Set oExcel = CreateObject("Excel.Application")
    End If
    On Error GoTo Error_Handler
    oExcel.Visible = False   'Keep Excel hidden until we are done with our manipulation
                             
    Set oExcelWrkBk = oExcel.Workbooks.Open(filePath)    'Open the workbook
    Set oExcelWrSht = oExcelWrkBk.Sheets(1)
    If oExcelWrSht.Cells(1, 1) <> "" Then
        isCorrectFormat = True
    Else
        isCorrectFormat = False
    End If
 Error_Handler_Exit:
    On Error Resume Next
    
    oExcel.Visible = True
    lngHandle = apiFindWindow(CStr("XLMain"), 0&)
    lngRet = ShowWindow(lngHandle, SW_SHOWMINIMIZED)
    
    oExcelWrkBk.Close False
    If isExcelOpen = False Then
        oExcel.Quit
    End If
    
    Set oExcelWrSht = Nothing
    Set oExcelWrkBk = Nothing
    Set oExcel = Nothing
    Exit Function
 Error_Handler:
    MsgBox "The following error has occured." & vbCrLf & vbCrLf & _
            "Error Number: " & Err.Number & vbCrLf & _
            "Error Source: isCorrectFormat" & vbCrLf & _
            "Error Description: " & Err.Description, _
            vbCritical, "An Error has Occured!"
    Resume Error_Handler_Exit
End Function

 

[vbaInet]

Create a new instance of Excel in the background and don't try to grab the current instance. You're opening a new file so you don't need the current Excel instance. With this approach your Excel instance will stay hidden without you even invoking the Visible command.

 

[poporacer]

Thanks, it works perfectly... I don't know why I didn't think of that.

 

[vbaInet]

Good to hear!