Issue with opening Excel

[Wolfroolz]

Hello all. I am hoping you can help me with this, I am stumped. Our system generates requests in Excel in a specific format. I created an Excel file with code that lets you select the request you want, and it formats it so it can be imported into access. In my database when the user clicks a button access opens the hidden Excel file, Excel asks them to select which request they want, and access imports the data and closes excel. Everything works great as long as the user doesnt have Excel open. If Excel is open then the hidden file shows up as visible, sometimes it asks which file they want to open multiple times, Access doesnt close the file, and it doesnt import. It also sometimes asks if you want to save the changes to the file. I worked around this by having access check to see if excel is open and then if it is prompting the user to either have access close excel,or to cancel. This is becoming very inconvenient for the end users however, since they have to close excel everytime they want to import a request.

Here is the access vba:

Function GetData()
 
    Dim strMessage As String
    Dim intOptions As Integer       'DECLARATION OF VALUES
    Dim bytChoice As Byte
 
    'MESSAGE BOX TEXT TO VERIFY ACTION
 
    sApp = "Excel.Application"
 
   If IsAppRunning(sApp) = True Then
        strMessage = "THIS WILL CLOSE ALL OPEN EXCEL FILES!!!!  PLEASE SAVE AND EXIT EXCEL BEFORE CONTINUING" & _
        vbCrLf & "" & _
        vbCrLf & "Are you sure you wish to continue?"
 
        intOptions = vbYesNo
        bytChoice = MsgBox(strMessage, 20, "Closing.....ALERT!")
 
        If bytChoice = vbYes Then              ' IF THEY CHOOSE YES
 
            Call Kill_Excel2
            Call sleep1(1)
            Call ImportRequestID
        Else                                    ' IF THEY CHOOSE NO
 
            TempVars("IDNumber").Value = [Forms]![MainRequestViewForm]![TempReqNum].Value
            Exit Function
 
            'DoCmd.RunMacro "Events.Cancel"  ' FEATURE IS NOT DELETED
 
        End If
    Else
 
        Call sleep1(1)
        Call ImportRequestID
    End If
End Function
 
Function ImportRequestID()
On Error GoTo ImportRequest_Err
 
    Dim xlApp As New Excel.Application
    Dim CheckDup As Variant
    Set xlApp = CreateObject("Excel.Application")
    Dim IDNumber As Integer
    IDNumber = 0
 
    xlApp.Workbooks.Open "ExcelImportND.xlsm", True, False
 
    Call sleep1(3)
    TempVars("IDNumber").Value = xlApp.Worksheets("RequestID").Cells(1, 1).Value
    CheckDup = DLookup("[AutoNum]", "[Requests]", "[CGER Ticket] = [TempVars]![IDNumber]")
        If TempVars("IDNumber").Value = "" Then
            TempVars("IDNumber").Value = [Forms]![MainRequestViewForm]![TempReqNum].Value
            GoTo ImportRequest_Exit
        End If
        If Not IsNull(CheckDup) Then
            MsgBox ("This request has been entered.")
            GoTo ImportRequest_Exit
        Else
            DoCmd.RunMacro ("ImportData.ImportRequest")
        End If
 
ImportRequest_Exit:
    xlApp.Workbooks.Close
    xlApp.Quit
    Set xlApp = Nothing
    Exit Function
ImportRequest_Err:
    MsgBox Error$
    Resume ImportRequest_Exit
 
End Function

If needed I can add the VBA in Excel to open and format the file, but I think the issue is in Access. Thanks for taking the time to read this and any help you can offer.

 

[spikepl]

Try

Dim xlApp As New Excel.Application -> Dim xlApp As Excel.Application

 

[Wolfroolz]

Thanks for the quick reply. I tried it, same issue. If excel is open it goes crazy. Sometimes it works but then doesnt close the instance of excel. Sometimes it opens visible, sometimes it asks me to select the file, and after I do, it then asks me select the file again.

It works perfectly if Excel is closed with both Dim xlApp As New Excel.Application and Dim xlApp As Excel.Application

 

[JHB]

Have you looked at the GetObject function?

 

[Wolfroolz]

Thanks JHB, that seems to work great.

I changed the code around to make two functions, one for if you have excel open, which uses the getobject, and the other if you don't, which keeps the old code.

'------------------------------------------------------------
' ImportRequestIDNoExcel
'
'------------------------------------------------------------
Function ImportRequestIDNoExcel()
On Error GoTo ImportRequest_Err
 
Dim xlApp As New Excel.Application
Dim CheckDup As Variant
Set xlApp = CreateObject("Excel.Application")
Dim IDNumber As Integer
IDNumber = 0
 
xlApp.Workbooks.Open "ExcelImportND.xlsm", True, False
 
Do Stuff
 
ImportRequest_Exit:
xlApp.Workbooks.Close
xlApp.Quit
Set xlApp = Nothing
Exit Function
ImportRequest_Err:
MsgBox Error$
Resume ImportRequest_Exit
 
 
End Function
 
