Newbie question, Excel and Visual Basic.Net

[Guest]

Hi,

I am trying to learn VB.Net and I am now going insane trying to get the active Excel document. The only thing I really need is to get the active document and read/set some of its properties. I have unsuccessfully tried numeros ways, including something like:

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

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

What am I doing wrong, and what must I do to get the active workbook and things like its name?

TIA

/ Richard

 

[Rob Bovey]

Hi Richard,

When you create an instance of Excel via Automation it does not have any
workbooks open like it does when you open it manually. I've modified your
code to add a new workbook. See if you don't have better luck with it.

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

xlApp = New Excel.Application
xlBook = xlApp.Workbooks.Add()
myBookName = xlBook.Name

MessageBox.Show(myBookName)

xlBook.Close(False)
xlApp.Quit()

--
Rob Bovey, MCSE, MCSD, Excel MVP
Application Professionals
http://www.appspro.com/

* Please post all replies to this newsgroup *
* I delete all unsolicited e-mail responses *

Hi,

I am trying to learn VB.Net and I am now going insane trying to get the

active Excel document. The only thing I really need is to get the active
document and read/set some of its properties. I have unsuccessfully tried
numeros ways, including something like:

 

[Bob Phillips]

Richard,

CreateObject creates a new instance of Excel, and as Rob says there is no
automatic new workbook created at that time when done via automation.If you
are trying to get at an already active workbook, then that suggests that you
already have an Excel instance running, which is why GetObject is then
appropriate. This is the sort of code that works

Set xlApp = GetObject(, "Excel.Application")
Set xlBook = xlApp.ActiveWorkbook
myBookName = xlBook.Name

But are you running this from within Excel? If so , you don't need
automation, as the Excel application is exposed via the application object.
So you can simply do

myBookName = Activeworkbook.Name

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

Rob,

Thanks for giving me a helping hand. Unfortunately it really isn't what I

am looking for. I did manage to create and refer to a new workbook, but I
really need to get the active workbook, which has been manually opened.

I did also got a suggestion elsewhere to exchange "CreateObject" with

"GetObject", but unfortunately that adwise results in an error saying an
Active X-component couldn't be made.

 

[Bob Phillips]

Richard,

Sorry, missed the bit about VB Net in my response, so some of it won't
apply. I don't use VB Net so I cannot answer the question , but I got the
same error if I tried

Set xlApp = GetObject("Excel.Application")

which was corrected with

Set xlApp = GetObject(, "Excel.Application")

I hope this can give you a lead.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[Guest]

Thank you Bob

Unfortunately it is still no go, when I try your method it creates a new instance of Excel, despite using the GetObject mehtod, So any additional things you can think of are welcom

As for using VB.Net, instead of VBA, my original intent was that it would be easier for what is supposed to be a cross application program (Adobe InDesign+Excel). But having spent several days just trying to get the Excel objects I want I am now beginning to feel a bit disappointed. All thick books, help files, Google searches etc. has not helped me to get any closer :-(

 

[Rob Bovey]

Hi Richard,

I've never had any luck making GetObject work correctly with Excel in
VB.NET, even with the correct primary interop assemblies installed for
Office XP (the version of Office loaded on my VS.NET machine). I can't swear
it's a bug, but it looks like one to me. You might try one of the VB.NET
newsgroups and see if someone there knows for sure.

--
Rob Bovey, MCSE, MCSD, Excel MVP
Application Professionals
http://www.appspro.com/

* Please post all replies to this newsgroup *
* I delete all unsolicited e-mail responses *

Rob,

Thanks for giving me a helping hand. Unfortunately it really isn't what I

am looking for. I did manage to create and refer to a new workbook, but I
really need to get the active workbook, which has been manually opened.

I did also got a suggestion elsewhere to exchange "CreateObject" with

"GetObject", but unfortunately that adwise results in an error saying an
Active X-component couldn't be made.

 

[Guest]

Haha, Victory at last

Dim xlApp As Excel.Applicatio
Dim xlBook As Excel.Workboo
REM Dim xlSheet As Excel.Workshee
Dim myBookName As Strin

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