By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,950 Members | 1,019 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,950 IT Pros & Developers. It's quick & easy.

Excel in VB.NET - looks the same as the old days?

P: n/a
To me, creating Excel 2003 spreadsheets programmatically via VB.NET hasn't
really changed since the days of VB6.

That is, I'd do something similar to this
Code:

Dim ExcelApp As Excel.Application
Dim ExcelWB As Excel.Workbook
Dim ExcelWS As Excel.Worksheet

Try
'
' Create a Spreadsheet for the selected company
'
ExcelApp = New Excel.Application
ExcelWB = ExcelApp.Workbooks.Add
ExcelWS = CType(ExcelWB.ActiveSheet, Excel.Worksheet)

' ...etc


What I'm wondering is there a better way of doing this now in VB.NET. That
is, can we get away from adding a reference to Excel11. This is unmanaged
code. (COM). Isn't there a .NET reference that can be added?

Someone mentioned I could go along the XML path, where I'd be creating XML
documents and the user would open them in Excel, etc. But this seams very
limiting to me. I want to be able to create a spreadsheet programmatically
with all the bells and whistles.

Of course I can do this via the old method but I can't believe MS hasn't
progressed from the old days. Or am I missing something? Probably am.

You may be asking what's wrong with the old way? Well in my experiences some
machines would not cope with creating instances of the Excel.Application,
...I'd have to use CreateObject("Excel.Application") instead. If the
application crashed half way thru there would be a bunch of Excel.exe 's
sitting in the task Manager. Argh. I just don't think it is pretty.

Any suggestions on an improvement?

Thanks,
Anthony
Nov 21 '05 #1
Share this Question
Share on Google+
9 Replies


P: n/a
Until they write a remoting piece to Excel, this is the only way to do it
really.

Chris
"Anthony" <an*****@sysdel.com.au> wrote in message
news:%2****************@TK2MSFTNGP11.phx.gbl...
To me, creating Excel 2003 spreadsheets programmatically via VB.NET hasn't
really changed since the days of VB6.

That is, I'd do something similar to this
Code:

Dim ExcelApp As Excel.Application
Dim ExcelWB As Excel.Workbook
Dim ExcelWS As Excel.Worksheet

Try
'
' Create a Spreadsheet for the selected company
'
ExcelApp = New Excel.Application
ExcelWB = ExcelApp.Workbooks.Add
ExcelWS = CType(ExcelWB.ActiveSheet, Excel.Worksheet)

' ...etc


What I'm wondering is there a better way of doing this now in VB.NET. That
is, can we get away from adding a reference to Excel11. This is unmanaged
code. (COM). Isn't there a .NET reference that can be added?

Someone mentioned I could go along the XML path, where I'd be creating XML
documents and the user would open them in Excel, etc. But this seams very
limiting to me. I want to be able to create a spreadsheet programmatically
with all the bells and whistles.

Of course I can do this via the old method but I can't believe MS hasn't
progressed from the old days. Or am I missing something? Probably am.

You may be asking what's wrong with the old way? Well in my experiences some machines would not cope with creating instances of the Excel.Application,
..I'd have to use CreateObject("Excel.Application") instead. If the
application crashed half way thru there would be a bunch of Excel.exe 's
sitting in the task Manager. Argh. I just don't think it is pretty.

Any suggestions on an improvement?

Thanks,
Anthony

Nov 21 '05 #2

P: n/a
Hi Anthony,

In addition to Chris's point, be aware that closing an excel spreadsheet is
a bit tricky in vb .net. Here's some of the code you'll need:
objwb.Close()

objxl.DisplayAlerts = True

Marshal.ReleaseComObject(objws)

objxl.Quit()

Marshal.ReleaseComObject(objxl)

objws = Nothing

objwb = Nothing

objwbs = Nothing

objxl = Nothing

HTH,

Bernie Yaeger

"Anthony" <an*****@sysdel.com.au> wrote in message
news:%2****************@TK2MSFTNGP11.phx.gbl...
To me, creating Excel 2003 spreadsheets programmatically via VB.NET hasn't
really changed since the days of VB6.

That is, I'd do something similar to this
Code:

Dim ExcelApp As Excel.Application
Dim ExcelWB As Excel.Workbook
Dim ExcelWS As Excel.Worksheet

Try
'
' Create a Spreadsheet for the selected company
'
ExcelApp = New Excel.Application
ExcelWB = ExcelApp.Workbooks.Add
ExcelWS = CType(ExcelWB.ActiveSheet, Excel.Worksheet)

' ...etc


What I'm wondering is there a better way of doing this now in VB.NET. That
is, can we get away from adding a reference to Excel11. This is unmanaged
code. (COM). Isn't there a .NET reference that can be added?

Someone mentioned I could go along the XML path, where I'd be creating XML
documents and the user would open them in Excel, etc. But this seams very
limiting to me. I want to be able to create a spreadsheet programmatically
with all the bells and whistles.

Of course I can do this via the old method but I can't believe MS hasn't
progressed from the old days. Or am I missing something? Probably am.

You may be asking what's wrong with the old way? Well in my experiences
some
machines would not cope with creating instances of the Excel.Application,
..I'd have to use CreateObject("Excel.Application") instead. If the
application crashed half way thru there would be a bunch of Excel.exe 's
sitting in the task Manager. Argh. I just don't think it is pretty.

Any suggestions on an improvement?

Thanks,
Anthony

Nov 21 '05 #3

P: n/a
It hasn't changed at all because you are still creating the same objects and
using the same methods and properties.

One thing that is different is the ability to use .NET code inside Excel
2003 or Word 2003 using Visual Studio Tools for Office.
http://msdn.microsoft.com/smartclien...standing/vsto/

--
Rob Windsor [MVP-VB]
G6 Consulting
Toronto, Canada
http://msmvps.com/windsor/
"Anthony" <an*****@sysdel.com.au> wrote in message
news:%2****************@TK2MSFTNGP11.phx.gbl...
To me, creating Excel 2003 spreadsheets programmatically via VB.NET hasn't
really changed since the days of VB6.

That is, I'd do something similar to this
Code:

Dim ExcelApp As Excel.Application
Dim ExcelWB As Excel.Workbook
Dim ExcelWS As Excel.Worksheet

Try
'
' Create a Spreadsheet for the selected company
'
ExcelApp = New Excel.Application
ExcelWB = ExcelApp.Workbooks.Add
ExcelWS = CType(ExcelWB.ActiveSheet, Excel.Worksheet)

' ...etc


What I'm wondering is there a better way of doing this now in VB.NET. That
is, can we get away from adding a reference to Excel11. This is unmanaged
code. (COM). Isn't there a .NET reference that can be added?

Someone mentioned I could go along the XML path, where I'd be creating XML
documents and the user would open them in Excel, etc. But this seams very
limiting to me. I want to be able to create a spreadsheet programmatically
with all the bells and whistles.

Of course I can do this via the old method but I can't believe MS hasn't
progressed from the old days. Or am I missing something? Probably am.

You may be asking what's wrong with the old way? Well in my experiences
some
machines would not cope with creating instances of the Excel.Application,
..I'd have to use CreateObject("Excel.Application") instead. If the
application crashed half way thru there would be a bunch of Excel.exe 's
sitting in the task Manager. Argh. I just don't think it is pretty.

Any suggestions on an improvement?

Thanks,
Anthony

Nov 21 '05 #4

P: n/a
Bernie,

I wanted to use your code, but need some help. Could you explain what your
different variables are? This is what I deduced:

Dim objxl As Excel.Application
Dim objwb As Excel.Workbook
Dim objws As Excel.Worksheet
Dim objwbs As ?

Thanks,
Greg