'------------------------------------------------------------
' ImportRequestIDExcel
'
'------------------------------------------------------------
Function ImportRequestIDExcel()
On Error GoTo ImportRequestE_Err
Dim xlApp As Excel.Application
Dim CheckDup As Variant
Dim WbXl As Excel.Workbook
Set xlApp = GetObject(, "Excel.Application")
Dim IDNumber As Integer
IDNumber = 0
 
xlApp.Visible = False
Set WbXl = xlApp.Workbooks.Open("ExcelImportND.xlsm", True, False)
 
Do Stuff
 
ImportRequest_Exit:
WbXl.Close
xlApp.Visible = True
Exit Function
 
ImportRequestE_Err:
MsgBox Error$
Resume ImportRequest_Exit
 
End Function
 
Function GetData()
 
Dim strMessage As String
Dim intOptions As Integer
Dim bytChoice As Byte
 
sApp = "Excel.Application"
 
If IsAppRunning(sApp) = True Then
Call sleep1(1)
Call ImportRequestIDExcel
Else
 
Call sleep1(1)
Call ImportRequestIDNoExcel
End If
 
End Function

 

[Solo712]

Thanks for the quick reply. I tried it, same issue. If excel is open it goes crazy. Sometimes it works but then doesnt close the instance of excel. Sometimes it opens visible, sometimes it asks me to select the file, and after I do, it then asks me select the file again.

It works perfectly if Excel is closed with both Dim xlApp As New Excel.Application and Dim xlApp As Excel.Application

Hi there,
as someone who uses Excel integration with Access extensively, I can only say, good luck with trying to get Access work on an open Excel file. I don't think it will work, and of course it makes sense that it should not work. It goes against the principles of Office automation. Access can't know the state of the open Excel file and have the 'other user' write into it at the same time.

Best,
Jiri

 

[Wolfroolz]

Thanks Jiri, but Access never writes into an Excel File. Basically, what I do is:
1. Access opens an Excel Template File with a bunch of VBA
2. Excel Template File prompts user to select a Request File and open it.
3. Template File copies everything in the request file onto the first sheet of itself then closes the request file.
4. Template File breaks down data on first sheet into two seperate sheets, Sheet 2 for request info(user, location etc), Sheet 3 for Item(s) info(description, price, etc)
5. Access runs a saved import that imports sheet 2 and 3 into two seperate tables.
6. Access closes the Template file

At no time does Access write into Excel, and Excel only writes to the template file, not the request file. It is pretty straight forward(it would have to be since I hacked together the whole thing using my vb skills from college 10+ years ago and a bunch of different internet sources, lol). The problem I was having was that Excel was acting inconsistant when it was already open. Originally I checked for Excel being open and closes all instances. Now I do the same check, but run my old code if Excel is closed, or use the GetObject method if Excel is open. Don't really understand why my original code wouldnt work either way, but if this solves the issue I am willing to not understand. I tested it a lot, and it seemed ok, so I pushed it live. I am hoping to not hear any complaints from the end users *fingers crossed*

Hi there,
as someone who uses Excel integration with Access extensively, I can only say, good luck with trying to get Access work on an open Excel file. I don't think it will work, and of course it makes sense that it should not work. It goes against the principles of Office automation. Access can't know the state of the open Excel file and have the 'other user' write into it at the same time.

Best,
Jiri

 

[Solo712]

Thanks Jiri, but Access never writes into an Excel File. Basically, what I do is:
1. Access opens an Excel Template File with a bunch of VBA
2. Excel Template File prompts user to select a Request File and open it.
3. Template File copies everything in the request file onto the first sheet of itself then closes the request file.
4. Template File breaks down data on first sheet into two seperate sheets, Sheet 2 for request info(user, location etc), Sheet 3 for Item(s) info(description, price, etc)
5. Access runs a saved import that imports sheet 2 and 3 into two seperate tables.
6. Access closes the Template file

At no time does Access write into Excel, and Excel only writes to the template file, not the request file. It is pretty straight forward(it would have to be since I hacked together the whole thing using my vb skills from college 10+ years ago and a bunch of different internet sources, lol). The problem I was having was that Excel was acting inconsistant when it was already open. Originally I checked for Excel being open and closes all instances. Now I do the same check, but run my old code if Excel is closed, or use the GetObject method if Excel is open. Don't really understand why my original code wouldnt work either way, but if this solves the issue I am willing to not understand. I tested it a lot, and it seemed ok, so I pushed it live. I am hoping to not hear any complaints from the end users *fingers crossed*

Ok, I see now that the third parameter on open is set to False meaning it's read-only open. No problem!

The standard way of testing whether Excel has already open instance is:

On Error Resume Next
Set xlapp = GetObject(, "Excel.Application")
If Err.Number <> 0 Then
      Set xlapp = CreateObject("Excel.Application")
      blnEXCEL = True
End If
Err.Clear
On Error GoTo 0

The boolean variable records whether your app was opening the Excel object. Then at the end you make the closing of Excel conditional

If blnEXCEL Then xlapp.Quit

ie close it only if you opened the instance.

Best,
Jiri

 

[JHB]

Thanks JHB, that seems to work great.

You're welcome, good luck!
Read what Solo712 writes about the standard way of testing whether Excel already has an open instance.

 

[Wolfroolz]

Thanks Solo, I think I will try to implement that instead of having two separate functions