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.

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.

PeopleTools 8.51 nVision – Excel forumulas throw UnrecoverableExceptionFilter

How about an upgrade story?  We are upgrading PeopleTools from 8.48 to 8.51 – as well as upgrading from Windows 2003 32 bit server to Windows 2008 R2 64 bit server – and for a trifecta from Office 2003 to Office 2007.

Okay – so we have nVision reports that worked fine in PTools 8.48 and Excel 2003.  One of my tasks was to upgrade the nVision reports to Office 2007 and then test them in the new PTools environment.  Found this problem – which has been reported to Oracle.

Report would error out with the following as part of the log file entry:

PSPAL::Abort: Unrecoverable exception received
PSPAL::Abort: Location: E:\pt85110b-retail\peopletools\src\pspal\exception_winunhandled.cpp:594: PSPAL::UnhandledExceptionFilter::UnrecoverableExceptionFilter

Couldn’t find anything on the Oracle site, went back and forth several times with Oracle on the service call.  This nVision had several pages and a macro associated with it – the macro was run from a call by NvsInstanceHook.  I took out the call to the macro, and started removing pages – finally got it to run by removing two specific pages in the layout.

I’m not going to go thru all the steps I took to find the bug – and it is a bug – what I found was the 8.51 version of nVision does not properly handle IF formulas in cells.

There was a nested IF statement having 5 truth checks.  There was also both an AND and an OR condition in the statement.  If I whittled the formula down to three checks maximum then the layout would run to success.

Some examples are in order here.  This formula is an edited version of the original – which failed:

=IF(AND(O5<>”Condition One”, O5<>”Condition Two”, O5<>”Condition Three”), IF(OR(O5=”Condition Four”, O5=”Condition Five”), , “”), <Add a bunch of cells>)

So – if I got rid of the OR condition – in essence only going with the AND operator:

=IF(AND(O5<>”Condition One”, O5<>”Condition Two”, O5<>”Condition Three”), “”, <Add a bunch of cells>)

The layout would work fine.  Same if I only used the OR conditional portion of the formula:

=IF(OR(O5=”Condition Four”, O5=”Condition Five”), <Add a bunch of cells>, “”)

Clearly nVison can handle having formulas in cells, plus it can handle the AND and OR conditional operators – it’s the number of truth table checks in the formula nVision is having an issue with.  Which is a shame as per Microsoft you can have up to 64 nested inline IF statements in a formula.

What I finally did was break the formula down – do the AND truth table test in one cell, the OR test in another.  Those cells would be hidden in the report – what was shown was an IF statement where if the value of the AND cell was blank use the value from the OR test.

PeopleTools 8.51 nVision configuration in Windows 2008 R2 64 Bit and Office 2007

We have been upgrading to PeopleTools 8.51 recently.  The company I work for uses both PeopleSoft Financials and PeopleSoft HRMS – and I work on the HRMS side.

Currently client machines use Windows 7 with Office 2007.  Our new Windows server environment will be virtualized instances of Windows 2008 R2 in 64 bit – which does pose some problems with installing and running nVision.

After a number of conference calls with Oracle Support and a lot of trial and error here seems to be the right configuration (at least in my corporate environment – ‘your mileage may differ’) :

  1. For each database instance on the server, either using PSADMIN or directly in the psprcs.cfg configuration file, go to the [nVision] section and set the following and then restart:
    • EnableDrillDownForFile=1
    • EnablePollDialogs=1
    • UseExcelAutomation=1  NOTE – if you are going to be using any macro code at all you must have MS Office installed and this switch must also be set.
  2. In your server PSOFT installation directory appserv\prcs\ create nvision\instance folders inside each and every PSOFT database instance that will be using nVision.  So as an example, in my machine I have <Drive>:\psoft\ hrms90\appserv\prcs\<Instance Name>\NVISION\INSTANCE.
  3. In your W2008 R2  server create a C:\Windows\SysWOW64\config\systemprofile\Desktop directory.
  4. We set the psoft directory in our Windows servers as a share and grant change access in our DEV and QA environments to our dev team.
  5. We set the following Windows Services up to use a service account that is created in Active Directory, and that service account is then set up as a local admin on the server.  For some reason both of these services in installation default to the Local System Account – and the PeopleSoft PSADMIN service sets Allow service to interact with desktop. They both need to be changed to use the This Account – interaction with local desktop needs to be turned off – and the account MUST HAVE LOCAL ADMIN RIGHTS:
    • Oracle Process Manager: ORACLE ProcMGR V10gR3 with VS2008
    • Process Scheduler: PeopleSoft <path to installed instance>
  6. Log into the server with whatever account the two services are using.  My suggestion – use the same account for both services but keep it unique for your PeopleTools installation.  After logging in start Excel.  Answer all the pop-up questions that come up.  Click the Office button – the new large button on the upper left of the opened Excel workbook.  At the bottom of the opened menu you will see a button labeled Excel Options – click on that.  On the left navigation bar click on Trust Center – when the Trust Center page loads click on the Trust Center Settings button you should see on the page to the right.  Again there is a left hand navigation bar – you want Macro Settings – it’s just about in the middle of the options.  Click on Enable all macros (not recommended but if you are going to be using macro code fired by NvsInstanceHook you need to do this).  Then click in the Trust access to the VBA project object model.  Click OK on the bottom of the page but don’t leave the menu just yet.
  7. With Excel still open and the Excel Options menu open – navigate to the top of the left hand navigation.  Select Popular – when the page loads put a check mark in Show Developer tab in the Ribbon.  You may need this to work on macros.

