Go Back   Access World Forums > Microsoft Access Discussion > Modules & VBA

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 06-10-2014, 07:49 AM   #1
AOB
Newly Registered User
 
AOB's Avatar
 
Join Date: Sep 2012
Location: Dublin, Ireland
Posts: 551
Thanks: 132
Thanked 39 Times in 37 Posts
AOB is on a distinguished road
Error when code runs in .accde / runs fine on .accdb (AC2007)

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 = ""
 
          .LeftHeader = "&K00-024" & strDesc1
          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

__________________
There are 10 types of people in this world
Those who understand binary and those who don't
AOB is offline   Reply With Quote
Old 06-10-2014, 01:52 PM   #2
spikepl
Eledittingent Beliped
 
spikepl's Avatar
 
Join Date: Nov 2010
Location: San Serriffe
Posts: 6,144
Thanks: 77
Thanked 980 Times in 958 Posts
spikepl is a jewel in the rough spikepl is a jewel in the rough spikepl is a jewel in the rough
Re: Error when code runs in .accde / runs fine on .accdb (AC2007)

Same window versions ?
__________________

Don't tell us what did not happen.
Do you tell the mechanic about your car "it doesn't work" or vague stuff like:
I couldn't, No joy, To no avail, No go
, Incorrect, Wrong, Seems, Without success, It didn't let me, I/it failed.

Do tell us what did happen
:

I want this ... to happen, I attempted this ..., using this ... code and pressing these buttons, the system did this ... in response, and I got this ... error in this ... line.

Did it help? Pass it forward!

Joys of Access:
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.

spikepl is offline   Reply With Quote
Old 06-12-2014, 01:36 AM   #3
gemma-the-husky
Super Moderator
 
gemma-the-husky's Avatar
 
Join Date: Sep 2006
Location: UK
Posts: 13,791
Thanks: 55
Thanked 1,026 Times in 992 Posts
gemma-the-husky is a name known to all gemma-the-husky is a name known to all gemma-the-husky is a name known to all gemma-the-husky is a name known to all gemma-the-husky is a name known to all gemma-the-husky is a name known to all
Re: Error when code runs in .accde / runs fine on .accdb (AC2007)

might be an issue with the excel library. an .db can resolve some library references, whereas an .de may not be able to.

__________________
Dave (Male!)
Gemma was my dog

if a poster helps you, please click the scales at the bottom left of this posting, or use the thanks button alongside.
gemma-the-husky is offline   Reply With Quote
Old 06-17-2014, 02:37 AM   #4
AOB
Newly Registered User
 
AOB's Avatar
 
Join Date: Sep 2012
Location: Dublin, Ireland
Posts: 551
Thanks: 132
Thanked 39 Times in 37 Posts
AOB is on a distinguished road
Re: Error when code runs in .accde / runs fine on .accdb (AC2007)

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?
__________________
There are 10 types of people in this world
Those who understand binary and those who don't
AOB is offline   Reply With Quote
Old 06-17-2014, 08:25 AM   #5
gemma-the-husky
Super Moderator
 
gemma-the-husky's Avatar
 
Join Date: Sep 2006
Location: UK
Posts: 13,791
Thanks: 55
Thanked 1,026 Times in 992 Posts
gemma-the-husky is a name known to all gemma-the-husky is a name known to all gemma-the-husky is a name known to all gemma-the-husky is a name known to all gemma-the-husky is a name known to all gemma-the-husky is a name known to all
Re: Error when code runs in .accde / runs fine on .accdb (AC2007)

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?
__________________
Dave (Male!)
Gemma was my dog

if a poster helps you, please click the scales at the bottom left of this posting, or use the thanks button alongside.
gemma-the-husky is offline   Reply With Quote
Old 06-18-2014, 01:14 AM   #6
AOB
Newly Registered User
 
AOB's Avatar
 
