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

Exporting a vb.net datagrid to excel

P: n/a
Hi All :-)

I'm using .Net Framework 1.1, VB and need to be able to download the data
from a datagrid into an Excel 2000 spreadsheet for our accounting users.
Can anyone please point me to a good link on how to do this or give me some
pointers? TIA,

Coleen
Sep 6 '06 #1
Share this Question
Share on Google+
6 Replies


P: n/a
Hi,

Excel = CreateObject("Excel.Application")

With Excel
.SheetsInNewWorkbook = 1
.Workbooks.Add()
.Worksheets(1).Select()

'For displaying the column value row-by-row in the the
excel file.

For intRow = 0 To ds.Tables(0).Rows.Count - 1

For intColumnValue = 0 To dt.Tables(0).Columns.Count - 1
.Cells(intRow + 1, intColumnValue + 1).Value =
ds.Tables(0).Rows(intRow).ItemArray(intColumnValue ).ToString
Next

Next

P
.ActiveWorkbook().SaveAs(strDir & strFileName)

.ActiveWorkbook.Close()
End With
Excel.Quit()
Excel = Nothing
GC.Collect()
End

it's a sample from my own app. it isn't very complicated so i think
that you would understand it :)

Mrozu
Coleen napisal(a):
Hi All :-)

I'm using .Net Framework 1.1, VB and need to be able to download the data
from a datagrid into an Excel 2000 spreadsheet for our accounting users.
Can anyone please point me to a good link on how to do this or give me some
pointers? TIA,

Coleen
Sep 6 '06 #2

P: n/a
Thank you so much Mrozu!

I will try this - I appreciate your help :-)
"Mrozu" <gr************@gmail.comwrote in message
news:11**********************@e3g2000cwe.googlegro ups.com...
Hi,

Excel = CreateObject("Excel.Application")

With Excel
.SheetsInNewWorkbook = 1
.Workbooks.Add()
.Worksheets(1).Select()

'For displaying the column value row-by-row in the the
excel file.

For intRow = 0 To ds.Tables(0).Rows.Count - 1

For intColumnValue = 0 To dt.Tables(0).Columns.Count - 1
.Cells(intRow + 1, intColumnValue + 1).Value =
ds.Tables(0).Rows(intRow).ItemArray(intColumnValue ).ToString
Next

Next

P
.ActiveWorkbook().SaveAs(strDir & strFileName)

.ActiveWorkbook.Close()
End With
Excel.Quit()
Excel = Nothing
GC.Collect()
End

it's a sample from my own app. it isn't very complicated so i think
that you would understand it :)

Mrozu
Coleen napisal(a):
Hi All :-)

I'm using .Net Framework 1.1, VB and need to be able to download the
data
from a datagrid into an Excel 2000 spreadsheet for our accounting users.
Can anyone please point me to a good link on how to do this or give me
some
pointers? TIA,

Coleen

Sep 6 '06 #3

P: n/a
Hi Mrozu :-)

I tried your code and get this error:
"Cannot create ActiveX component. "

Here is the code that I am using:
Public Sub send_to_excel()
Dim Excel As Object
Dim intRow As Integer = 0
Dim intColumnValue As Integer = 0
Dim strDir As String = ""
Dim strFilename As String = ""
Dim ds As DataSet
Dim dt As DataTable = idt_final_report

Excel = CreateObject("Excel.application")

With Excel
.SheetsInNewWorkbook = 1
.Workbooks.Add()
.Worksheets(1).Select()

'To display the column value row-by-row in the excel file
For intRow = 0 To ds.Tables(0).Rows.Count - 1

For intColumnValue = 0 To ds.Tables(0).Columns.Count - 1
.cells(intRow + 1, intColumnValue + 1).value.ToString()
ds.Tables(0).Rows(intRow).ItemArray(intColumnValue ).ToString()
Next

Next

.activeWorkbook().SaveAs(strDir & strFilename)
.activeWorkbook.close()
End With
Excel.Quit()
Excel = Nothing
GC.Collect()

End Sub

I know I haven't defined the filename or file directory, but I wanted to
test it first and see if it would even read the datatable I already have
established. It seems to set the Datatable to idt_final_report just fine,
but on the very next line for Excel = CreateObject("Excel.application"), I
get the error. Can you please explain a little more? What am I missing?
Do you know of a link that shows how to do this? Thanks so much for your
time.

Coleen
"Mrozu" <gr************@gmail.comwrote in message
news:11**********************@e3g2000cwe.googlegro ups.com...
Hi,

Excel = CreateObject("Excel.Application")

With Excel
.SheetsInNewWorkbook = 1
.Workbooks.Add()
.Worksheets(1).Select()

'For displaying the column value row-by-row in the the
excel file.

For intRow = 0 To ds.Tables(0).Rows.Count - 1

For intColumnValue = 0 To dt.Tables(0).Columns.Count - 1
.Cells(intRow + 1, intColumnValue + 1).Value =
ds.Tables(0).Rows(intRow).ItemArray(intColumnValue ).ToString
Next

