Code Fails at Remote Site

Question

There are two models for working remotely:

Model One is the company issues a laptop to an employee with all the software it needs to do the job. In this model, data is sucked across the internet.

Model Two is to use the local computer to log into a computer on site. In this model the only thing that is needed is that which is used to drive the display.

I've developed a series of spreadsheets that work for me (model two) and my bosses (usually they are in the office). Our model one employee frequently has issues with the spreadsheets crashing at random places. I have convinced everyone to reboot their computers at least once a week to minimize the effects of memory leaks.

What appears to be happening is that the data transfer is slow and Excel is becoming "impatient" and terminating processing with a 1004 Runtime error. When the coed does run end-to-end, it still takes two to three times longer than it does for me and for those in the office.

I put in a number of DoEvents in the code at what I believe are critical points, but they do not seem to help.

Does anyone have any other suggestions on how to trap and resolve this issue?

Answer 1

I haven't meet same situation. I'd suggest you create a new thread on  Stack Overflow which is special channel to handle VBA related questions.

Hope they can give you suggestions on it. Thank you for your understanding.

Answer 2

The system that is failing is an enterprise license to the company the version is 2408 and the Build is 17928.20156.

There are 5 spreadsheets involved, but so far only two of them are exhibiting the issue. Unfortunately, there is too much proprietary data involved to sanitize the files for public publishing. Also, the problem is very intermittent and random. I've not been able to reproduce the errors. Howeve,r I will describe them.

The user logs into a 3rd party application, runs a report and downloads the data to a CSV or XLS(!) file. I use Power Query to read the data into tables in the application. We have two dozen departments. The application looks at each of them and summarizes the information into a Table for a display and also creates a file with information specific to that department. The Table is included in the body of the email and the table is attached and the mail is sent off. In addition, information is summarized and written to a historical table. This process occurs for each department.

The program fails at random departments and at random places in the code.

I suspect the failures are happening when creating the files to be attached for the emails. These are stored with the department names and a date stamp in an archive directory on the server. So the creation and building of these files occurs over the internet. Also, the transactions that write to the historical file also go over the internet.

The most common point of failure is in the paste function:

SomeRange.Copy

OtherRange.PasteSpecial xlPasteValues

OtherRange.PasteSpecial xlPasteFormats

The failure occurs on the second paste special.

I have put in a number of DoEvents into the code where I thought it was critical.

I can rewrite the program to download the data over the internet (There's not much I can do about that.), process it on the C:Drive and then after all the mail has gone out, move all the files across the internet to the server.

Answer 3

Could you share a test file to reproduce your issue?

For sharing file, you may upload it to OneDrive or any other cloud drive and then post the link here.

*Please make sure you have removed any sensitive or private information in the sample file before uploading.

Could you share the Excel version which the code fails?