Object Library References Question
 

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.

Object Library References Question
 

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.

--
__________________________________
HTH

Bob

"mcambrose" wrote in message
...
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.

Object Library References Question
 

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(NumRec ords / 2, 0)
"Bob Phillips" wrote:

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.

--
__________________________________
HTH

Bob

"mcambrose" wrote in message
...
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.

Object Library References Question
 

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?

--
__________________________________
HTH

Bob

"mcambrose" wrote in message
...
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(NumRec ords / 2, 0)
"Bob Phillips" wrote:

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.

--
__________________________________
HTH

Bob

"mcambrose" wrote in message
...
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.

Object Library References Question
 

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" wrote:

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?

--
__________________________________
HTH

Bob

"mcambrose" wrote in message
...
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(NumRec ords / 2, 0)
"Bob Phillips" wrote:

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.

--
__________________________________
HTH

Bob

"mcambrose" wrote in message
...
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.

Object Library References Question
 

So, does my offering work or not?

--
__________________________________
HTH

Bob

"mcambrose" wrote in message
...
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" wrote:

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?

--
__________________________________
HTH

Bob

"mcambrose" wrote in message
...
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(NumRec ords / 2,
0)
"Bob Phillips" wrote:

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.

--
__________________________________
HTH

Bob

"mcambrose" wrote in message
...
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.