"Bernie Yaeger" <be*****@cherwellinc.com> wrote in message
news:Oq**************@TK2MSFTNGP15.phx.gbl...
Hi Anthony,

In addition to Chris's point, be aware that closing an excel spreadsheet
is a bit tricky in vb .net. Here's some of the code you'll need:
objwb.Close()

objxl.DisplayAlerts = True

Marshal.ReleaseComObject(objws)

objxl.Quit()

Marshal.ReleaseComObject(objxl)

objws = Nothing

objwb = Nothing

objwbs = Nothing

objxl = Nothing

HTH,

Bernie Yaeger

"Anthony" <an*****@sysdel.com.au> wrote in message
news:%2****************@TK2MSFTNGP11.phx.gbl...
To me, creating Excel 2003 spreadsheets programmatically via VB.NET
hasn't
really changed since the days of VB6.

That is, I'd do something similar to this
Code:

Dim ExcelApp As Excel.Application
Dim ExcelWB As Excel.Workbook
Dim ExcelWS As Excel.Worksheet

Try
'
' Create a Spreadsheet for the selected company
'
ExcelApp = New Excel.Application
ExcelWB = ExcelApp.Workbooks.Add
ExcelWS = CType(ExcelWB.ActiveSheet, Excel.Worksheet)

' ...etc


What I'm wondering is there a better way of doing this now in VB.NET.
That
is, can we get away from adding a reference to Excel11. This is unmanaged
code. (COM). Isn't there a .NET reference that can be added?

Someone mentioned I could go along the XML path, where I'd be creating
XML
documents and the user would open them in Excel, etc. But this seams very
limiting to me. I want to be able to create a spreadsheet
programmatically
with all the bells and whistles.

Of course I can do this via the old method but I can't believe MS hasn't
progressed from the old days. Or am I missing something? Probably am.

You may be asking what's wrong with the old way? Well in my experiences
some
machines would not cope with creating instances of the Excel.Application,
..I'd have to use CreateObject("Excel.Application") instead. If the
application crashed half way thru there would be a bunch of Excel.exe 's
sitting in the task Manager. Argh. I just don't think it is pretty.

Any suggestions on an improvement?

Thanks,
Anthony


Nov 21 '05 #5

P: n/a
"Anthony" <an*****@sysdel.com.au> schrieb:
To me, creating Excel 2003 spreadsheets programmatically via VB.NET hasn't
really changed since the days of VB6.


In addition to the other replies: There are loads of information about how
to use Office applications together with .NET in the Microsoft Office
Developer Center:

Microsoft Office Developer Center
<URL:http://msdn.microsoft.com/office/>

--
M S Herfried K. Wagner
M V P <URL:http://dotnet.mvps.org/>
V B <URL:http://dotnet.mvps.org/dotnet/faqs/>

Nov 21 '05 #6

P: n/a
Hi Greg,

Sorry I missed you last night - went to sleep early.

Your deductions were pretty sound:
Dim objxl As Excel.Application

Dim objwbs As Excel.Workbooks

Dim objwb As Excel.Workbook

Dim objws As Excel.Worksheet

Here's the full code I use to convert an sql table to a .csv or .xls. It's
in a .dll called 'imcfunctionlib':

Public Function sqltabletocsvorxls(ByVal dt As DataTable, ByRef strpath As
String, ByVal dtype As String, ByVal includeheader As Boolean) As Integer

' signature:

' dim funcs as new imcfunctionlib.functions

' dim xint as integer

' xint = funcs.sqltabletocsvorxls(dsmanifest.tables(0),mstr path,
"csv",false)

' where mstrpath = , say, "f:\imcapps\xlsfiles\test.xls"

sqltabletocsvorxls = 0

Dim objxl As Excel.Application

Dim objwbs As Excel.Workbooks

Dim objwb As Excel.Workbook

Dim objws As Excel.Worksheet

