none
creating excel spreadsheet in vb RRS feed

  • Question

  • Is it possible to create an excel spreadsheet through code from within a VB application?  I'm not looking to display the spreadsheet in a form or anything, I just want to do things like create an excel spreadsheet on disk, open an existing spreadsheet, add a page to an existing spreadsheet, add a row to an existing spreadsheet, etc.
    Wednesday, January 7, 2009 3:49 AM

Answers

  • VBNuB said:

    Is it possible to create an excel spreadsheet through code from within a VB application?  I'm not looking to display the spreadsheet in a form or anything, I just want to do things like create an excel spreadsheet on disk, open an existing spreadsheet, add a page to an existing spreadsheet, add a row to an existing spreadsheet, etc.



    Yes, an Excel Workbook can be created w/o Excel. You can use a data access method. The caveat is that you have to add column names (first row) to the Worksheet, which of course would be expected when treating the Worksheet as a database table.

            Dim ConnectionString As String  
     
            'For versions prior to 2007  
            'ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" & _  
            ' "Data Source=C:\Test Files\ExcelWB.xls;Extended Properties=Excel 8.0"  
     
            'Excel 2007  
            ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Test Files\myExcel2007file.xlsx;Extended Properties=""Excel 12.0 Xml;HDR=Yes"""  
     
            Dim ExcelConnection As New System.Data.OleDb.OleDbConnection(ConnectionString)  
            ExcelConnection.Open()  
     
            Dim SQLDDLCommand As String = "CREATE TABLE tblCustomers " & _  
                                            "(CustomerID INTEGER, " & _  
                                            "[Last Name] TEXT(50), " & _  
                                            "[First Name] TEXT(50), " & _  
                                            "Phone TEXT(10), " & _  
                                            "Email TEXT(50))"  
     
            Dim ExcelCommand As New System.Data.OleDb.OleDbCommand(SQLDDLCommand, ExcelConnection)  
            ExcelCommand.ExecuteNonQuery()  
     
            ExcelConnection.Close() 


    Paul ~~~~ Microsoft MVP (Visual Basic)
    • Marked as answer by VBNuB Monday, January 12, 2009 2:57 AM
    Thursday, January 8, 2009 1:08 PM

All replies

  • If you have Office, you can add a reference to the Microsoft Office Object Model and then to the Microsoft Excel Object Model to a Forms Application and manipulate Excel programmatically, that way.  MSDN (and a lot of 3rd party sources) has just about everything you could imagine for Office Automation documented, so you shouldn't have much trouble getting started.

    I don't know of a free, DotNET Native (or even accessible) tool for creating and manipulating any Office Document without Office Automation.

    Wednesday, January 7, 2009 5:18 AM
  • 1. Perhaps this is a stupid question, but is Excel required to build the application, or to both build and run it?  Keep in mind that I don't actually want to view the spreadsheet from my application, I just want to create one and write data to it (i.e. create / open / read / write to a spreadsheet file on disk, but never actually open the spreadsheet in Excel from my application).

    2. All of the examples I can find online using the office automation seem to involve macros.   Is it true that I need to use macros to make changes to a spreadsheet (e.g. add a row) or is there an api that lets me operate on these objects in vb?  The first time my application runs I was hoping not to actually have an Excel file on disk (the app would create it) so storing macros w/ the spreadsheet won't work.
    Thursday, January 8, 2009 2:29 AM
  • The API is the MS Office Object Model, including Extensibility and Interop DLL files.  You add references to these Interops in Visual Studio, then your standalone Application (or Addin) can call on those Object Models the same way VBA Macros do natively - without VBA.

    Without extensive experience with the COM Interop or a 3rd party SDK, it's safe to say that Excel is required for you to build with and required for your user to run your software.  Also with this method you must at some point start an Excel Application, although you can do so hidden, in the background, and the user will never know.
    Thursday, January 8, 2009 3:05 AM
  • Thanks.

    Given that it appears I have to add these references to particular versions of Excel, is it safe to assume that the user of my application would have to have the same version of Excel as I do as well.  For example, if I built my app against Excel 2003 is it safe to assume that they couldn't run the application, which is trying to create a 2003 spreadsheet, w/ a 2007 version of Excel?  Sounds like I would need to build different versions of the application was different versions of Excel.  Is that right?


    Thursday, January 8, 2009 3:28 AM
  • VBNuB said:

    Thanks.

    Given that it appears I have to add these references to particular versions of Excel, is it safe to assume that the user of my application would have to have the same version of Excel as I do as well.  For example, if I built my app against Excel 2003 is it safe to assume that they couldn't run the application, which is trying to create a 2003 spreadsheet, w/ a 2007 version of Excel?  Sounds like I would need to build different versions of the application was different versions of Excel.  Is that right?




    Depends.  Office Object Model 9 (Office 2000) is the earliest model you can work with this way.  If you do your homework you can stick to procedures and calls that haven't changed... same might happen if you just plain get lucky.

    Every OOM version (and every individual application interop version) has its own quirks, but there is such a thing as an Addin developed for Office 2000 (OOM 9) that's compatible with all newer versions.  MS did this deliberately (partly) to provide backward compatibility with user-scripts and macros in VBA.  That said, Office 2007 departs abruptly and drastically.  Your simplest VBA Macros are likely to perform as expected, but actual scripts/apps in VBA have been known to puke like a drunken college student.

    A 3rd Party SDK will help you get around this by providing a conglomerated interop model - Google "Add-In Express" (with the quotes in the search line) and hit the "I'm Feeling Lucky" button.  Mind you this is a tool for creating Addins in Office, or creating your own application with Office as the platform.  Not an option for writing direct to Excel file format from a standalone app.
    Thursday, January 8, 2009 4:34 AM
  • i would recommend you look at SpreadsheetGear for .Net.  it can be embedded in your application and does not require excel to be on the developer or user computer.  it is a great tool for background use of a spreadsheet.  much faster and is not automation like controlling the excel process.  this is a tool with advanced features.  very good, i use it for many things.  there is a free version in the registration benefits for the express editions of vb.  worth taking a look.  it will eliminate a lot of what you are asking about.  i have code samples for it on my website and there are many in the express forum also. 

    Jeff - www.srsoft.us
    FREE DEVELOPER TOOLS, CODE & PROJECTS at www.srsoft.us
    Thursday, January 8, 2009 12:52 PM
  • VBNuB said:

    Is it possible to create an excel spreadsheet through code from within a VB application?  I'm not looking to display the spreadsheet in a form or anything, I just want to do things like create an excel spreadsheet on disk, open an existing spreadsheet, add a page to an existing spreadsheet, add a row to an existing spreadsheet, etc.



    Yes, an Excel Workbook can be created w/o Excel. You can use a data access method. The caveat is that you have to add column names (first row) to the Worksheet, which of course would be expected when treating the Worksheet as a database table.

            Dim ConnectionString As String  
     
            'For versions prior to 2007  
            'ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" & _  
            ' "Data Source=C:\Test Files\ExcelWB.xls;Extended Properties=Excel 8.0"  
     
            'Excel 2007  
            ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Test Files\myExcel2007file.xlsx;Extended Properties=""Excel 12.0 Xml;HDR=Yes"""  
     
            Dim ExcelConnection As New System.Data.OleDb.OleDbConnection(ConnectionString)  
            ExcelConnection.Open()  
     
            Dim SQLDDLCommand As String = "CREATE TABLE tblCustomers " & _  
                                            "(CustomerID INTEGER, " & _  
                                            "[Last Name] TEXT(50), " & _  
                                            "[First Name] TEXT(50), " & _  
                                            "Phone TEXT(10), " & _  
                                            "Email TEXT(50))"  
     
            Dim ExcelCommand As New System.Data.OleDb.OleDbCommand(SQLDDLCommand, ExcelConnection)  
            ExcelCommand.ExecuteNonQuery()  
     
            ExcelConnection.Close() 


    Paul ~~~~ Microsoft MVP (Visual Basic)
    • Marked as answer by VBNuB Monday, January 12, 2009 2:57 AM
    Thursday, January 8, 2009 1:08 PM
  • Does the same thing work for any version of Excel format based on CFB?

    Thursday, January 8, 2009 5:09 PM
  • What is CFB?
    Paul ~~~~ Microsoft MVP (Visual Basic)
    Thursday, January 8, 2009 5:24 PM
  • CFB is the base spec for the binary-on-disk file format of any document created by any version of Excel earlier than 2007.  In looking at your code a second time I see you do have a prior to 2007 connection string.

    Does this method support additional Excel File Features like reading/manipulating formulas and functions or graphic charts?

    Thursday, January 8, 2009 5:33 PM
  • I believe that the code would support any 32-bit version. I don't have a 16-bit version of Excel so I can't confirm this.

    Excel features would not be supported by the Excel ISAM database driver.
    Paul ~~~~ Microsoft MVP (Visual Basic)
    Thursday, January 8, 2009 7:17 PM