Modifying The Current Worksheet in Excel (using vb6) 1

[Adamba]

Hey all,

I apologise if this question has already be asked.

Im creating a pseudo add-in for excel which looks at the currently opened workbook and then pulls out most of the details such as sheet names, data ranges, headings, ...

On my hunt for code to interact with excel i have found the following different two lines (ive ignored all the other refrencing code from this):

1)
[blue]Set[/blue] objExcel = GetObject(, "Excel.Application")
2)
[blue]Dim[/blue] objExcel [blue]As[/blue] New Excel.application

Number 2 seems to (as it says) create a new instance of excel so ive pretty much ruled this one out.

Number 1 seems to be roughly what i want but does anyone know how to interact with excel once the 'connecting' code has been run?

Below is the code i have been trying to run but only to result in getting error no '91' Object variable or With block variable not set.

Any ideas how i can get this to work?

Many Regards,
Adam Bates

[blue]Dim[/blue] objExcel [blue]As[/blue] Excel.application
[blue]Set[/blue] objExcel = GetObject(, "Excel.Application")
Msgbox objExcel.ActiveWorkbook.Name

 

[dwlerwill]

This should do it

Option Explicit
'reference Microsoft Excel -Version Number- Object Library
Private Sub Command1_Click()
'in a module place
'Public tmpExcel As excel.Application

Set tmpExcel = New Excel.Application

tmpExcel.Workbooks.Open ("c:\test.xls")

tmpExcel.Visible = False

MsgBox tmpExcel.ActiveWorkbook.Name

End Sub


David Lerwill
"If at first you don't succeed go to the pub"

 

[Adamba]

dwlerwill,

Thanks for your quick response,

What i was after was not having to 'open' or create a 'new' instance of excel but to look at the one that is already open.

Intrestingly i compiled my program and run it on a pc with Excel 97 (SR-2) and it works fine but on my 'programming' pc with Excel 2002 (SP3) it gives me a error '91'.

The references i have added are for Excel 10.0, Graph 10.0, and Office 10.0 which has now stumped me as i believed Excel 97 was version 8.0 (hope that makes sense)

Any ideas?




Adam

 

[dwlerwill]

I have just tried this code

Private Sub Command1_Click()

Dim objExcel As Excel.Application
Set objExcel = GetObject(, "Excel.Application")
MsgBox objExcel.ActiveWorkbook.Name

End Sub

with only Excel 10.0 referenced and it works find. I have VB 6.0 and Excel Xp maybe it is specific to Excel 2002 and you need something different?

David Lerwill
"If at first you don't succeed go to the pub"

 

[Adamba]

dwlerwill,

Many Thanks, it appears that i can run it on various pc's with different versions of excel ,except my vb computer, so ill stick with the code and have to debug on those pcs then come back and figure out the problem before shipping.

Think ill go to the pub now and down my sorrows.




Adam