Object Library References Question

[mcambrose]

What do you do about macros that will run on machines with different versions
of something such as Excel. For instance I created a database macro on a
machine with Office 2007, so I set a reference to the Excel 12.0 library
using the Visual Basic editor. When I opened the file on an Office 2003
machine and tried to run the macro I had an error. I found that the Excel
11.0 library was the only one available. I removed the reference to Excel
12.0 library and selected the Excel 11.0. It then worked as planned. Is there
a way to change this object library refernce in code based on what Office
version is being used? I have an example of how to look through the
references collection, so I know I could change those items, but I wouldn't
necessarily know the exact path to use for the object library on a different
machine. Thanks for any insight provided. If this is too complex to give a
full explanation you might just tell me it can be done, but not worth the
trouble. THanks.

 

[Bob Phillips]

Use late bound code, don't set a reference and createobjects rather than new
them.

Show the code and we can probably convert it to late-bound for you.

 

[mcambrose]

Thanks Bob, I found some other posts about late binding with examples that
were helpful. I revised my code and late binding worked as you suggested.
However, I tried to use an exel worsheet function from access, but it would
not work using late binding, but it does work with the reference to excel
object library checked. Is this just a limitation that can't be circumvented
with late binding. The following snippet is from my code that works with the
reference checked. The last line shows the command to use the excel worksheet
function.

Public Function SetPageNum()
' sets the page number so the directory can be printed two persons to a page
' the first half of the records are numbered odd pages and the second half
if numbered even pages.
'THis will allow the printed pages to just be cut in half and the top stack
put
'on top of the bottom half to give a complete directory.
Dim xlApp As Excel.Application
Dim db As Database
Dim Rst As Recordset ' v.1: Rst as Table
Dim NumRecords As Long
Dim BM As String
Dim ReCount As Long 'use this variable to calculate page numbers

Set xlApp = New Excel.Application
Set db = CurrentDb() ' v.2: Set db = dbengine(0)(0)
Set Rst = db.OpenRecordset("Members- All Pages") ' v.1:
db.OpenTable("tblCustomers")
If Rst.RecordCount = 0 Then
Exit Function
End If

SetDefaultPrinter (EmailPDF_Printer)
' Show record count, and loop backwards through records.
Rst.MoveLast
NumRecords = Rst.RecordCount
ReCount = xlApp.Application.WorksheetFunction.RoundUp(NumRecords / 2, 0)

 

[Bob Phillips]

Does this not work?

Public Function SetPageNum()
' sets the page number so the directory can be printed two persons to a page
' the first half of the records are numbered odd pages and the second half
if numbered even pages.
'THis will allow the printed pages to just be cut in half and the top stack
put
'on top of the bottom half to give a complete directory.
Dim xlApp As Object
Dim db As Database
Dim Rst As Recordset ' v.1: Rst as Table
Dim NumRecords As Long
Dim BM As String
Dim ReCount As Long 'use this variable to calculate page numbers

Set xlApp = CreateObject("Excel.Application")
Set db = CurrentDb() ' v.2: Set db = dbengine(0)(0)
Set Rst = db.OpenRecordset("Members- All Pages") ' v.1:
db.OpenTable "tblCustomers"
If Rst.RecordCount = 0 Then
Exit Function
End If

'SetDefaultPrinter (EmailPDF_Printer)
' Show record count, and loop backwards through records.
Rst.MoveLast
NumRecords = Rst.RecordCount
ReCount = (NumRecords + 1) \ 2

BTW, what are you using Excel for here?

 

[mcambrose]

I use the roundup function. I am dividing the number of pages by two and then
rounding up. If I have 99 half pages then I am formatting 50 full size pages.
THanks.

 

[Bob Phillips]

So, does my offering work or not?