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

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

just hard code the value of the constant:

-4161

--
Regards,
Tom Ogilvy


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

Jake,

Thanks so much for the quick response. Honestly, I'm not sure I would have
ever thought about using the constants.

I cannot tell you how much I appreciate the help.

Thanks again,

Bud Dean
"Jake Marx" wrote in message
...
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

Tom,

I don't know if you recall or not, but a few years ago (1999 in fact), you
were a big help to me on a Y2K project I was working on. I still use many
of things you taught me then.

I know I'm not the first and certainly won't be last, but I want to thank
you again for the help and guidance you provide to not only me, but all the
other people who post.

Just finished up with several db and vb apps. If you're ever in Irvine,
CA., please let me know. I owe you dinner...

Thanks again,

Bud Dean
"Tom Ogilvy" wrote in message
...
just hard code the value of the constant:

-4161

--
Regards,
Tom Ogilvy


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

Thanks.

The name sounds familiar, but I can't say I remember particulars. I don't
think you wll see me in Irvine. That is a long way from Virginia. <g

--
Regards,
Tom Ogilvy

"Bud Dean" wrote in message
...
Tom,

I don't know if you recall or not, but a few years ago (1999 in fact), you
were a big help to me on a Y2K project I was working on. I still use many
of things you taught me then.

I know I'm not the first and certainly won't be last, but I want to thank
you again for the help and guidance you provide to not only me, but all
the
other people who post.

Just finished up with several db and vb apps. If you're ever in Irvine,
CA., please let me know. I owe you dinner...

Thanks again,

Bud Dean
"Tom Ogilvy" wrote in message
...
just hard code the value of the constant:

-4161

--
Regards,
Tom Ogilvy


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