Excel VBA Error #1004 – Excel cannot access the file

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.

And to make things both simpler as well as complex, the older style UNC path would no longer be allowed; instead all paths have to be DFS pointers.

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:

  1. Create a new workbook from a template
  2. Save the new workbook with a unique file name
  3. Copy some text from the source workbook
  4. Paste it into the new workbook
  5. Run some more vba to make the new worksheets pretty
  6. 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.

Advertisements

Excel VBA – Execute macro code in another workbook

File this in the realm of why would I ever do that – then maybe after I explain you’ll see it makes sense.

I do this in situations where I’m ‘exploding’ data into reports.  And while this is more of a PeopleSoft nVision trick, I don’t see why it wouldn’t be useable in other applications.

Let me set up the scenario.  I have a chunk of data dumped into a workbook – I’m going to call it wbSource.  Inside that workbook is a macro that is going to run thru a worksheet and select a section of it based on some value in a column.

I copy and then paste the subset of data into another worksheet.  Nice, but I want it in another workbook.

Okay.  A bit of work here, I create another workbook and use it as a template.  That workbook has whatever base formatting I want along with macro code of its own.

Inside my wbSource I have the following code:

Private Function CreateTargetWorkbook() As Workbook

Dim wbTarget As Workbook
Set wbTarget = Workbooks.Add()
wbTarget.Activate
ActiveWorkbook.SaveAs Filename:=, FileFormat:=FileFormatNum
Set CreateTargetWorkbook = wbTarget
Set wbTarget = Nothing
End Function

The above function gets called this way:

Dim wbTarget As Workbook
Dim strRunCommand as String
Set wbTarget = CreateTargetWorkbook

I do my copy and paste code here…


strRunCommand = “‘” &  & “‘!Report_Main”
Application.Run strRunCommand

The italics make it a bit hard to read.  The string you create has a single quote surrounded by double quotes, the fully qualified path and file name, then another double quote, a single quote, an exclamation point, then the name of the macro that gets run followed by an ending double quote.  That gets added as a parameter to the Application.Run command.

Once the called macro code finishes control is passed back to the code in the source/caller workbook.

Now about why I go thru this.  First of all it follows good programming practice in that this follows the Principle of least privilege.  Code that knows how to section and subsection data is now separate from code that knows how to beautify a worksheet.  It also makes it easier to debug.  And later if changes are needed to the way the page is displayed, you don’t take a chance of breaking the code that chunks thru the data.  Vice versa if a change in terms of data occurs.

nVision Error Handling – Write to the Windows Event Log

Wouldn’t it be great to be able to write to a log file that is centrally located, is standard, and built inside of Windows?

Why not write to the system event log? First – DON”T use this suggestion available from Microsoft.  It’s actually horrid looking.  Read it to appreciate where I’m going to be leading you to below.

Old school VB and now VBA create and then use COM object references.  So here we are going to create an object referencing the Windows Scripting libraries.  WSH is getting old but is still inside the current operating systems (Windows 2008 and Windows 7).

MS Technet has a nice outline here on Windows Scripting – the link directs you to an article on writing to the event logs.

Let’s do one better – here is all you need to write to the event log:

Dim WshShell As Object

Set WshShell = CreateObject(“WScript.Shell”)
WshShell.LogEvent
 , Set WshShell = Nothing

That is it.  When your code does write to the event log, it will be available in the Windows Application Logs, and the Source will be WSH.

I created a module way back in about 2002 that I bring into all my nVision programs.  There are two parts to it – an enumerated type that is used to send an optional icon type; and the function that writes to the event  log.

I have a link to a Word document with the code in it here.

For the other part – here is how to throw errors that will get written to the event log.

VBA does not allow the more modern try … catch type of error handling – you declare your error handling procedure in a GoTo statement (this carries over from older BASIC).  Your error declaration becomes:

On Error GoTo Error_Handler

So in a subroutine or function you would write this as:

Private Function doSomeWork( ByRef foo as VariableType)

On Error GoTo Error_Handler

Dim ErrorHeader As String

ErrorHeader = “Oops, a bad thing happened here”

Exit Function

Error_Handler:

Dim strErrorMessage As String  

strErrorMessage = strErrorMessage & ”  Error Source: ” & Err.Source & “.  ”
strErrorMessage = strErrorMessage & “Error #” & Err.Number & ” – ”
strErrorMessage = strErrorMessage & “Description: ” & Err.Description & “.  ”
strErrorMessage = strErrorMessage & “VBA Help Message # ” & Err.HelpContext & “.  ”
strErrorMessage = strErrorMessage & “DLL Error: ” & Err.LastDllError & “.  ”
WriteErrorLogEntry ErrorHeader & strErrorMessage

End Function

You can of course get a lot more creative than this – when I was recently debugging macro code during our conversion to Office 2007 on a 64 bit Windows 2008 machine – I wrote to the event log at several points in functions/sub-routines that were causing me problems.  That way I could have nVision run in the background but still provide messages about what state the code was in during particular points in the program run.

Excel VBA Classes – high speed low drag but with costs

Look – let’s be honest here – anyone doing programming in this day and age has at some point been exposed to object based and/or object-oriented programming.  In the words of Jack and Stan – Nuff said!

So let’s recap.  VBA allows three types of variable definitions:

Intrinsic – these are the usual suspects – Strings, Integers etc.  Have scope depending on where declared.  If you don’t declare a variable as a specific type it becomes a Variant wasting 32 bits of space plus added overhead.

UDT or User Defined Types – a collection of variables which can also contain arrays.  Must be declared/defined at the module level – then used as a variable definition later on in your code.  Used just as an intrinsic type, multiple variables can be declared using your UDT.

Class definitions – a template that contain three elements – Properties, Methods and Events.  Where have you seen those before…

All this can be looked up – it’s the reason for using a class versus any other type that I want to discuss.

Intrinsic types are the building blocks and are pretty much self explanatory.  UDT’s are a nice way to create a collection of intrinsic variables and because of that are very helpful.

Classes not only provide a collection of intrinsic variables but also allow code to be written inside the class definition that deals with the data inside the class.  That is what can make a class very handy to have.

I’ve used classes in a couple of ways in my coding.  One is a way to keep – think of a scratch pad on the side.  I’m dating myself here – but remember back in the day doing a math quiz or test?  You had scratch paper on the side to allow figuring etc. of a problem but the answer was on a different sheet.  Classes can be used as that – plug some data in, do some manipulation and get an answer.  Each object that gets declared with a single class definition can obviously have unique and different data – but the manipulation of that data remains a constant.

Second way I use classes is due to the reuse factor.  For example I have a utility I wrote as a class – it provides computer name, if the instance (now that we are in the virtual world) is a server or workstation etc.  I can and do plug that class into other projects as warranted – makes my life easier.

But here is my all time favorite reason for using classes versus just regular code modules – the Friend keyword.  This harkens back to C and C++ concepts – a variable, function etc. should have the minimum amount of visibility as possible.  Private locks things down – and Public exposes for all to see.  But Friend is a pre-Java Interface construct.  The visibility is throughout the project BUT nowhere else.  In other words a class function can’t be inadvertently called by some other code running on the same machine.  And since a lot of my VBA coding is being done for PeopleSoft nVision reports that are run unattended on a server – that provides me with a more comfortable feeling.

Classes are higher in costs – frankly I find myself trying to get further and further away from them.  But they are a valuable part of your toolkit and well worth your time to investigate and use them.

%d bloggers like this: