Excel

[Sam]

Hi All,

I posted this message few days ago. No one has answered so far.
I just would like to know if it is possible to do or not.
Even if you can not tell me how to do this, maybe you know which library I
need to use in order to access excel information from a VB.NET code.
For example, I would be interested by knowing how to access a simple cell
value in an excel file from a vb.net code.

Here is the mail I sent last week, but as I said, just knowing how to access
excel cell value would be enough for me to start.

---------

I would like to know if it is possible to group three excell files with only
one worksheet inside each file into one excel file with three worksheets

so, at the begining I have
1 excel file with one Worksheet inside
another excel file with one Worksheet inside
another excel file with one Worksheet inside

and at the end I (should) have
1 excel file with three worksheets inside

If so, would you tell me which object/interfaces/method I need to use in
order to make it work ?

Big thank to your help

Samuel

 

[Pipo]

Hi Sam,

Look at this.
[http://support.microsoft.com/default.aspx?scid=kb;EN-US;316934#12]

Hope it helps you.

 

[Rob Nicholson]

so, at the begining I have

1 excel file with one Worksheet inside
another excel file with one Worksheet inside
another excel file with one Worksheet inside

and at the end I (should) have
1 excel file with three worksheets inside

If so, would you tell me which object/interfaces/method I need to use in
order to make it work ?

What's the host for this application - a Windows or web app?

The Excel object model is the way to go about this and I can help if
required.

Cheers, Rob.

 

[Sam]

Thank you Rob.

Yes, if possible, your help would be highly appreciated.

It is a Console application. I need to add a functionality on an existing
program so I have no choice, it has to be a console app.

Right now, I have added as reference the COM Component "Microsoft Excel 10.0
Object Librairy" and it allows me to create these objects within my class :

Private m_oExcelApp As Excel.ApplicationClass

Private m_oBooks As Excel.Workbooks

Private m_oBook As Excel.Workbook

Private m_oSheet As Excel.Worksheet

What would you suggest to copy and paste sheets from excel files from file A
to file B ?

Thank you very much...

Samuel

 

[Rob Nicholson]

Thank you Rob.

Yes, if possible, your help would be highly appreciated.

The three workbooks each one one worksheet - are the worksheet names
different in each worksheet or the same?

Cheers, Rob.

 

[Rob Nicholson]

Right now, I have added as reference the COM Component "Microsoft Excel
10.0

Object Librairy" and it allows me to create these objects within my class

:

That's the hard part over with then Here's some source code to read all
the sheets from three input workbooks and write them into a output workbook.
It can handle any number of worksheets in the inputs although you've only
got one. It also handles the situation whereby the sheets in the three input
workbooks might have the same name (which would cause an error) and names
them as Book1_Sheet1, Book2_Sheet1 and Book3_Sheet2.

It's written in VBA as that's all I had to hand but it'll translate very
easily into VB.NET.

A few points:

1. Make sure you cleanup afterwards (as this code does) or you'll end up
with EXCEL.EXE processes hang around in memory
2. When a new workbook is created, it gets typically three blanks sheets
called SHEET1, SHEET2 & SHEET3. You don't want these so they are deleted.
Unfortunately, you can't delete all three - you have to leave at least one
so this is deleted at the end.
3. DisplayAlerts prevents Excel popping up a message when a sheet is
deleted.
4. Tested using Excel 2000 but the object model hasn't changed much.
5. There's no error handling

Cheers, Rob.

Option Explicit

Sub Copy()

' List of workbooks to read.

Dim InputWorkbooks As New Collection
InputWorkbooks.Add "s:\temp\excel example\book1.xls"
InputWorkbooks.Add "s:\temp\excel example\book2.xls"
InputWorkbooks.Add "s:\temp\excel example\book3.xls"

' Output workbook.

Dim OutputWorkbookFileName As String
OutputWorkbookFileName = "s:\temp\excel example\master.xls"

' Create an Excel application.

Dim ExcelApp As Excel.Application
Set ExcelApp = New Excel.Application

' Create output workbook.

Dim OutputWorkbook As Excel.Workbook
Set OutputWorkbook = ExcelApp.Workbooks.Add

' Delete all default sheets except the first one - when a new workbook is
created, it has several blanks sheets. You
' can't delete them all - must be at least one.

ExcelApp.DisplayAlerts = False ' otherwise a "are you sure" prompt
appears for FirstSheet.Delete
While OutputWorkbook.Worksheets.Count > 1
Dim FirstSheet As Worksheet
Set FirstSheet = OutputWorkbook.Sheets(1)
FirstSheet.Delete
Wend

' Make sure remaining sheet (can't delete all sheets) has a unique name so
it doesn't conflict with incoming sheets.

Set FirstSheet = OutputWorkbook.Worksheets(1)
FirstSheet.Name = "$$WIBBLE$$"

' Loop for each input workbook.

Dim BookCounter As Long ' Used to prefix each sheet from workbook
in case sheet names conflicts
BookCounter = 1
Dim InputWorkbookFileName As Variant
For Each InputWorkbookFileName In InputWorkbooks

' Open the input Excel workbook.

Dim InputWorkbook As Excel.Workbook
Set InputWorkbook = ExcelApp.Workbooks.Open(InputWorkbookFileName,
False, True)

' Loop for each worksheet in the input workbook.

Dim Worksheet As Excel.Worksheet
For Each Worksheet In InputWorkbook.Worksheets

' Copy sheet to output workbook after last current sheet.

Worksheet.Copy ,
OutputWorkbook.Worksheets(OutputWorkbook.Worksheets.Count)

' Rename the newly copied sheet so names don't conflict.

Dim NewWorksheet As Excel.Worksheet
Set NewWorksheet =
OutputWorkbook.Worksheets(OutputWorkbook.Worksheets.Count)
NewWorksheet.Name = "Book" & BookCounter & "_" & Worksheet.Name

' Next input worksheet.

Next Worksheet

' Next input workbook.

InputWorkbook.Close False
Set InputWorkbook = Nothing
BookCounter = BookCounter + 1

Next

' Delete the unused first sheet left from when the workbook was created.

FirstSheet.Delete

' Save the output workbook (delete old copy first).

OutputWorkbook.SaveAs OutputWorkbookFileName

' Cleanup.

OutputWorkbook.Close False
Set OutputWorkbook = Nothing
Set ExcelApp = Nothing

End Sub

 

[Rob Nicholson]

The three workbooks each one one worksheet - are the worksheet names

different in each worksheet or the same?

Forget it - example code I gave renames the sheets so they are unique.

Cheers, Rob.

 

[Sam]

Thank you very much.

I tested it and it works fine, it is exactly what I needed.

Translation to VB.NET is easy. In fact, nothing to change barely, except the
"FirstSheet" declaration who has been done within a loop (while), and as
this variable is used out of the loop as well, .NET does not allow this.

Really really really big thanks.

Sam

 

[Rob Nicholson]

Translation to VB.NET is easy. In fact, nothing to change barely, except
the

"FirstSheet" declaration who has been done within a loop (while), and as
this variable is used out of the loop as well, .NET does not allow this.

Yes, VB.NET variable scope is a bit tighter and more C++ like.

Really really really big thanks.

Glad I could help.

Cheers, Rob.