Dim mrow As DataRow

Dim colindex As Integer

Dim rowindex As Integer

Dim col As DataColumn

Dim fi As FileInfo = New FileInfo(strpath)

If fi.Exists = True Then

Kill(strpath)

End If

objxl = New Excel.Application

'objxl.Visible = False ' i may not need to do this

objwbs = objxl.Workbooks

objwb = objwbs.Add

objws = CType(objwb.Worksheets(1), Excel.Worksheet)

' i many want to change this to pass in a variable to determine

' if i want to have a column name row or not

If includeheader Then

For Each col In dt.Columns

colindex += 1

objws.Cells(1, colindex) = col.ColumnName

Next

rowindex = 1

Else

rowindex = 0

End If

Dim fixedstring As String

For Each mrow In dt.Rows

rowindex += 1

colindex = 0

For Each col In dt.Columns

colindex += 1

fixedstring = mrow(col.ColumnName).ToString().Replace(vbCrLf, "")

If col.DataType.ToString = "System.String" Then

objws.Cells(rowindex, colindex) = "'" & fixedstring

Else

objws.Cells(rowindex, colindex) = fixedstring

End If

Next

Next

If dtype = "csv" Then

objwb.SaveAs(strpath, xlCSV)

Else

objwb.SaveAs(strpath)

End If

objxl.DisplayAlerts = False

objwb.Close()

objxl.DisplayAlerts = True

Marshal.ReleaseComObject(objws)

objxl.Quit()

Marshal.ReleaseComObject(objxl)

objws = Nothing

objwb = Nothing

objwbs = Nothing

objxl = Nothing

End Function

HTH,

Bernie

"Greg Burns" <greg_burns@DONT_SPAM_ME_hotmail.com> wrote in message
news:e7*************@TK2MSFTNGP12.phx.gbl...
Bernie,

I wanted to use your code, but need some help. Could you explain what your
different variables are? This is what I deduced:

Dim objxl As Excel.Application
Dim objwb As Excel.Workbook
Dim objws As Excel.Worksheet
Dim objwbs As ?

Thanks,
Greg

"Bernie Yaeger" <be*****@cherwellinc.com> wrote in message
news:Oq**************@TK2MSFTNGP15.phx.gbl...
Hi Anthony,

In addition to Chris's point, be aware that closing an excel spreadsheet
is a bit tricky in vb .net. Here's some of the code you'll need:
objwb.Close()

objxl.DisplayAlerts = True

Marshal.ReleaseComObject(objws)

objxl.Quit()

Marshal.ReleaseComObject(objxl)

objws = Nothing

objwb = Nothing

objwbs = Nothing

objxl = Nothing

HTH,

Bernie Yaeger

"Anthony" <an*****@sysdel.com.au> wrote in message
news:%2****************@TK2MSFTNGP11.phx.gbl...
To me, creating Excel 2003 spreadsheets programmatically via VB.NET
hasn't
really changed since the days of VB6.

That is, I'd do something similar to this
Code:

Dim ExcelApp As Excel.Application
Dim ExcelWB As Excel.Workbook
Dim ExcelWS As Excel.Worksheet

Try
'
' Create a Spreadsheet for the selected company
'
ExcelApp = New Excel.Application
ExcelWB = ExcelApp.Workbooks.Add
ExcelWS = CType(ExcelWB.ActiveSheet, Excel.Worksheet)

' ...etc


What I'm wondering is there a better way of doing this now in VB.NET.
That
is, can we get away from adding a reference to Excel11. This is
unmanaged
code. (COM). Isn't there a .NET reference that can be added?

Someone mentioned I could go along the XML path, where I'd be creating
XML
documents and the user would open them in Excel, etc. But this seams
very
limiting to me. I want to be able to create a spreadsheet
programmatically
with all the bells and whistles.

Of course I can do this via the old method but I can't believe MS hasn't
progressed from the old days. Or am I missing something? Probably am.

