PC Review


Reply
Thread Tools Rating: Thread Rating: 3 votes, 4.67 average.

Working with Excel via Visual Studio 2008

 
 
Kirk
Guest
Posts: n/a
 
      24th Sep 2008
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!
 
Reply With Quote
 
 
 
 
Chip Pearson
Guest
Posts: n/a
 
      24th Sep 2008
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)


On Wed, 24 Sep 2008 11:16:44 -0700 (PDT), Kirk <(E-Mail Removed)>
wrote:

>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!

 
Reply With Quote
 
 
 
 
Kirk
Guest
Posts: n/a
 
      25th Sep 2008
On Sep 24, 6:14*pm, Chip Pearson <(E-Mail Removed)> wrote:
> 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)
>
> On Wed, 24 Sep 2008 11:16:44 -0700 (PDT), Kirk <(E-Mail Removed)>
> wrote:
>
>
>
> >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!- Hide quoted text -

>
> - 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!
 
Reply With Quote
 
 
 
Reply

Thread Tools
Rate This Thread
Rate This Thread:

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
Enabling browser back button for GridView Paging and Sorting in Ajax1.1 and 3.5 (using Visual Studio 2005/ Visual studio 2008) anil reddy Microsoft C# .NET 0 11th Feb 2009 05:22 PM
Enabling browser back button for GridView Paging and Sorting in Ajax1.1 and 3.5 (using Visual Studio 2005/ Visual studio 2008) anil reddy Microsoft ASP .NET 0 11th Feb 2009 05:22 PM
Visual Web Express 2008 X Visual Studio Pro 2008 HRsoft Informática Microsoft ASP .NET 3 9th Jun 2008 03:07 PM
Upgrading a visual studio 2005 solution to a visual studio 2008 solution Andy B Microsoft C# .NET 3 19th Apr 2008 04:50 PM
Visual Studio 2005 Beta 1 Refresh vs Visual Studio 2005 CTP vs Visual C++ 2005 Express Beta1 Peter Nimmo Microsoft VC .NET 1 16th Dec 2004 04:30 PM


Features
 

Advertising
 

Newsgroups
 


All times are GMT +1. The time now is 02:26 PM.