"Remote server does not exist or is unavailable" - vague automation error

[Isaac]

First a quick explanation for why I didn't post this in the Excel forum ... Because even though it has to do with automating Excel, it's not really about Excel VBA, it's about precisely using ACCESS vba to automate an Excel application, and it's more about the inter-automation itself ... and I have a feeling the corporate wisdom that might be out there about this issue is not at all limited to Excel folks

I have Office 365 Enterprise installed on a virtual Windows server, one leased monthly from IONOS. On that server is installed Office as I mentiond and I have an Access database which does a fair amount of Excel automation. The binding is 100% late bound, createobject etc.

I should mention that this database runs continually (occasionally being stopped, restarted, etc), but generally speaking it's constantly running, going through a 'schedule' of sorts to check on whether certain 'jobs' need to be done. When it finds a job that needs doing, it fires off the core code which involves opening and closing Excel workbooks, making small edits, refreshing data connections, etc. etc.

We've had this going for about a year, but recently (with no changes made to the design), we've been getting a lot of instances where the Automation just seems to get disconnected. The errors will be my most dreaded "the remote server does not exist or is unavailable" - dreaded because the only time I've seen that in the past, from my memory, is when you try to automate Excel on a without Excel installed....Or, the code seems to "think" it is running on some other machine where Excel is not installed, even though there is only one machine in play.

So the code will be going along and come to a complete stop with this error - when I debug it is usually on a typical line where an object like wb (previously successfully set to an Excel workbook whose parent is an Excel createobject("excel.application") variable), and just sit there - err.description shows the "remote server does not exist or is unavailable", etc.

I just started happening with no design changes - any ideas?

 

[Minty]

I get this with Powerpoint Automations occasionally.
One, in particular, creates/updates about 20 slides within one presentation and loops around and creates 7 or 8 different versions of the same base template file.

If I hit debug when it happens and simply press continue, it invariably continues to function without any further error. Until the next time.
Your guess is as good as mine...

I've just upgraded to 64Gb of memory in my laptop to see if it was running out of resources, but if it was I wasn't getting any messages to that effect.

 

[Isaac]

I know exactly what you mean and I believe I've done the same thing a few times too - give it a few seconds and then continue. this time I tried that to no avail. Hmm. This is one of only a few times in my life I've created an app designed to 'continually' run code, and I worry that I'm over my head, because things come into play that a normal Access app (which is constantly "running" but only in one sense - the sense that Access itself handles - not code running 24/7) doesn't incur - memory or caching or who knows what. Somehow this app seems to run into weird problems occasionally and they're head scratchers for me.

I may need to just tell the client to reboot the server once a day - I told her that before but I'm unsure if it was put into strict practice so will follow up on that.

 

[MarkK]

What might be happening is that you create an Excel.Application object, assign it to a variable, and then the user destroys that instance of Excel. Now you have a variable with a pointer to an instance that no longer exists in memory. You can cause this error, for instance, with this code...

Sub TestRemoteServerProblem()
    ' create and show excel application
    Dim xl As New Excel.Application
    xl.Visible = True
    
    Wait 2
    
    ' destroy excel application
    With CreateObject("WScript.Shell")
        .Run "taskkill /f /im Excel.EXE", 0, True
    End With
    
    Wait 2

    ' try to use Excel.Application variable
    MsgBox xl.Workbooks.Count
End Sub

Private Sub Wait(Seconds As Integer)
    Dim clock As Single: clock = Timer
    Do While Timer < clock + Seconds
    Loop
End Sub

If your code creates an instance of Excel and assigns that instance to a variable, you can't control what your user does with that instance, and so your variable is always vulnerable.

One work-around is to only ever "get" your Excel instance as you need it. Consider the code below, in which we try to get our hands on a running instance, and failing that we create a new instance, but still, we never assign that instance to a declared variable.

Private Const XL_STRING = "Excel.Application"

Property Get XLApp() As Excel.Application
    Dim tmp As Excel.Application
    
    On Error Resume Next
        Set tmp = GetObject(, XL_STRING)                    ' try to get existing instance
        If Err <> 0 Then Set tmp = CreateObject(XL_STRING)  ' on failure, create a new instance
    On Error GoTo 0
    
    If TypeName(tmp) = "Nothing" Then Err.Raise 5, , "Could not get or create Excel"
    
    Set XLApp = tmp
End Property

Now, if you have some specific job to do with Excel, use the XLApp Property. Say we want to open a workbook, we simply do this from a command button...

