[RESOLVED] Modifying Excel workbooks, any good code available?

[FunkMonkey]

I am looking for some code that can allow me to manipulate an Excel book the way you can in VBA. I know the most common answer will be write it, but if there is some code available this would reduce the time needed for the VBA -> VB net conversion.

Anyone know where there may be a class or module that I can get for manipulating Excel from VB? I'm going to start trying to create a class, but as I mentioned it will be much easier not re-creating the wheel.

Thanks!

EDIT:
OK, starting out of the gate at not a good rate.

I generated the code below from some examples I could find, but mine just pukes and won't accept the same setup found on the web. Anyone see what is wrong? I'm sure there is some code that was in the example that was not shown, would be my luck.

Option Explicit On

Imports Excel = Microsoft.Office.Interop.Excel
Imports Microsoft.Office.Interop.Excel.Application
Imports Workbook = Microsoft.Office.Interop.Excel.Workbook  ,_ I added this.
Imports Worksheet = Microsoft.Office.Interop.Excel.Worksheet ,_ I added this.
Imports System.IO





Public Class ClsExcel

    Private m_szWBPath As String
    Private xlApp As Excel.Application <-Does not like

    'Dim xlWorkBook As Microsoft.Office.Interop.Excel.Workbook
    'Dim xlWorkSheet As Microsoft.Office.Interop.Excel.Worksheet

    Public Function CreateExcelObject(ByVal szFileName As String) As Object

        m_szWBPath = Directory.GetCurrentDirectory()

        xlApp = New Excel.Application <- Does not like

what seems to be the issue?

[wes4dbt]

What is it you want too do? There are lots of examples of working with Excel in the code bank forum and you can find plenty using Google. But it's hard to give you an accurate answer without more information.

Also, saying "Does not like this" isn't very helpful. What is the exact error?

[FunkMonkey]

What is it you want too do? There are lots of examples of working with Excel in the code bank forum and you can find plenty using Google. But it's hard to give you an accurate answer without more information.

Also, saying "Does not like this" isn't very helpful. What is the exact error?

My bad..... More is better. Yes I am finding that, but most are generally they are the same with minute changes. Except when they use the CreateObject method.

I'm putting code together to work from VB net to Excel. This is a move from VBA to VB and so I'm trying to put the functions together to do what was done in VBA.

The error is: Excel.Application is not defined

Hope this helps?

[wes4dbt]

My bad..... More is better. Yes I am finding that, but most are generally they are the same with minute changes. Except when they use the CreateObject method.

I'm putting code together to work from VB net to Excel. This is a move from VBA to VB and so I'm trying to put the functions together to do what was done in VBA.

The error is: Excel.Application is not defined

Hope this helps?

Not very much.

So, do you want to create the data in VB and export to an Excel file or do you want to import an Excel file, manipulate the data and then export back to the same Excel file?

This is a move from VBA to VB and so I'm trying to put the functions together to do what was done in VBA

I have no idea what your VBA program did.

[FunkMonkey]

Not very much.

So, do you want to create the data in VB and export to an Excel file or do you want to import an Excel file, manipulate the data and then export back to the same Excel file?



I have no idea what your VBA program did.

Understood.

manipulate the data and then export back to the same Excel file

Yes and no. Some I will read in to drive the program but mostly changes will be written to/from the Excel document.

I didn't mean you needed to, but was rather indicating how one can control the workbook from VBA. Simple commands that manipulate the sheet as you need, this is what VBA gives. Theres more work to do in VB just for obtaining the same results as in VBA.

So what do you think is wrong with the code where "Excel.Application" is not defined, when, to me, it clearly is.

[Shaggy Hiker]

Do you have a reference to the Excel interop in the Solution Explorer? The error makes it sound like you do not, and that opens up a whole different can of worms.

With Excel, you have to include a reference to an interop, but each version of Excel uses a different interop. That isn't quite as big a deal anymore, I think, because I'm not sure that Excel is changing versions every other year the way it used to. If it still is, then this will still be a problem. If you have Excel X installed, but add the interop for Excel Y, it won't work....unless you use late binding.

You don't want to be developing using late binding, but when it comes to any Office products, you might want to end up there. With late binding, you build the application like normal, then you remove the reference to the interop. This will cause loads of, "This type is not defined" errors for things like Excel, Workbook, Worksheet, etc. You would then Set Option Strict OFF at the top of that page, and change all those types to Object. That's easy to do. Once you do that, so long as there is some version of the interop on the computer running the program, it will work (unless you get off into the weeds), regardless of which version of the interop you have. However, you lose all intellisense, so debugging becomes a total nightmare. For that reason, you don't want to go with late binding until you have the program working the way you want it to be...and for that, you need to have a reference to the interop.

[FunkMonkey]

Do you have a reference to the Excel interop in the Solution Explorer? The error makes it sound like you do not, and that opens up a whole different can of worms.

With Excel, you have to include a reference to an interop, but each version of Excel uses a different interop. That isn't quite as big a deal anymore, I think, because I'm not sure that Excel is changing versions every other year the way it used to. If it still is, then this will still be a problem. If you have Excel X installed, but add the interop for Excel Y, it won't work....unless you use late binding.

You don't want to be developing using late binding, but when it comes to any Office products, you might want to end up there. With late binding, you build the application like normal, then you remove the reference to the interop. This will cause loads of, "This type is not defined" errors for things like Excel, Workbook, Worksheet, etc. You would then Set Option Strict OFF at the top of that page, and change all those types to Object. That's easy to do. Once you do that, so long as there is some version of the interop on the computer running the program, it will work (unless you get off into the weeds), regardless of which version of the interop you have. However, you lose all intellisense, so debugging becomes a total nightmare. For that reason, you don't want to go with late binding until you have the program working the way you want it to be...and for that, you need to have a reference to the interop.

Thanks Shaggy. I'm showing I have Microsoft Excel 16.0 Object Library as a reference in Excel. Is this the interop you are rerfering to? What about going with the CreateObject method, is that also a problem in this fashion?

EDIT: do you have an example of what you're refering to?

[wes4dbt]

You don't have to use the Office Interop to import Excel data. You can do it using the OLEDB Data provider. Both work fine.

Here is an example where the Excel file is loaded in to a DataTable, you could also load it into a DataReader. I have an old version of MS Office (2010) so your Excel version number in the may need to be different (I don't know, you might have to research that)

        Dim con As New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0; Data Source=C:\AJunk2022\2022Test\ExcelTest.xls;Extended Properties='Excel 8.0;HDR=YES';")
        Dim da As New OleDbDataAdapter("select * from [Sheet1$]", con)
        Dim dt As New DataTable

        da.Fill(dt)

        DataGridView1.DataSource = dt

This example uses the MS Jet 4.0 data provider. This come preinstalled on Windows machines.

        Dim con As New OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0; Data Source=C:\AJunk2022\2022Test\ExcelTest.xlsx;Extended Properties='Excel 12.0;HDR=YES';")
        Dim da As New OleDbDataAdapter("select * from [Sheet1$]", con)
        Dim dt As New DataTable

        da.Fill(dt)

        DataGridView1.DataSource = dt

This example uses the MS ACE 12.0 OLEDB data provider. If Office is installed on the machine is will probably already be installed. If it's not, you can download the runtime engine from MS and install it.

Unfortunately I can't help you with the Interop method. I hadn't used it since I set up my current machine (been over a year ago) and when I went to create a demo I found out I have a problem with them being registered properly.