You may be asking what's wrong with the old way? Well in my experiences
some
machines would not cope with creating instances of the
Excel.Application,
..I'd have to use CreateObject("Excel.Application") instead. If the
application crashed half way thru there would be a bunch of Excel.exe 's
sitting in the task Manager. Argh. I just don't think it is pretty.

Any suggestions on an improvement?

Thanks,
Anthony



Nov 21 '05 #7

P: n/a
I was just looking at my code (this was originally done in VB 6 and I am
trying to upgrade it)

Private objxlglobal As Excel.Global
Private objxl As Excel.Application
Private objwb As Excel.Workbook
Private objws As Excel.Worksheet

I have this objxlglobal scattered all throughout the program. I use it a lot
for its Selection object, like objxlglobal.Selection.Columns.AutoFit().

I didn't see you using that object. Would this be the correct code to
release it (see below)? I wasn't using these ReleaseComObject calls
previously. What are they doing for me? Also, I don't automatically close
Excel, I make it visible so the user can save or close as desired. Does
that change the need to release these variables (proboably not).

Marshal.ReleaseComObject(objws)
'objxl.Quit()
Marshal.ReleaseComObject(objxl)
Marshal.ReleaseComObject(objxlglobal)

objws = Nothing
objwb = Nothing
objxl = Nothing
objxlglobal = Nothing

Thanks,
Greg
"Bernie Yaeger" <be*****@cherwellinc.com> wrote in message
news:ex**************@TK2MSFTNGP12.phx.gbl...
Hi Greg,

Sorry I missed you last night - went to sleep early.

Your deductions were pretty sound:
Dim objxl As Excel.Application

Dim objwbs As Excel.Workbooks

Dim objwb As Excel.Workbook

Dim objws As Excel.Worksheet

Here's the full code I use to convert an sql table to a .csv or .xls.
It's in a .dll called 'imcfunctionlib':

Public Function sqltabletocsvorxls(ByVal dt As DataTable, ByRef strpath As
String, ByVal dtype As String, ByVal includeheader As Boolean) As Integer

' signature:

' dim funcs as new imcfunctionlib.functions

' dim xint as integer

' xint = funcs.sqltabletocsvorxls(dsmanifest.tables(0),mstr path,
"csv",false)

' where mstrpath = , say, "f:\imcapps\xlsfiles\test.xls"

sqltabletocsvorxls = 0

Dim objxl As Excel.Application

Dim objwbs As Excel.Workbooks

Dim objwb As Excel.Workbook

Dim objws As Excel.Worksheet

Dim mrow As DataRow

Dim colindex As Integer

Dim rowindex As Integer

Dim col As DataColumn

Dim fi As FileInfo = New FileInfo(strpath)

If fi.Exists = True Then

Kill(strpath)

End If

objxl = New Excel.Application

'objxl.Visible = False ' i may not need to do this

objwbs = objxl.Workbooks

objwb = objwbs.Add

objws = CType(objwb.Worksheets(1), Excel.Worksheet)

' i many want to change this to pass in a variable to determine

' if i want to have a column name row or not

If includeheader Then

For Each col In dt.Columns

colindex += 1

objws.Cells(1, colindex) = col.ColumnName

Next

rowindex = 1

Else

rowindex = 0

End If

Dim fixedstring As String

For Each mrow In dt.Rows

rowindex += 1

colindex = 0

For Each col In dt.Columns

colindex += 1

fixedstring = mrow(col.ColumnName).ToString().Replace(vbCrLf, "")

If col.DataType.ToString = "System.String" Then

objws.Cells(rowindex, colindex) = "'" & fixedstring

Else

objws.Cells(rowindex, colindex) = fixedstring

End If

Next

Next

If dtype = "csv" Then

objwb.SaveAs(strpath, xlCSV)

Else

objwb.SaveAs(strpath)

End If

objxl.DisplayAlerts = False

