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