That should do it.  Exit out of Excel, make sure the services are up and running and you should have nVision working.  However!  I have been finding a few gotchas along the way – I’ll try to detail some of those in later posts.

Get Computer Name and other useful VBA functions

I have a number of PeopleSoft nVision reports that have macros attached to them.  PS nVision on the client can be thought of as a wrapper around Microsoft Excel – so from this point on we’ll treat any nVision layout as an Excel workbook.

These workbooks are on different servers or can be run on a clients PC.  A lot of them use templates to create a final report.  The macros need the path to the templates – and while the directory structure in our development to QA to production environments all stay the same – it’s the computer name that obviously changes.

I have some helper functions that I use to help get information used by my various macros.

To get the computer name – in the General Declarations of the code module add:

Private Declare Function GetComputerName Lib “kernel32″ Alias “GetComputerNameA” _
(ByVal lpBuffer As String, nSize As Long) As Long

I also declare some other variables:

Private Const MAX_COMPUTERNAME_LENGTH = 31
Private Const sNameUnknown = “NOT_FOUND”
Private mvalComputerName As String
Private mvalHaveValidName As Boolean

Then it’s all put together in this function:

Private Function ComputerNameIs() As String
    Dim lngLength As Long
    Dim lngResult As Long
    Dim strNameBuffer As String
    ‘Maximum Computer Name + Terminating Null Char
    lngLength = MAX_COMPUTERNAME_LENGTH + 1
    ‘Create Buffer
    strNameBuffer = String(lngLength, “X”)
    ‘Get the computer Name
    lngResult = GetComputerName(strNameBuffer, lngLength)
    If lngResult <> 0 Then
        ComputerNameIs = Mid(strNameBuffer, 1, lngLength)
        mvalHaveValidName = True
    Else
        ComputerNameIs = sNameUnknown
        mvalHaveValidName = False
    End If
   
End Function

I’ll be adding more in future posts.

Check for a printer in Excel VBA Macro

I’ve been doing upgrades to Microsoft Office 2003 Excel workbooks – specifically to VBA macros in the workbooks.

The workbooks are used as PeopleSoft nVision layouts.  PS nVision utilizes Excel to provide reports – however nVision is run on a server.

During the upgrade I kept running into one error in particular – due to the server I was working on not having a printer driver installed.  A number of the macros did print set up as part of the report formatting – without a printer driver the code would bomb.  So I came up with the following function – it determines if a printer is set up on the machine, and returns a Boolean value:

Public Function IsPrinterInstalled() As Boolean
On Error GoTo IsPrinterInstalled_ERR
Dim objWMIService, colInstalledPrinters As Object
Dim strComputer As String
Dim i As Integer

strComputer = “.”
Set objWMIService = GetObject( _
“winmgmts:” & “{impersonationLevel=impersonate}!\\” _
& strComputer & “\root\cimv2″)
Set colInstalledPrinters = objWMIService.ExecQuery _
(“Select * from Win32_Printer”)

i = colInstalledPrinters.Count

Set objWMIService = Nothing
Set colInstalledPrinters = Nothing

If i > 0 Then
 IsPrinterInstalled = True
Else
  IsPrinterInstalled = False
End If

Exit Function

IsPrinterInstalled_ERR:
 If Not objWMIService Is Nothing Then
   Set objWMIService = Nothing
End If

If Not colInstalledPrinters Is Nothing Then
 Set colInstalledPrinters = Nothing
End If

IsPrinterInstalled = False

End Function

Follow

Get every new post delivered to your Inbox.

%d bloggers like this: