Error when code runs in .accde / runs fine on .accdb (AC2007) (1 Viewer)

AOB

Registered User.
Local time
Today, 08:20
Joined
Sep 26, 2012
Messages
613
Hi guys,

I have a subroutine in a split database (distributed as an .accde file) which runs fine for me but which causes an error on another user's machine?

The subroutine is used to compile an Excel report (using late-bound objects rather than referencing the Excel object library directly - I thought that would circumvent these kinds of problems!)

What's really strange is, when my colleaugue reported the error, I tried to reproduce the problem by running the same subroutine on his computer but using the .accdb version on the network (as the VBA is obviously not available in the .accde on his local machine) - and it worked perfectly fine??

The error he is getting, per my error handler is : "1004 : Unable to set the LeftHeader property of the PageSetup class"

Which suggests that the problem lies at the line highlighted in red in the code (have reduced this down for clarity but left anything which I feel may help to identify the problem)

Code:
Option Compare Database
Option Explicit
 
Private appExcel As Object
Private objWorkbook As Object
Private objDataSheet As Object
 
Public Sub CreateReport(rst As Recordset, strDesc1 As String, strDesc2 As String, strDesc3 As String)
 
  On Error GoTo ErrorHandler
 
  Set appExcel = CreateObject("Excel.Application")
 
  With appExcel
 
    Set objWorkbook = .Workbooks.Add
 
    With objWorkbook
 
      Set objDataSheet = .Sheets.Add
 
      With objDataSheet
 
        .....
 
        With .PageSetup
 
          .PrintTitleRows = "$1:$1"
          .PrintTitleColumns = ""
 
          [COLOR=red].LeftHeader = "&K00-024" & strDesc1[/COLOR]
          If strDesc2 <> "" Then .LeftHeader = .LeftHeader & vbCr & strDesc2
          If strDesc3 <> "" Then .LeftHeader = .LeftHeader & vbCr & strDesc3
 
          .RightHeader = "&K00-024Report" & vbCr & GetFullName(cSysInfo.UserName) & " " & Format(Now, "dd mmm yyyy hh:nn:ss")
 
          .CenterHorizontally = True
          .CenterVertically = False
          .Orientation = 2                ' xlLandscape
          .Draft = False
          .Zoom = False
          .FitToPagesWide = 1
          .FitToPagesTall = False
 
        End With
 
      End With
 
      .ErrorCheckingOptions.NumberAsText = False
      .WindowState = -4140                    ' xlMinimized
      .Visible = True
 
    End With
 
  End With
 
Exit_CreateReport:
 
    Set appExcel = Nothing
    MsgBox "Report Ready!", vbInformation
    Exit Sub
 
ErrorHandler:
 
    Call LogError(Err.Number, Err.Description, "CreateReport", "modReportFunctions")
    Resume Exit_CreateReport
 
End Sub

It's a tricky one because I can't capture and debug the error at run-time as it only happens when running the .accde version, and only on my colleague's machine (both .accdb and .accde work fine for me on mine)

All I have to go on is the error that is logged by the handler.

Any suggestions?

Thanks

Al
 

gemma-the-husky

Super Moderator
Staff member
Local time
Today, 08:20
Joined
Sep 12, 2006
Messages
15,614
might be an issue with the excel library. an .db can resolve some library references, whereas an .de may not be able to.
 

AOB

Registered User.
Local time
Today, 08:20
Joined
Sep 26, 2012
Messages
613
Sorry for the delay guys, was away in France at a wedding...

spikepl - yes, both of us using Windows 7 / Office 2007

Dave - yeah, I was doing some research and found some threads suggesting that was the case, but I deliberately used late-binding so as to avoid issues with library references. In any event, I wouldn't have thought this was a particularly complex object property to be manipulating?
 

gemma-the-husky

Super Moderator
Staff member
Local time
Today, 08:20
Joined
Sep 12, 2006
Messages
15,614
late binding should fix reference issues. are you sure the problem user actually has excel at all?
sorry - obviously yes, or it wouldnt get that far

.LeftHeader = "&K00-024" & strDesc1

Any idea what this is supposed to resolve to on the "funny" machine?
 

AOB

Registered User.
Local time
Today, 08:20
Joined
Sep 26, 2012
Messages
613
strDesc1 is a string variable passed to the subroutine as an argument (there are actually 3 description variables, based on the filter criteria of the subform which provides the data for the report)

The "&K00-024" is for formatting purposes (to make the text a light gray in Calibri font - I got this from the recorder) - I don't know how else to edit the font of a header / footer other than using - what I assume to be - HTML tags?
 

AOB

Registered User.
Local time
Today, 08:20
Joined
Sep 26, 2012
Messages
613
Resurrecting this thread as having a similar, but more fundamental, error elsewhere

Basically, I have some import functionality which requires opening a file in an instance of Excel, performing some rudimentary checks / formatting, and saving.

The code is fairly basic, and again, late-bound :

Code:
Public appExcel As Object
....
Dim wbkExcel As Object
Dim shtExcel As Object
...
Set appExcel = CreateObject("Excel.Application")
  With appExcel
  Set wbkExcel = .Workbooks.Open(strFile)
    With wbkExcel
    Set shtExcel = .Sheets(strTab)
    ....

As before, this is raising an error on a user's machine :

424 : Object Required

But not on mine?

And, also as before, when I run the .accdb version on the user's machine, it runs without error?

There is nothing more complex than the code above; it just seems as if the .accde version cannot resolve the Excel Application object (or one of its derivatives) even though the .accdb version, on the same machine, can?

And, again as before, I can't debug the .accde version and I can't trap any error in the .accdb version - so I'm stabbing in the dark as to the problem? (But these are the only objects used in the function where the error is being raised)

The only thing I can think of is to add the reference to the Excel object library and switch to early-binding but I'm loathe to do that in case somebody, somewhere, is using an earlier version of Excel.

As a general rule, I prefer early-binding for development and late-binding for deployment and I'd like to stick to that convention here.

Anybody have any suggestions??
 

spikepl

Eledittingent Beliped
Local time
Today, 09:20
Joined
Nov 3, 2010
Messages
6,144
Verify which line gave the error and that all other variables required at that stage had requireds values
 

AOB

Registered User.
Local time
Today, 08:20
Joined
Sep 26, 2012
Messages
613
Thanks spike

Have added some code to track exactly where the error is raised and the variable values

Here is the precise line in the code where the problem occurs (in red)

Code:
Public appExcel As Object
....
Dim wbkExcel As Object
Dim shtExcel As Object
....
Set appExcel = CreateObject("Excel.Application")
 
  With appExcel
 
    .DisplayAlerts = False
 
    [COLOR=red]Set wbkExcel = .Workbooks.Open(strFile)[/COLOR]

So the code is able to open an instance of Excel and turn off the alerts

But cannot create the Workbook object within it?

Have validated that all variables hold their correct values at this point (strFile is a UNC pointing to a valid and available Excel workbook)
 

spikepl

Eledittingent Beliped
Local time
Today, 09:20
Joined
Nov 3, 2010
Messages
6,144
and the Strfile was not already open at that stage, in some lingering background Excel process perhaps ?
 

AOB

Registered User.
Local time
Today, 08:20
Joined
Sep 26, 2012
Messages
613
Nope, definitely not, the file is actually created in a prior function - saved locally from an attachment in an Outlook e-mail, it does not exist until this code is run (but can see it has been saved and is 'present & correct' at run-time)
 

spikepl

Eledittingent Beliped
Local time
Today, 09:20
Joined
Nov 3, 2010
Messages
6,144
Ahem - are you sure the file is there already?

Test your code on a copy of the file (do not write it in code - just leave the previous one in place) if that runs it means that a small delay might be in order - I had some issue of this sort myself, and a Wait : silly loop lasting 2 seconds solved that.
 

AOB

Registered User.
Local time
Today, 08:20
Joined
Sep 26, 2012
Messages
613
How about this?... Still getting the same error though...

