Hi Bud,

When using late binding, you must define all library-related constants. Or
you can use the actual values. Since Access doesn't have any idea what
xlToRight is, you'll either get a syntax error (with Option Explicit) or
unexpected results (as xlToRight will evaluate to 0).

To get the values of the built-in constants, you can go to the immediate
window in Excel and type them in with a "?" in front:

?xlToRight
-4161

So, at the top of your VBA module in Access, you could do this:

Private Const xlToRight As Long = -4161

Or you could simply use -4161 instead of xlToRight in your code.

The same would apply to xlSum and other Excel constants.

--
Regards,

Jake Marx
MS MVP - Excel
www.longhead.com

[please keep replies in the newsgroup - email address unmonitored]


Bud Dean wrote:
Hi folks,

I have been struggling with this for days. We have a real hodgepodge
of Office Installations, from 97 to 2003. Many of these are installed
mixed as well. For example, we have might Office 2k small business
installed with either access 97, 2k or 2002. The combinations are
many.

Many of our access applications either import or export (or both)
data to and from Excel. I am trying to use late binding for this.
All has worked well until a couple of days ago.

Here's the requirement:

Open a workbook
Delete the last column of data
Format the first row of type in BOLD
AutoFit the columns
Add totals on a range of columns grouped on the first column

When referencing excel I have no problem. However, I just can't seem
to figure it out using late binding

I have looked through all my old books, searched google, msdn,
mvps.org. I am simply stumped.

The following code works when a reference is added...

I have added some inline comments with some of the errors.

*******************Start*****************

Sub DelXLIDColumn(BookName As String, shtname As String)
Dim objXL As Object
Dim boolXL As Boolean
Dim objActiveWkb As Object
Dim objSHT As Object
DoCmd.Hourglass True
If fIsAppRunning("Excel") Then
Set objXL = GetObject(, "Excel.Application")
boolXL = False
Else
Set objXL = CreateObject("Excel.Application")
boolXL = True
End If

objXL.Application.workbooks.Open (BookName)
Set objActiveWkb = objXL.Application.ActiveWorkBook
'objXL.Visible = True
'Set objSHT = objActiveWkb.Worksheets(1)

With objActiveWkb

With objActiveWkb.Worksheets(1)

.Range("A1").Select
-----------------
Selection.End(xlToRight).Select

the above gives the following errors:

variable not defined (xlToRight)
trying to fully qualify with objXL.xlToRight or objSht.xlToRight
returns object doesn't support this property or method
"Selection" doesn't seem to be a supported object method as well

--------------------------------------------------------------

Selection.EntireColumn.Delete
Selection.Subtotal GroupBy:=1, Function:=xlSum,
TotalList:=Array(9, 10, 11), _
Replace:=True, PageBreaks:=False, SummaryBelowData:=False
.Name = shtname
.Columns("A:AA").EntireColumn.AutoFit
.Range("A1:AA1").Select
Selection.Font.Bold = True
.Range("A1").Select
End With
End With

objActiveWkb.Close savechanges:=True

If boolXL Then objXL.Application.Quit

Set objActiveWkb = Nothing: Set objXL = Nothing

DoCmd.Hourglass False
End Sub


*********************End Code*****************

Thanks in advance for any help and guidance...

Bud Dean