objwb.Close()

objxl.DisplayAlerts = True

Marshal.ReleaseComObject(objws)

objxl.Quit()

Marshal.ReleaseComObject(objxl)

objws = Nothing

objwb = Nothing

objwbs = Nothing

objxl = Nothing

End Function

HTH,

Bernie

"Greg Burns" <greg_burns@DONT_SPAM_ME_hotmail.com> wrote in message
news:e7*************@TK2MSFTNGP12.phx.gbl...
Bernie,

I wanted to use your code, but need some help. Could you explain what
your different variables are? This is what I deduced:

Dim objxl As Excel.Application
Dim objwb As Excel.Workbook
Dim objws As Excel.Worksheet
Dim objwbs As ?

Thanks,
Greg

"Bernie Yaeger" <be*****@cherwellinc.com> wrote in message
news:Oq**************@TK2MSFTNGP15.phx.gbl...
Hi Anthony,

In addition to Chris's point, be aware that closing an excel spreadsheet
is a bit tricky in vb .net. Here's some of the code you'll need:
objwb.Close()

objxl.DisplayAlerts = True

Marshal.ReleaseComObject(objws)

objxl.Quit()

Marshal.ReleaseComObject(objxl)

objws = Nothing

objwb = Nothing

objwbs = Nothing

objxl = Nothing

HTH,

Bernie Yaeger

"Anthony" <an*****@sysdel.com.au> wrote in message
news:%2****************@TK2MSFTNGP11.phx.gbl...
To me, creating Excel 2003 spreadsheets programmatically via VB.NET
hasn't
really changed since the days of VB6.

That is, I'd do something similar to this
Code:

Dim ExcelApp As Excel.Application
Dim ExcelWB As Excel.Workbook
Dim ExcelWS As Excel.Worksheet

Try
'
' Create a Spreadsheet for the selected company
'
ExcelApp = New Excel.Application
ExcelWB = ExcelApp.Workbooks.Add
ExcelWS = CType(ExcelWB.ActiveSheet, Excel.Worksheet)

' ...etc


What I'm wondering is there a better way of doing this now in VB.NET.
That
is, can we get away from adding a reference to Excel11. This is
unmanaged
code. (COM). Isn't there a .NET reference that can be added?

Someone mentioned I could go along the XML path, where I'd be creating
XML
documents and the user would open them in Excel, etc. But this seams
very
limiting to me. I want to be able to create a spreadsheet
programmatically
with all the bells and whistles.

Of course I can do this via the old method but I can't believe MS
hasn't
progressed from the old days. Or am I missing something? Probably am.

You may be asking what's wrong with the old way? Well in my experiences
some
machines would not cope with creating instances of the
Excel.Application,
..I'd have to use CreateObject("Excel.Application") instead. If the
application crashed half way thru there would be a bunch of Excel.exe
's
sitting in the task Manager. Argh. I just don't think it is pretty.

Any suggestions on an improvement?

Thanks,
Anthony



Nov 21 '05 #8

P: n/a
Hi Greg,

The releasecomobject is necessary to take excel out of memory as a process.
If you open task manager you will see each instance of Excel unless you do
this.

Since you actually want to have excel available, you should not use the
releasecomobject until you want to dispose of it completely.
Also, I haven't been making the kinds of changes to the spreadsheet as you
do, so I haven't had to use the global you refer to, but much has not
changed, so I'd say continue to use it and test to see if it does what it
did formerly. It's pretty much the same object model, so it should work
fine.

Bernie

"Greg Burns" <greg_burns@DONT_SPAM_ME_hotmail.com> wrote in message
news:Op**************@tk2msftngp13.phx.gbl...
I was just looking at my code (this was originally done in VB 6 and I am
trying to upgrade it)

Private objxlglobal As Excel.Global
Private objxl As Excel.Application
Private objwb As Excel.Workbook
Private objws As Excel.Worksheet