Join Date: Sep 2012
Location: Dublin, Ireland
Posts: 551
Thanks: 132
Thanked 39 Times in 37 Posts
AOB is on a distinguished road
Re: Error when code runs in .accde / runs fine on .accdb (AC2007)

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?
__________________
There are 10 types of people in this world
Those who understand binary and those who don't
AOB is offline   Reply With Quote
Old 09-22-2014, 05:05 AM   #7
AOB
Newly Registered User
 
AOB's Avatar
 
Join Date: Sep 2012
Location: Dublin, Ireland
Posts: 551
Thanks: 132
Thanked 39 Times in 37 Posts
AOB is on a distinguished road
Re: Error when code runs in .accde / runs fine on .accdb (AC2007)

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 :

Quote:
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??

__________________
There are 10 types of people in this world
Those who understand binary and those who don't
AOB is offline   Reply With Quote
Old 09-22-2014, 05:31 AM   #8
spikepl
Eledittingent Beliped
 
spikepl's Avatar
 
Join Date: Nov 2010
Location: San Serriffe
Posts: 6,144
Thanks: 77
Thanked 980 Times in 958 Posts
spikepl is a jewel in the rough spikepl is a jewel in the rough spikepl is a jewel in the rough
Re: Error when code runs in .accde / runs fine on .accdb (AC2007)

Verify which line gave the error and that all other variables required at that stage had requireds values
__________________

Don't tell us what did not happen.
Do you tell the mechanic about your car "it doesn't work" or vague stuff like:
I couldn't, No joy, To no avail, No go
, Incorrect, Wrong, Seems, Without success, It didn't let me, I/it failed.

Do tell us what did happen
:

I want this ... to happen, I attempted this ..., using this ... code and pressing these buttons, the system did this ... in response, and I got this ... error in this ... line.

Did it help? Pass it forward!

Joys of Access:
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.

spikepl is offline   Reply With Quote
Old 09-22-2014, 06:02 AM   #9
AOB
Newly Registered User
 
AOB's Avatar
 
Join Date: Sep 2012
Location: Dublin, Ireland
Posts: 551
Thanks: 132
Thanked 39 Times in 37 Posts
AOB is on a distinguished road
Re: Error when code runs in .accde / runs fine on .accdb (AC2007)

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
 
    Set wbkExcel = .Workbooks.Open(strFile)
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)
__________________
There are 10 types of people in this world
Those who understand binary and those who don't
AOB is offline   Reply With Quote
Old 09-22-2014, 06:09 AM   #10
spikepl
Eledittingent Beliped
 
spikepl's Avatar
 
Join Date: Nov 2010
Location: San Serriffe
Posts: 6,144
Thanks: 77
Thanked 980 Times in 958 Posts
spikepl is a jewel in the rough spikepl is a jewel in the rough spikepl is a jewel in the rough
Re: Error when code runs in .accde / runs fine on .accdb (AC2007)

and the Strfile was not already open at that stage, in some lingering background Excel process perhaps ?
__________________

Don't tell us what did not happen.
Do you tell the mechanic about your car "it doesn't work" or vague stuff like:
I couldn't, No joy, To no avail, No go
, Incorrect, Wrong, Seems, Without success, It didn't let me, I/it failed.

Do tell us what did happen
:

I want this ... to happen, I attempted this ..., using this ... code and pressing these buttons, the system did this ... in response, and I got this ... error in this ... line.

Did it help? Pass it forward!

Joys of Access:
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.

spikepl is offline   Reply With Quote
Old 09-22-2014, 06:12 AM   #11
AOB
Newly Registered User
 
AOB's Avatar
 
Join Date: Sep 2012
Location: Dublin, Ireland
Posts: 551
Thanks: 132
Thanked 39 Times in 37 Posts
AOB is on a distinguished road
Re: Error when code runs in .accde / runs fine on .accdb (AC2007)

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)
__________________
There are 10 types of people in this world
Those who understand binary and those who don't
AOB is offline   Reply With Quote
Old 09-22-2014, 06:20 AM   #12
spikepl
Eledittingent Beliped
 