Code:
Public appExcel As Object
....
Dim wbkExcel As Object
Dim shtExcel As Object
Dim objFSO As Object
....
[COLOR=blue]' Ensure source file is present before proceeding[/COLOR]
[COLOR=blue]Set objFSO = CreateObject("Scripting.FileSystemObject")[/COLOR]
[COLOR=blue]Do Until objFSO.FileExists(strFile)[/COLOR]
[COLOR=blue] DoEvents[/COLOR]
[COLOR=blue]Loop[/COLOR]
....
Set appExcel = CreateObject("Excel.Application")
 
  With appExcel
 
    .DisplayAlerts = False
 
    [COLOR=red]Set wbkExcel = .Workbooks.Open(strFile)[/COLOR]
 

spikepl

Eledittingent Beliped
Local time
Today, 09:20
Joined
Nov 3, 2010
Messages
6,144
I prefer to reduce complexity rather that add to it. You have now added a new factor into the equation: test for existence of a file. The question is how does that relate to the ability to open that file. Maybe it is the same thing or maybe not. I'd rather not even delve into that. So your code's ability or inability to open an existing closed file is still not 100 % verified.
 

AOB

Registered User.
Local time
Today, 08:20
Joined
Sep 26, 2012
Messages
613
Okay - re-tested using the copy that was already in place (i.e. file no longer created at runtime) - still the same error as before, in the same place (i.e. setting the wbkExcel object)

(Just to reiterate, this error only occurs with the .accde version of the FE on the user's machine, so the only way I have of testing this is to adjust the code in the master .accdb, make a new .accde, provide the updated .accde to this particular user and have them rerun the process - this all works absolutely fine on my own machine, and also when the user tries the master .accdb on theirs...)
 

spikepl

Eledittingent Beliped
Local time
Today, 09:20
Joined
Nov 3, 2010
Messages
6,144
I have not forgotten that this is accde. If accdb works both here and there but accde does not, that means there is a difference between the systems. Try to make the accde on user's mchine and see if that helps. If it does then you have different versions or setup of win/office.
 

AOB

Registered User.
Local time
Today, 08:20
Joined
Sep 26, 2012
Messages
613
Definitely same versions of both Windows (7) and Office (2007) - can't vouch for setup although I would imagine our system admins would aim to keep everything consistent (sic) - nothing immediately obvious to me anyway?

Tried making the .accde on the users machine (recompiled on users machine first as well) - still getting the same error at the same point though

:banghead:
 

spikepl

Eledittingent Beliped
Local time
Today, 09:20
Joined
Nov 3, 2010
Messages
6,144
Yeah. I know IT keeps everything consistently sic(k) :D

So apparently there is some accde thing going on locally. How about checking if it is the location or filename or Excel or ....?? I.e. do some code converted to accde that just opens a Word file located and named elsewhere ... then Excel ... then same file name, then same location ...
 

irish634

Registered User.
Local time
Today, 04:20
Joined
Sep 22, 2008
Messages
230
Does the user have 'write' permissions on the folder where trying to create the workbook? Probably not it, but I had a similar issue. Mine wouldn't create with either until I granted permission.
 

AOB

Registered User.
Local time
Today, 08:20
Joined
Sep 26, 2012
Messages
613
Hi Irish (fellow patriot? :D)

Yeah I had the same thought; the FE is on the users C: drive and the folder where the file is saved is in the same root directory (C:\Temp\)

However, I'm fairly comfortable that the appropriate write permissions are available as 1) part of the script involves checking for the presence of a TempFiles subfolder to house these files, and creating it if necessary and 2) the file is being saved down as part of the same script and is present at the point of error

Therefore it is both creating and writing to that folder with no difficulty

I am convinced it's an issue with resolving the late-bound wbkExcel object in the .accde (otherwise, I would get the same error in the .accdb version on the same machine and I don't)

For some reason - I suspect - something is going askew with the libraries when working with an .accde over an .accdb (see my original post; this isn't the first time I've come across this)

Thanks for the suggestion though!
 

Users who are viewing this thread

Top Bottom