I have this objxlglobal scattered all throughout the program. I use it a
lot for its Selection object, like
objxlglobal.Selection.Columns.AutoFit().

I didn't see you using that object. Would this be the correct code to
release it (see below)? I wasn't using these ReleaseComObject calls
previously. What are they doing for me? Also, I don't automatically close
Excel, I make it visible so the user can save or close as desired. Does
that change the need to release these variables (proboably not).

Marshal.ReleaseComObject(objws)
'objxl.Quit()
Marshal.ReleaseComObject(objxl)
Marshal.ReleaseComObject(objxlglobal)

objws = Nothing
objwb = Nothing
objxl = Nothing
objxlglobal = Nothing

Thanks,
Greg
"Bernie Yaeger" <be*****@cherwellinc.com> wrote in message
news:ex**************@TK2MSFTNGP12.phx.gbl...
Hi Greg,

Sorry I missed you last night - went to sleep early.

Your deductions were pretty sound:
Dim objxl As Excel.Application

Dim objwbs As Excel.Workbooks

Dim objwb As Excel.Workbook

Dim objws As Excel.Worksheet

Here's the full code I use to convert an sql table to a .csv or .xls.
It's in a .dll called 'imcfunctionlib':

Public Function sqltabletocsvorxls(ByVal dt As DataTable, ByRef strpath
As String, ByVal dtype As String, ByVal includeheader As Boolean) As
Integer

' signature:

' dim funcs as new imcfunctionlib.functions

' dim xint as integer

' xint = funcs.sqltabletocsvorxls(dsmanifest.tables(0),mstr path,
"csv",false)

' where mstrpath = , say, "f:\imcapps\xlsfiles\test.xls"

sqltabletocsvorxls = 0

Dim objxl As Excel.Application

Dim objwbs As Excel.Workbooks

Dim objwb As Excel.Workbook

Dim objws As Excel.Worksheet

Dim mrow As DataRow

Dim colindex As Integer

Dim rowindex As Integer

Dim col As DataColumn

Dim fi As FileInfo = New FileInfo(strpath)

If fi.Exists = True Then

Kill(strpath)

End If

objxl = New Excel.Application

'objxl.Visible = False ' i may not need to do this

objwbs = objxl.Workbooks

objwb = objwbs.Add

objws = CType(objwb.Worksheets(1), Excel.Worksheet)

' i many want to change this to pass in a variable to determine

' if i want to have a column name row or not

If includeheader Then

For Each col In dt.Columns

colindex += 1

objws.Cells(1, colindex) = col.ColumnName

Next

rowindex = 1

Else

rowindex = 0

End If

Dim fixedstring As String

For Each mrow In dt.Rows

rowindex += 1

colindex = 0

For Each col In dt.Columns

colindex += 1

fixedstring = mrow(col.ColumnName).ToString().Replace(vbCrLf, "")

If col.DataType.ToString = "System.String" Then

objws.Cells(rowindex, colindex) = "'" & fixedstring

Else

objws.Cells(rowindex, colindex) = fixedstring

End If

Next

Next

If dtype = "csv" Then

objwb.SaveAs(strpath, xlCSV)

Else

objwb.SaveAs(strpath)

End If

objxl.DisplayAlerts = False

objwb.Close()

objxl.DisplayAlerts = True

Marshal.ReleaseComObject(objws)

objxl.Quit()

Marshal.ReleaseComObject(objxl)

objws = Nothing

objwb = Nothing

objwbs = Nothing

objxl = Nothing

End Function

HTH,

Bernie

"Greg Burns" <greg_burns@DONT_SPAM_ME_hotmail.com> wrote in message
news:e7*************@TK2MSFTNGP12.phx.gbl...
Bernie,

I wanted to use your code, but need some help. Could you explain what
your different variables are? This is what I deduced:

