Refering to Active Wbk from VB6

[avi]

Hello,

I have found this code in a old posting that seems the right one to
get the a reference fron VB6 to the active Excel workbook. When
running it, i get "Sub or function not defined" that seems to refer to
CType . It could be that the code is written in VB.net. Could you help
with the right version for VB6 (if this is the problem)?

Sub Comm1()

Dim xlApp As Excel.Application
Dim xlBook As Excel.Workbook
Dim xlSheet As Excel.Worksheet
Dim myBookName As String

xlApp = CType((GetObject(, "Excel.Application")),
Excel.Application)
xlBook = CType(xlApp.ActiveWorkbook, Excel.Workbook)
myBookName = xlBook.Name
MsgBox (myBookName)

End Sub

 

[Peter T]

I notice Jim Crone suggested what you should look into in your slightly
early post -
"GetObject" is the magic word...

In help or with a little research you might have been able to come up with
something like this -

Dim xlApp As Object ' Late binding, or As Excel.Application with Early
Dim oWB As Object ' or Excel.Workbook with Early binding

On Error Resume Next

Set xlApp = GetObject(, "Excel.Application")
' the first randomly found instance if multiple instances

If Not xlApp Is Nothing Then
Set oWB = xlApp.ActiveWorkbook
If Not oWB Is Nothing Then
MsgBox "ActiveWorkbook.Name = " & oWB.Name
Else
MsgBox "An Excel instance found but no Activeworkbook"
End If
Else
MsgBox "No running Excel instance found"
End If

Regards,
Peter T

 

[avi]

Thanks a lot. Works!!!!!!!!!

Avi