Next

P
.ActiveWorkbook().SaveAs(strDir & strFileName)

.ActiveWorkbook.Close()
End With
Excel.Quit()
Excel = Nothing
GC.Collect()
End

it's a sample from my own app. it isn't very complicated so i think
that you would understand it :)

Mrozu
Coleen napisal(a):
Hi All :-)

I'm using .Net Framework 1.1, VB and need to be able to download the
data
from a datagrid into an Excel 2000 spreadsheet for our accounting users.
Can anyone please point me to a good link on how to do this or give me
some
pointers? TIA,

Coleen

Sep 7 '06 #4

P: n/a
Hi,

so the problem is when u don't have installed Excel on your machine;)

this code is only available when you have it installed. Sorry that I
haven't told you that.

Mrozu
Coleen napisal(a):
Hi Mrozu :-)

I tried your code and get this error:
"Cannot create ActiveX component. "

Here is the code that I am using:
Public Sub send_to_excel()
Dim Excel As Object
Dim intRow As Integer = 0
Dim intColumnValue As Integer = 0
Dim strDir As String = ""
Dim strFilename As String = ""
Dim ds As DataSet
Dim dt As DataTable = idt_final_report

Excel = CreateObject("Excel.application")

With Excel
.SheetsInNewWorkbook = 1
.Workbooks.Add()
.Worksheets(1).Select()

'To display the column value row-by-row in the excel file
For intRow = 0 To ds.Tables(0).Rows.Count - 1

For intColumnValue = 0 To ds.Tables(0).Columns.Count - 1
.cells(intRow + 1, intColumnValue + 1).value.ToString()
ds.Tables(0).Rows(intRow).ItemArray(intColumnValue ).ToString()
Next

Next

.activeWorkbook().SaveAs(strDir & strFilename)
.activeWorkbook.close()
End With
Excel.Quit()
Excel = Nothing
GC.Collect()

End Sub

I know I haven't defined the filename or file directory, but I wanted to
test it first and see if it would even read the datatable I already have
established. It seems to set the Datatable to idt_final_report just fine,
but on the very next line for Excel = CreateObject("Excel.application"), I
get the error. Can you please explain a little more? What am I missing?
Do you know of a link that shows how to do this? Thanks so much for your
time.

Coleen
"Mrozu" <gr************@gmail.comwrote in message
news:11**********************@e3g2000cwe.googlegro ups.com...
Hi,

Excel = CreateObject("Excel.Application")

With Excel
.SheetsInNewWorkbook = 1
.Workbooks.Add()
.Worksheets(1).Select()

'For displaying the column value row-by-row in the the
excel file.

For intRow = 0 To ds.Tables(0).Rows.Count - 1

For intColumnValue = 0 To dt.Tables(0).Columns.Count - 1
.Cells(intRow + 1, intColumnValue + 1).Value =
ds.Tables(0).Rows(intRow).ItemArray(intColumnValue ).ToString
Next

Next

P
.ActiveWorkbook().SaveAs(strDir & strFileName)

.ActiveWorkbook.Close()
End With
Excel.Quit()
Excel = Nothing
GC.Collect()
End

it's a sample from my own app. it isn't very complicated so i think
that you would understand it :)

Mrozu
Coleen napisal(a):
Hi All :-)
>
I'm using .Net Framework 1.1, VB and need to be able to download the
data
from a datagrid into an Excel 2000 spreadsheet for our accounting users.
Can anyone please point me to a good link on how to do this or give me
some
pointers? TIA,
>
Coleen
Sep 7 '06 #5

P: n/a
Hi,

so the problem is when u don't have installed Excel on your machine;)

this code is only available when you have it installed. Sorry that I
haven't told you that.

Mrozu
Coleen napisal(a):
Hi Mrozu :-)

I tried your code and get this error:
"Cannot create ActiveX component. "

Here is the code that I am using:
Public Sub send_to_excel()
Dim Excel As Object
Dim intRow As Integer = 0
Dim intColumnValue As Integer = 0
Dim strDir As String = ""
Dim strFilename As String = ""
Dim ds As DataSet
Dim dt As DataTable = idt_final_report

Excel = CreateObject("Excel.application")

With Excel
.SheetsInNewWorkbook = 1
.Workbooks.Add()
.Worksheets(1).Select()

'To display the column value row-by-row in the excel file
For intRow = 0 To ds.Tables(0).Rows.Count - 1

For intColumnValue = 0 To ds.Tables(0).Columns.Count - 1
.cells(intRow + 1, intColumnValue + 1).value.ToString()
ds.Tables(0).Rows(intRow).ItemArray(intColumnValue ).ToString()
Next

Next

.activeWorkbook().SaveAs(strDir & strFilename)
.activeWorkbook.close()
End With
Excel.Quit()
Excel = Nothing
GC.Collect()

End Sub

