August 30, 2013 Leave a comment
This is primarily a PeopleSoft nVision post – but it also pertains to Excel developers. For those not familiar with it, nVision is a wrapper provided by Oracle/PeopleSoft whereby the Excel application can be used by PeopleSoft processes. What gets produced is an Excel workbook.
Excel is installed on an application server, and is called via the nVision wrapper. A number of our nVision layouts (Excel workbooks) have VBA macro code associated with them.
A shift in providing reports to consumers was recently made within the company. Up til recently the reports were available from shares on the application server where nVision/Excel was running. That’s been changed – the reports now have to be made available on a separate file server share.
Our users started running into random problems shortly after the change. The common error was:
Error Source: Microsoft Office Excel. Error #1004 – Description: Microsoft Office Excel cannot access the file ‘some file name’. There are several possible reasons:
The file name or path does not exist.
The file is being used by another program.
The workbook you are trying to save has the same name as a currently open workbook.
VBA Help Message # 1001004.
I was able to pin point what code was throwing the error, and it was in a section that does the following:
- Create a new workbook from a template
- Save the new workbook with a unique file name
- Copy some text from the source workbook
- Paste it into the new workbook
- Run some more vba to make the new worksheets pretty
- Save the new workbook and close it out
Rinse and repeat another several hundred times. It took a couple of tries but from what I determined the error would always get thrown when attempting some action on the new (target) workbook. And since that workbook was now being created in a remote share, DFS was the culprit.
That is a reasonable assumption based on how DFS works. That’s not a topic for this post – if you want more Microsoft has an article here. Note this line from the link – DFS requires Domain Name System (DNS) and Active Directory replication are working properly.
I see DNS and I think HTTP, network packets, domain controllers and RPC. A far too complex environment for VBA to be operating in.
So to resolve the issue I changed where the work was being done. Instead of saving the new workbook over the wire to the final destination and then doing more work to it via VBA; the work is back to being done in the same place the source workbook is. That path is guaranteed by getting the ThisWorkbook.Path value of the source workbook.
So the above list is back to getting accomplished locally. Once step 6 is complete there are two more items to the task list:
- Use FIleSystemObject method CopyFile - and put a copy of the new workbook in the new reports share using DFS
- Then user FileSystemObject method DeleteFile to get rid of the local copy of the new workbook.
No more random errors and the users are back to being happy. And company policy is maintained.