Private Sub cmdOpenWorkbook_Click()
    With XLApp
        .Workbooks.Add "C:\MyFiles\SomeExcelFile.xlsx"
        .Visible = True
    End With
End Sub

See how with this approach we never assign an Excel.Application to a declared variable? Rather, we get or create an instance, do our work, and release all the resources we used.

hth

 

[The_Doc_Man]

In line with some of MarkK's comments, I had some nasty errors in automation of an Excel app through Access VBA and it turned out that I had failed to properly qualify the object in question. Like, if my Excel object was named XLO, then maybe at one point I referenced a Workbook without calling it XLO.Workbook. Tedious though it might be, you need to look for an improperly referenced item.

In particular, with Excel having long-winded and very deep references, like XLO.Workbook(1).Worksheet(3).Row(19).Cell(23).Text.... if ANY of the intermediate items used to build that reference were named without a proper prefix in the definition, that reference might still work until something closes and it turns out it was the implied reference for one of the middle items, you get an immediate BANG ZOOM CRASH. Turns out I had used the wrong Excel object for one of the definitions and ended up having something based on a temporary connection rather than the long-term one.

IF you can trap the error at the time of failure and enter DEBUG mode, you can examine all references to see which one is faulty. You have identified wb as one such case - but perhaps whatever was used to define it is also whacked.

That might not be helpful, but the description comes close to some experiences I had with one of the Excel "art" packages.

 

[Galaxiom]

If your code creates an instance of Excel and assigns that instance to a variable, you can't control what your user does with that instance, and so your variable is always vulnerable.

The Excel Application instance can be opened with Visible = False. That is pretty effective at stopping users interfering unless they get to it through the Task Manager.

 

[Isaac]

@MarkK , @Galaxiom
Yes, in this case the applications are always being opened invisible. Also this is one of my most long term (12 years) and trusted clients, who are more like powerusers/admins, they are aware that this entire VM is mostly just for this scheduler and are under strict instructions not only to never mess with any excel instances they may see in task mgr but also to not even open Excel while the Scheduler (my app) is running for fear it somehow muddles things up. So this is a very special machine with elite access where that problem doesn't come into play. It's only for this purpose, just about.

So I am virtually certain the issue is not a user ending excel, as I've witnessed the problem myself while exclusively logged on.

However, @MarkK , your post contains valuable and informative content that I appreciate and will keep it nearby for possible implementation here or elsewhere to see how it goes..

For the moment the client has agreed to reinstall Office and reboot the server daily.

We do seem to have some buggy problems with Office (it complains that it is installed on Terminal Server sometimes, although it's just a virtual Windows server), but that - and other- problems seem to disappear when reinstalled, at least for a while.

Doc I appreciate your input as well - very true, no doubt, but in this case I am quite certain I have made none of those mistakes, as it has worked 24/7 for the better part of 1 years without this problem but you make some good points and doubtless they will help a future thread wanderer, thanks as always.

I tend to think my whole problem does come back to doing this on a virtual machine, as we've had previous problems with Office saying it wasn't licensed for Terminal Server - but we've convinced that problem to 'disappear' by reinstalling, and changing from desktop office to 365.
I posted about that once here

Microsoft Office Licensing for "Terminal Server"

What is the cheapest option, which will result in a copy of MS Office that contains at least Access Excel and Outlook, which is >= 2007 version (or the equivalent), (I am fine with Desktop, doesn't need to be 365 in fact I prefer to avoid 365 if possible, but am open)........which will work on a...

www.access-programmers.co.uk

 

[Galaxiom]

The fact that it appears to be affected by the remote Desktop client machines is pretty odd but if it isn't just coincidental, it indicates something about Office is being stored on the client machines' user profiles. We have just been through getting 365 Enterprise running on Remote Desktop servers and there were some pretty funky things involved.

BTW We had extra trouble because there are multiple servers in a farm and the licensing had to work across all of them. This is done by using FSLogix which stores the user's central remote desktop profile on another server as a virtual disk that is connected to by whichever server they connect to. We had Office licensing problems caused by corruption of those profiles as well as problems with the Remote Desktop licenses.

There was also something about licensing information apparently being stored in the users' local profiles on the client machine. I don't think the location of that information was ever found.

Do you have Office installed on the client machines? If so, has that installation been registered for those users?
Could be there is some interaction with them not having the local instance registered on their profile.

Maybe try deleting and recreating the user's profiles on the client machines.

The Enterprise License allows of five machines per user.
If you haven't already I would try installing Office on the client machines and have the users register it there too.