CODEWITHSUNDEEP
vbaexcel
Sam WB
Sam WB

Reputation: 195

Excel VBA: Waiting for another application to complete an OLE action when macro tries to open another workbook

A little background to the title: I've written a macro that gets called on workbook open. It opens a [shared] workbook on a shared directory and pulls in some information to the workbook the user is using.

Any user working with this sheet already has the shared directory mapped to their computer (and the macro finds the correct drive letter).

I've tested this worksheet multiple times with users in my office. I've also tested it and had two people open the workbooks simultaneously to confirm that the macros for both users are able to pull data from the shared workbook concurrently.

So far, I've had no issues.

This sheet then got rolled out to multiple other users in my company. All in all, about 40 people are expected to use this sheet (not necessarily at the same time.. just in total).

One of the users is located in Poland (I'm located in London).

When he opens the workbook, he gets a 'Microsoft Excel is waiting for another application to complete an OLE action' notification. The notification comes with an 'OK' button. Pressing this button seems to have no effect and the workbook effectively hangs on this notification.

I'm having a lot of trouble resolving this problem as I have not been able to replicate it. Does anyone have an idea why this would come up? Code below:

Sub PreliminaryDataImport()
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
Dim x As Variant
Dim usename As String
usename = Environ("USERNAME")
Dim xlo As New Excel.Application
Dim xlw As New Excel.Workbook, wkbk As New Excel.Workbook
Dim xlz As String, regions As String
Dim LRow As Long, LCell As Long, LRow2 As Long
Dim RegionList As String
RegionList = ""


xlz = Sheet1.Range("o1").Value & "\Region Planning\TestDB.xlsx"
Set xlw = xlo.Workbooks.Open(xlz)

If Not Sheet11.Range("S1").Value = xlw.Worksheets("validation") _
    .Range("N1").Value Then
    MsgBox "YOU ARE USING AN OUT OF DATE VERSION" & vbLf & _
        "Please check your inbox or contact xxxx for the current version."
    xlw.Close False
    Set xlo = Nothing
    Set xlw = Nothing
    Call Module7.ProtectSheets
    End
End If

x = CheckValidation(usename, xlw)

'~~ Check to see if User has access to view/modify.
'~~ If they have access, return regions


On Error Resume Next
For i = LBound(x) To UBound(x)
    regions = regions + " --- " & x(i)
    RegionList = RegionList + x(i) & ", "
    Sheet1.Cells(i + 2, 33).Value = x(i)
Next
If Err.Number <> 0 Then
    MsgBox "You do not have access to view or modify any regions."
    xlw.Close False
    Set xlo = Nothing
    Set xlw = Nothing
    End
Else
    MsgBox "You have access to view and modify the following regions:" & vbLf _
        & vbLf & regions & "---"

I believe the issue occurs somewhere within this section of the code as the msgbox on the last line doesn't show up prior to the notification. I haven't been able to run in debug from his machine as he's located remotely and that would be a large effort (should only be done if absolutely necessary).

Anyone have ideas on why this one user is getting this error? I'm particularly confused because it's only him having the issue.

Upvotes: 1

Views: 11288

Answers (1)

to StackOverflow
to StackOverflow

Reputation: 124794

One thing that looks a bit suspicious is that you're creating a new instance of Excel

Dim xlo As New Excel.Application

Normally this is done so that a hidden instance of Excel can be used to open a workbook that you don't want to show to the user, but I don't see any code to hide this second instance, i.e.:

xlo.Visible = False

Since you open and close the shared workbook quickly, and you have ScreenUpdating = False in your main Excel instance, you may be able to do this in your main Excel instance without the overhead of creating a new Excel instance.

Also you aren't calling xlo.Quit to close the second Excel instance, so it may hang around in the background...

An alternative approach would be to use OleDb to read from the shared workbook, in which case you don't need to open it at all.

Upvotes: 1

Related Questions