Working with Excel via Visual Studio 2008

Discussion in 'Microsoft Excel Programming' started by Kirk, Sep 24, 2008.

  1. Kirk

    Kirk Guest

    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!
     
    Kirk, Sep 24, 2008
    #1
    1. Advertisements

  2. Kirk

    Chip Pearson Guest

    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 <>
    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!
     
    Chip Pearson, Sep 24, 2008
    #2
    1. Advertisements

  3. Kirk

    Kirk Guest

    On Sep 24, 6:14 pm, Chip Pearson <> 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 <>
    > 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!
     
    Kirk, Sep 25, 2008
    #3
    1. Advertisements

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. Guest

    working with excel from visual studio 2005 express

    Guest, Nov 3, 2005, in forum: Microsoft Excel Programming
    Replies:
    0
    Views:
    192
    Guest
    Nov 3, 2005
  2. Kirk

    Working with Excel via Visual Studio 2008

    Kirk, Sep 24, 2008, in forum: Microsoft Excel Programming
    Replies:
    0
    Views:
    255
  3. Gord Dibben

    Re: Working example how to create a DLL using Visual C# 2008

    Gord Dibben, May 17, 2009, in forum: Microsoft Excel Programming
    Replies:
    8
    Views:
    799
    Patrick Molloy
    Jul 7, 2009
  4. Dougaj4

    Re: Working example how to create a DLL using Visual C# 2008

    Dougaj4, May 17, 2009, in forum: Microsoft Excel Programming
    Replies:
    0
    Views:
    234
    Dougaj4
    May 17, 2009
  5. Replies:
    9
    Views:
    1,624
    Chip Pearson
    May 22, 2009
Loading...

Share This Page