Opening Excel in my program

  • Thread starter Thread starter Edgar
  • Start date Start date
E

Edgar

I had best explain what I'm doing first. I need to write
a method in C# that will open an excel file and read the
contents into a DataSet, which will then be placed into a
database. I would like to write it such that my method
signature is this: public void FromExcel(string fileName)
{...body...}. While I am writing this method in C# I
will welcome VB.NET examples. Thank you.
 
This is a simple example in VB.NET. It assumes that the column names are in
the first row and that the last row on a worksheet can be determined by
looking for an empty cell in the first column. You need to set up a
reference to the Excel object library for this to work.

This function takes the workbook filename (full path) as an argument and
returns a DataSet with one DataTable for each worksheet.

Private Function fImportData(ByVal sWorkbookName As String) As DataSet
Dim xlApp As Excel.Application, xwbImport As Excel.Workbook,
xwsImport As Excel.Worksheet
Dim dtWorksheet As DataTable
Dim drCurrRow As DataRow
Dim nColumn As Integer, nRow As Integer

xlApp = New Excel.Application()
xwbImport = xlApp.Workbooks.Open(sWorkbookName)
fImportData = New DataSet()
For Each xwsImport In xwbImport.Worksheets
If xwsImport.Cells(1, 1).Value <> "" Then
dtWorksheet = fImportData.Tables.Add(xwsImport.Name)
nColumn = 1
While xwsImport.Cells(1, nColumn).Value <> ""
dtWorksheet.Columns.Add(xwsImport.Cells(1,
nColumn).Value)
nColumn = nColumn + 1
End While
nRow = 2
While xwsImport.Cells(nRow, 1).Value <> ""
nColumn = 1
drCurrRow = dtWorksheet.NewRow
While xwsImport.Cells(1, nColumn).value <> ""
drCurrRow(nColumn - 1) = xwsImport.Cells(nRow,
nColumn).Value
nColumn = nColumn + 1
End While
dtWorksheet.Rows.Add(drCurrRow)
nRow = nRow + 1
End While
End If
Next xwsImport
End Function

Hope this helps,
Paul
 
Paul,

I posted a pretty silly post 'here
(http://www.excelforum.com/t177312-s) a few minutes ago about running
macro from an .exe file. Can I write an application in VB.net that wil
perform the same task as the macro, but perform it as an exe withou
opening excel.

The macro simply formats an xls file so that it can be imported into
database.

Thanks

.tehw
 
Sort of...

In VB.NET you can use code like:

Public Sub SomeSub(...)
Dim xlApp as Excel.Application

Set xlApp = New Excel.Application
' your VBA code here
xlApp.Quit
End Sub

and then compile this into an .exe. You will need to set a reference to the
Excel library (on the COM tab) in your code, and every top-level call to
Excel will need to be qualified using xlApp (e.g. if your VBA used the
Workbooks collection, this would need to be changed to xlApp.Workbooks).

This still opens Excel, and so still requires the end-user to have Excel
installed on their machine, however, Excel will not be visible to the
end-user.

Note that there are some small differences between VBA and VB.NET (e.g.
using End While instead of Wend), these are all documented in the Visual
Studio.NET documentation.

Let me know how you get on.

Cheers,
Paul
 
Paul Reynolds said:
Sort of...

In VB.NET you can use code like:

Public Sub SomeSub(...)
Dim xlApp as Excel.Application

Set xlApp = New Excel.Application
' your VBA code here
xlApp.Quit
End Sub
Click to expand...
....

Depending on how good (or not) the garbage collection is, it may be safer to
add a final statement:

Set xlApp = Nothing
 
Back
Top