Working with Excel via Visual Studio 2008

[Kirk]

Hello,

I have been programming for some time in VB & VBA, but this is my
first attempt to program in Visual Studio 2008 for Excel. Basically,
I am looking for a good place to start for my application.

I have a customer that uses a custom software package (not mine) that
kicks out around 12 reports as Excel 2003 documents. The customer
then imports data from specific cell ranges in these 12 documents to a
"Master" Excel file. Within this Master file, he does calculations
and generates graphs & charts. As a final step, he exports the graphs
generated to PowerPoint presentations. I realize this is convulted,
but I wanted to explain the existing scenario, as this is what I am
trying to automate.

What would be my best method for creating a VB application that pulled
data from specific locations (cell ranges in the 12 report documents)
and put them into the Master Excel file? I know that VS2008 has some
templates and support for Excel, but I am having a difficult time
getting started.

I have attempted to do some research myself, but most of the examples
I have found relate to manipulating data in a singe Excel document
(not across multiple files). I would greatly appreciate any
suggestions or references to sites where I could get more information.

Thank you!

 

[Chip Pearson]

You have two choices: 1) a COM Add-In that would open all the
workbooks and consolidate them into a single master fie, or 2) a
standalone exe program that starts the Excel application (perhaps
never making it visible to the user) that opens the workbooks and
consolidates the results. Once you become accustomed to the new
language syntax (I assume you are using VB.NET), the actual code will
be very similar to VBA/VB6.

If you decide to go down the COM Add-In route, I would very strongly
recommend that you get Add-In Express For NET (www.add-in-express.com)
which greatly simplifies creating the add-in by automatically
generating all the plumbing code that you need to make a non-managed
COM application like Excel work with the managed NET framework.

If you go down the standalone exe project, each user will have to have
Excel on their machines. You set a reference (Project menu, Add
Reference, COM, Excel 11), create a New Excel.Application object and
use that to reference anything in Excel. The actual work of the
program, the consolidation, will be nearly identical in VB.NET as it
would be in VBA or VB6. Really, all you have to do is prefix things
that are globally scoped in VBA (e.g., ActiveWorkbook) with the
variable that contains the reference to he application:

Dim XlApp As Excel.Application
XLApp = New Excel.Application
Debug.Print XlApp.ActiveWorkbook.Name
' rather than
Debug.Print ActiveWorkbook.Name.

If you are proficient at VB6, you will find it relatively easy to
learn VB.NET. This biggest hurdle in learning all the NET Base
Classes.

Cordially,
Chip Pearson
Microsoft MVP
Excel Product Group
Pearson Software Consulting, LLC
www.cpearson.com
The San Diego Project Group, LLC
(email is on the web site)
USA Central Daylight Time (-5:00 GMT)

 

[Kirk]

You have two choices: 1) a COM Add-In that would open all the
workbooks and consolidate them into a single master fie, or 2) a
standalone exe program that starts the Excel application (perhaps
never making it visible to the user) that opens the workbooks and
consolidates the results. Once you become accustomed to the new
language syntax (I assume you are using VB.NET), the actual code will
be very similar to VBA/VB6.

If you decide to go down the COM Add-In route, I would very strongly
recommend that you get Add-In Express For NET (www.add-in-express.com)
which greatly simplifies creating the add-in by automatically
generating all the plumbing code that you need to make a non-managed
COM application like Excel work with the managed NET framework.

If you go down the standalone exe project, each user will have to have
Excel on their machines. You set a reference (Project menu, Add
Reference, COM, Excel 11), create a New Excel.Application object and
use that to reference anything in Excel. The actual work of the
program, the consolidation, will be nearly identical in VB.NET as it
would be in VBA or VB6. Really, all you have to do is prefix things
that are globally scoped in VBA (e.g., ActiveWorkbook) with the
variable that contains the reference to he application:

Dim XlApp As Excel.Application
XLApp = New Excel.Application
Debug.Print XlApp.ActiveWorkbook.Name
' rather than
Debug.Print ActiveWorkbook.Name.

If you are proficient at VB6, you will find it relatively easy to
learn VB.NET. This biggest hurdle in learning all the NET Base
Classes.

Cordially,
Chip Pearson
Microsoft MVP
    Excel Product Group
Pearson Software Consulting, LLCwww.cpearson.com
The San Diego Project Group, LLC
(email is on the web site)
USA Central Daylight Time (-5:00 GMT)








- Show quoted text -

Chip,

Thank you very much for your detailed response! That is exactly what
I was looking for. Sometimes when you are starting something new, you
need a "keyword" or concept to really get your research started in the
right direction. The second option seems more ideal for my scenario
(as all of the users have Excel installed anyway), so I will explore
that further.

Thank you again!