I'm pretty new to Visual Basic, so any help will be greatly appreciated!!! I have two different excel sheets with pretty much the same information but with updateddata in one of the columns(one excel sheet is from this week and one from last week). I'm trying to take this week'ssheet and subtract column B's information from column B's information from last week's sheet and copy the results into a new excel workbook. I'm creating an application with this happening on the Button Click event. I'm not really sure what to do next, help me please! I have referenced the Excel Object Library so that I can control excel through VB and I know its incomplete but I have this so far...

Dim cn1 As System.Data.OleDb.OleDbConnection

Dim cn2 As System.Data.OleDb.OleDbConnection

Dim cmd As System.Data.OleDb.OleDbDataAdapter

Dim ds As New System.Data.DataSet()

Dim NewSheet as New Excel.Application

cn1 = New System.Data.OleDb.OleDbConnection("provider=Microsoft.Jet.OLEDB.4.0;" & _

"data source=C:\thisWeek.XLS;Extended Properties=Excel 8.0;")

cn2 =New System.Data.OleDb.OleDbConnection("provider=Microsoft.Jet.OLEDB.4.0;" & _

"data source=C:\lastWeek.XLS;Extended Properties=Excel 8.0;")

cmd = New System.Data.OleDb.OleDbDataAdapter("select * from [thisWeek$]", cn1)

cn1.Open()

cmd.Fill(ds)

NewSheet.xls=ds.copy

cn1.close()
cn2.close()

Can I just put and excel formula in my code to directly referece these two columns in their respective sheets and compute it that way? Then output into a new sheet?

Hi,

I would write the macro in VBA and then call it from .NET ... have a look at these google results

http://www.google.co.uk/search?hl=en&q=run+vba+macro+from+.net&meta=

it will make you life a lot easier as Excel is very easy to play with from the VBA backend.

HTH,

James.

Hi, HELPNewToVB,

James Randle's idea of using VBA is fine,

however, there is still some other way to achieve this.

You can use Microsoft Excel Object Library to control the whole Excel WorkSheet,

including manipulating data, adding formula.

Please read the following article on automate Microsoft Excel

http://support.microsoft.com/kb/301982/en-us

Hopes this helps,

Regards

Hi,

I would write the macro in VBA and then call it from .NET ... have a look at these google results

http://www.google.co.uk/search?hl=en&q=run+vba+macro+from+.net&meta=

it will make you life a lot easier as Excel is very easy to play with from the VBA backend.

HTH,

James.

I'll look at those results and try it soon! Thank you so much. So should I just open the excel sheet from my button click event and have an automatic macro on that excel sheet?

Hi,

I think (i would expect that) you can run a macro by using it's name. this would be prefered because you don't really want something running every time someone opens the worksbook up - they might try to stop it half way through and break it!

Cheers,

James.

Hi, HELPNewToVB,

James Randle's idea of using VBA is fine,

however, there is still some other way to achieve this.

You can use Microsoft Excel Object Library to control the whole Excel WorkSheet,

including manipulating data, adding formula.

Please read the following article on automate Microsoft Excel

http://support.microsoft.com/kb/301982/en-us

Hopes this helps,

Regards

Awesome! Thank you so much!

Do you know of any other resources that I can find code examples toautomate excel from visual studio?

Hi, HELPNewToVB,

First, you can use search Engine such as Google with the key words "automate Excel VB.net".

Second, you can find a lot of examples on MSDN andhttp://www.codeproject.com.

I have found some for you,

http://msdn2.microsoft.com/en-us/office/aa905411.aspx

http://msdn2.microsoft.com/en-us/library/aa209782(office.10).aspx

http://support.microsoft.com/default.aspx/kb/302094

http://www.codeproject.com/office/csharp_excel.asp(with C#)

http://www.codeproject.com/office/excel_using_oledb.asp

http://www.codeproject.com/office/Excel_Automation.asp

Hopes this helps,

Regards

You have been abig help! Thank you so much!