VB.NET

[Mitch]

From reading other threads here I get the impression that a fair few people have experience of computer languages. I'm having a bash at VB using Visual Studio Express and could use some help if possible.

I have some info from another website that I'd like to put into my Excel sheet that I use with BA. I've googled, searched, and asked on another forum without success about how to automate an existing workbook.
I've managed to create a new instance of Excel with a new workbook and use that, but can't find out how to use a workbook that's already open.

Any suggestions?

[PeteB]

If you have create an instance of Excel and a new workbook, you are mostly there

Is the workbook already open in Excel, or will you need to search for it on the drive?

If it is open, the Excel.Application object should have a property Workbooks, and this should have a property / method like item or GetItem() - have a look at the help for that - you should be able to pass the name of the workbook in as a String.
If you are opening it from the drive it will be a bit more fiddly - can't remember how just now...

[GaryRussell]

The code I use in BA to automate Excel uses the following function to get an existing instance of Excel.

xl = GetObject(, "Excel.Application")
wb = xl.Workbooks(1) 'first workbook
ws = wb.Worksheets(1) 'first worksheet in workbook 1

Note that the workbooks and worksheets start at index 1, not 0

[Mitch]

If you have create an instance of Excel and a new workbook, you are mostly there

Is the workbook already open in Excel, or will you need to search for it on the drive?

If it is open, the Excel.Application object should have a property Workbooks, and this should have a property / method like item or GetItem() - have a look at the help for that - you should be able to pass the name of the workbook in as a String.
If you are opening it from the drive it will be a bit more fiddly - can't remember how just now...

The workbook is already open in Excel, and has BA linked to it. I want to put the info I've got alongside the info BA sends to it.
I'll have a search for GetItem(). Thanks Pete.

[Mitch]

The code I use in BA to automate Excel uses the following function to get an existing instance of Excel.

xl = GetObject(, "Excel.Application")
wb = xl.Workbooks(1) 'first workbook
ws = wb.Worksheets(1) 'first worksheet in workbook 1

Note that the workbooks and worksheets start at index 1, not 0

I did try replacing CreateObject in the code I had working with GetObject, but it didn't work at the time so I guess I did something else wrong. I'll have another play with it.
Thanks for the hint Gary.