spikepl's Avatar
 
Join Date: Nov 2010
Location: San Serriffe
Posts: 6,144
Thanks: 77
Thanked 980 Times in 958 Posts
spikepl is a jewel in the rough spikepl is a jewel in the rough spikepl is a jewel in the rough
Re: Error when code runs in .accde / runs fine on .accdb (AC2007)

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.
__________________

Don't tell us what did not happen.
Do you tell the mechanic about your car "it doesn't work" or vague stuff like:
I couldn't, No joy, To no avail, No go
, Incorrect, Wrong, Seems, Without success, It didn't let me, I/it failed.

Do tell us what did happen
:

I want this ... to happen, I attempted this ..., using this ... code and pressing these buttons, the system did this ... in response, and I got this ... error in this ... line.

Did it help? Pass it forward!

Joys of Access:
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.

spikepl is offline   Reply With Quote
Old 09-22-2014, 06:32 AM   #13
AOB
Newly Registered User
 
AOB's Avatar
 
Join Date: Sep 2012
Location: Dublin, Ireland
Posts: 551
Thanks: 132
Thanked 39 Times in 37 Posts
AOB is on a distinguished road
Re: Error when code runs in .accde / runs fine on .accdb (AC2007)

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
....
' Ensure source file is present before proceeding
Set objFSO = CreateObject("Scripting.FileSystemObject")
Do Until objFSO.FileExists(strFile)
 DoEvents
Loop
....
Set appExcel = CreateObject("Excel.Application")
 
  With appExcel
 
    .DisplayAlerts = False
 
    Set wbkExcel = .Workbooks.Open(strFile)
__________________
There are 10 types of people in this world
Those who understand binary and those who don't
AOB is offline   Reply With Quote
Old 09-22-2014, 06:38 AM   #14
spikepl
Eledittingent Beliped
 
spikepl's Avatar
 
Join Date: Nov 2010
Location: San Serriffe
Posts: 6,144
Thanks: 77
Thanked 980 Times in 958 Posts
spikepl is a jewel in the rough spikepl is a jewel in the rough spikepl is a jewel in the rough
Re: Error when code runs in .accde / runs fine on .accdb (AC2007)

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.
__________________

Don't tell us what did not happen.
Do you tell the mechanic about your car "it doesn't work" or vague stuff like:
I couldn't, No joy, To no avail, No go
, Incorrect, Wrong, Seems, Without success, It didn't let me, I/it failed.

Do tell us what did happen
:

I want this ... to happen, I attempted this ..., using this ... code and pressing these buttons, the system did this ... in response, and I got this ... error in this ... line.

Did it help? Pass it forward!

Joys of Access:
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.

spikepl is offline   Reply With Quote
Old 09-22-2014, 07:19 AM   #15
AOB
Newly Registered User
 
AOB's Avatar
 
Join Date: Sep 2012
Location: Dublin, Ireland
Posts: 551
Thanks: 132
Thanked 39 Times in 37 Posts
AOB is on a distinguished road
Re: Error when code runs in .accde / runs fine on .accdb (AC2007)

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...)

__________________
There are 10 types of people in this world
Those who understand binary and those who don't
AOB is offline   Reply With Quote
Reply

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
[SOLVED] msg box that runs a code Clod_13 Modules & VBA 3 05-29-2014 03:05 AM
Query runs fine, but not in report... Starchildren3317 Reports 4 11-30-2010 11:42 AM
Program runs fine in Design View crashes in Access Run Time randommetalguy General 1 11-02-2009 11:40 AM
Query is too complex... On report, but query runs fine Canderel General 1 10-18-2005 04:30 PM
Query runs fine-Report takes forever ctaylor Reports 2 02-27-2001 10:35 AM




All times are GMT -8. The time now is 08:18 PM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Featured Forum post


Sponsored Links


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World