I know I haven't defined the filename or file directory, but I wanted to
test it first and see if it would even read the datatable I already have
established. It seems to set the Datatable to idt_final_report just fine,
but on the very next line for Excel = CreateObject("Excel.application"), I
get the error. Can you please explain a little more? What am I missing?
Do you know of a link that shows how to do this? Thanks so much for your
time.

Coleen
"Mrozu" <gr************@gmail.comwrote in message
news:11**********************@e3g2000cwe.googlegro ups.com...
Hi,

Excel = CreateObject("Excel.Application")

With Excel
.SheetsInNewWorkbook = 1
.Workbooks.Add()
.Worksheets(1).Select()

'For displaying the column value row-by-row in the the
excel file.

For intRow = 0 To ds.Tables(0).Rows.Count - 1

For intColumnValue = 0 To dt.Tables(0).Columns.Count - 1
.Cells(intRow + 1, intColumnValue + 1).Value =
ds.Tables(0).Rows(intRow).ItemArray(intColumnValue ).ToString
Next

Next

P
.ActiveWorkbook().SaveAs(strDir & strFileName)

.ActiveWorkbook.Close()
End With
Excel.Quit()
Excel = Nothing
GC.Collect()
End

it's a sample from my own app. it isn't very complicated so i think
that you would understand it :)

Mrozu
Coleen napisal(a):
Hi All :-)
>
I'm using .Net Framework 1.1, VB and need to be able to download the
data
from a datagrid into an Excel 2000 spreadsheet for our accounting users.
Can anyone please point me to a good link on how to do this or give me
some
pointers? TIA,
>
Coleen
Sep 7 '06 #6

P: n/a
No that is not the problem - I DO have Excel installed on my machine - I use
it almost every day. It does not reside on a server, I actually have Excel
installed: Microsoft Excel 2000 (9.0.3926 SP-3) so that can't be the
problem. I must be missing something. Is there an Imports that I need to
include? I also had to Rem out the Option Strict and Option Explicit I had
set to On for this page. Would that make any difference?
"Mrozu" <gr************@gmail.comwrote in message
news:11**********************@m79g2000cwm.googlegr oups.com...
Hi,

so the problem is when u don't have installed Excel on your machine;)

this code is only available when you have it installed. Sorry that I
haven't told you that.

Mrozu
Coleen napisal(a):
Hi Mrozu :-)

I tried your code and get this error:
"Cannot create ActiveX component. "

Here is the code that I am using:
Public Sub send_to_excel()
Dim Excel As Object
Dim intRow As Integer = 0
Dim intColumnValue As Integer = 0
Dim strDir As String = ""
Dim strFilename As String = ""
Dim ds As DataSet
Dim dt As DataTable = idt_final_report

Excel = CreateObject("Excel.application")

With Excel
.SheetsInNewWorkbook = 1
.Workbooks.Add()
.Worksheets(1).Select()

'To display the column value row-by-row in the excel file
For intRow = 0 To ds.Tables(0).Rows.Count - 1

For intColumnValue = 0 To ds.Tables(0).Columns.Count - 1
.cells(intRow + 1, intColumnValue + 1).value.ToString()
ds.Tables(0).Rows(intRow).ItemArray(intColumnValue ).ToString()
Next

Next

.activeWorkbook().SaveAs(strDir & strFilename)
.activeWorkbook.close()
End With
Excel.Quit()
Excel = Nothing
GC.Collect()

End Sub

I know I haven't defined the filename or file directory, but I wanted to
test it first and see if it would even read the datatable I already have
established. It seems to set the Datatable to idt_final_report just
fine,
but on the very next line for Excel = CreateObject("Excel.application"),
I
get the error. Can you please explain a little more? What am I
missing?
Do you know of a link that shows how to do this? Thanks so much for
your
time.

Coleen
"Mrozu" <gr************@gmail.comwrote in message
news:11**********************@e3g2000cwe.googlegro ups.com...
Hi,
>
Excel = CreateObject("Excel.Application")
>
With Excel
.SheetsInNewWorkbook = 1
.Workbooks.Add()
.Worksheets(1).Select()
>
'For displaying the column value row-by-row in the the
excel file.
>
For intRow = 0 To ds.Tables(0).Rows.Count - 1
>
For intColumnValue = 0 To dt.Tables(0).Columns.Count - 1
.Cells(intRow + 1, intColumnValue + 1).Value =
ds.Tables(0).Rows(intRow).ItemArray(intColumnValue ).ToString
Next
>
Next
>
P
.ActiveWorkbook().SaveAs(strDir & strFileName)
>
.ActiveWorkbook.Close()
End With
Excel.Quit()
Excel = Nothing
GC.Collect()
End
>
it's a sample from my own app. it isn't very complicated so i think
that you would understand it :)
>
Mrozu
>
>
Coleen napisal(a):
Hi All :-)

I'm using .Net Framework 1.1, VB and need to be able to download the
data
from a datagrid into an Excel 2000 spreadsheet for our accounting
users.
Can anyone please point me to a good link on how to do this or give
me
some
pointers? TIA,

Coleen
>

Sep 7 '06 #7

This discussion thread is closed

Replies have been disabled for this discussion.