Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Working with Excel via Visual Studio 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! |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Working with Excel via Visual Studio 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 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! |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Working with Excel via Visual Studio 2008
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! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Is Visual Basic the same as Visual Studio 2008? | Excel Worksheet Functions | |||
working with excel from visual studio 2005 express | Excel Programming | |||
changing the visual basic in office 2003 to visual studio net | Excel Discussion (Misc queries) | |||
excel object in visual studio | Excel Programming | |||
Visual Studio .Net C# and Excel | Excel Programming |