Excel late binding help, please

[Bud Dean]

Here's the issue:

We multiple versions office, mixed versions of office. We have everything
from 97 to 2003. Some machines with one version of office and a different
version of Access. Many of the Access Db's we do genereally have to import
data or export data to
excel or both.

I have been successful with everything so far. I have run into a couple
issues I can't seem to figure out.

The following code does the following:

Deletes the las column
Highlights column headings (row1)
Adds totals

I have looked in all my old books, searched google, msdnfor the better part
of yesterday into this morning...I just can't seem to figure out how to make
this wok using late binding...sure would appreciate any help and guidance...

*************Code start***************

Sub DelXLIDColumn(BookName As String, shtname As String)

Dim objXL As Object

Dim strWhat As String, 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 = objSHT.Worksheets(1)

With objActiveWkb

With objActiveWkb.Worksheets(1)

.Range("A1").Select

Selection.End(xlToRight).Select

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,

Bud Dean

[Ken Snell [MVP]]

What isn't working with this code? Are you getting error message; if yes,
which one and which line?

Your code on first readthrough appears valid.

--

Ken Snell
<MS ACCESS MVP>

"Bud Dean" <bud_deanR...@hotmail.com> wrote in message
news:u6OfcDVo...@tk2msftngp13.phx.gbl...

[Jamie Collins]

"Bud Dean" <bud_deanR...@hotmail.com> wrote ...

> of I just can't seem to figure out how to make

> this wok using late binding

I noticed the following Excel constants in your code:

xlToRight
xlSum

Unless you have re-defined them locally, these may be causing problems.

Jamie.

--

[Ken Snell [MVP]]

Ah, good catch, Jamie!

--

Ken Snell
<MS ACCESS MVP>

"Jamie Collins" <jamiec...@xsmail.com> wrote in message
news:2ed66b75.04092...@posting.google.com...

[mflorezm]

Hello Guys:

Does anyone know How to create an Excel event handler while using late
binding?

I had to use late binding because my application must run across multiple
MS-Office versions, but I need to catch WorkbookBeforeClose Event of the
application object.

I tried both: delegates and WithEvents keyword like I found in Microsoft
documents, but it doesn't work because those methods use early binding.

http://support.microsoft.com/default.aspx?scid=kb;en-us;249843
http://support.microsoft.com/default.aspx?scid=kb;en-us;247579
http://support.microsoft.com/default.aspx?scid=kb;en-us;244167

Thanks

--
MFM

[Jamie Collins]

"mflorezm" <mflo...@discussions.microsoft.com> wrote ...

> Does anyone know How to create an Excel event handler while using late
> binding?
>
> I had to use late binding because my application must run across multiple
> MS-Office versions, but I need to catch WorkbookBeforeClose Event of the
> application object.

Which is the *earliest* version of MS Office that you have to support?
If it is Office2000, use early binding on this version. Because of the
way MS Office type libraries are coded, if the client has a later
version of Office the references may be reassigned accordingly. See
this thread for a confirmation:

http://groups.google.com/groups?threadm=u0Lh4zBQEHA.1160%40TK2MSFTNGP09.phx.gbl

If you have code that is specific to a later version of Office, to
avoid compile errors you will need to put it in a separate module to
your main code and call the methods in the separate module using
conditional compilation.

Jamie.

--

[mflorezm]

Thanks Jamie:

Yes, I had to install office 2000, change the reference to library version
9.0 and recompile the complete project.

That was the only solution.

Thanks again.

MFM

[Rick Roberts]

My app is using MS Office Word, Excel and Outlook and my goal is to be
compatible with Office 2000, Office XP, Office 2003 and upward.

If I make my reference to the Office 2000 libraries and use early binding.
I have tested this on all 3 platforms (independently) and it seems to work
flawlessly.

Obviously I cannot make any reference to anything new to XP or 2003 but
what else am I missing?

[onedaywhen]

Rick Roberts wrote:
> Obviously I cannot make any reference to anything new to XP or 2003

That's not correct. You can use conditional compiliation for Excel97
using the VBA6 compile constant. You can put the new features into
separate modules and call them conditionally; the code will not blow up
in the older versions unless you force compiliation e.g. from the VBE
or by calling code in the version-specific modules. This may give you
some clues for further research:

Private Sub Workbook_Open()
#If VBA6 Then
If Application.Version < 10 Then
modExcel2K.ShowMessage
Else
modExcelXP.ShowMessage
End If
#Else
modExcel97.ShowMessage
#End If
End Sub

Jamie.

--