B
Bud Dean
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
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