Dim objxl As Excel.Application
Dim objwb As Excel.Workbook
Dim objws As Excel.Worksheet
Dim objwbs As ?

Thanks,
Greg

"Bernie Yaeger" <be*****@cherwellinc.com> wrote in message
news:Oq**************@TK2MSFTNGP15.phx.gbl...
Hi Anthony,

In addition to Chris's point, be aware that closing an excel
spreadsheet is a bit tricky in vb .net. Here's some of the code you'll
need:
objwb.Close()

objxl.DisplayAlerts = True

Marshal.ReleaseComObject(objws)

objxl.Quit()

Marshal.ReleaseComObject(objxl)

objws = Nothing

objwb = Nothing

objwbs = Nothing

objxl = Nothing

HTH,

Bernie Yaeger

"Anthony" <an*****@sysdel.com.au> wrote in message
news:%2****************@TK2MSFTNGP11.phx.gbl...
> To me, creating Excel 2003 spreadsheets programmatically via VB.NET
> hasn't
> really changed since the days of VB6.
>
> That is, I'd do something similar to this
>
>
> Code:
>
> Dim ExcelApp As Excel.Application
> Dim ExcelWB As Excel.Workbook
> Dim ExcelWS As Excel.Worksheet
>
> Try
> '
> ' Create a Spreadsheet for the selected company
> '
> ExcelApp = New Excel.Application
> ExcelWB = ExcelApp.Workbooks.Add
> ExcelWS = CType(ExcelWB.ActiveSheet, Excel.Worksheet)
>
> ' ...etc
>
>
>
>
> What I'm wondering is there a better way of doing this now in VB.NET.
> That
> is, can we get away from adding a reference to Excel11. This is
> unmanaged
> code. (COM). Isn't there a .NET reference that can be added?
>
> Someone mentioned I could go along the XML path, where I'd be creating
> XML
> documents and the user would open them in Excel, etc. But this seams
> very
> limiting to me. I want to be able to create a spreadsheet
> programmatically
> with all the bells and whistles.
>
> Of course I can do this via the old method but I can't believe MS
> hasn't
> progressed from the old days. Or am I missing something? Probably am.
>
> You may be asking what's wrong with the old way? Well in my
> experiences some
> machines would not cope with creating instances of the
> Excel.Application,
> ..I'd have to use CreateObject("Excel.Application") instead. If the
> application crashed half way thru there would be a bunch of Excel.exe
> 's
> sitting in the task Manager. Argh. I just don't think it is pretty.
>
> Any suggestions on an improvement?
>
> Thanks,
> Anthony
>
>



Nov 21 '05 #9

P: n/a
Thanks very much to all that responded.

I've decided to go with the "old" style referencing of Excel11 et al.

As our company is just about to move from Office 97 to Office 2003 (5000
employees!), ... I've been doing a little reading and one of the microsoft
white pages (97-03Delta.doc) says late binding is actually more beneficial
in some instances, ...more reading required. Since I now know that everyone
is on 2003 I will prbably early bind (need the intellisense!)

Once I got my head round the releasing of objects (marshal) properly (order
seems very important) it all looks fine.

Thanks again.
Anthony

"Herfried K. Wagner [MVP]" <hi***************@gmx.at> wrote in message
news:Om**************@TK2MSFTNGP12.phx.gbl...
"Anthony" <an*****@sysdel.com.au> schrieb:
To me, creating Excel 2003 spreadsheets programmatically via VB.NET hasn't really changed since the days of VB6.
In addition to the other replies: There are loads of information about

how to use Office applications together with .NET in the Microsoft Office
Developer Center:

Microsoft Office Developer Center
<URL:http://msdn.microsoft.com/office/>

--
M S Herfried K. Wagner
M V P <URL:http://dotnet.mvps.org/>
V B <URL:http://dotnet.mvps.org/dotnet/faqs/>

Nov 21 '05 #10

This discussion thread is closed

Replies have been disabled for this discussion.