Exporting a vb.net datagrid to excel

6 Sep 2006 5:00 PM

Coleen

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

6 Sep 2006 7:00 PM

Mrozu

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):
Show quoteHide quote

> 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

6 Sep 2006 8:05 PM

Coleen

Thank you so much Mrozu!

I will try this - I appreciate your help :-)

Show quoteHide quote

"Mrozu" <grzesiek.mr***@gmail.com> wrote in message
news:1157569209.327404.131030@e3g2000cwe.googlegroups.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
>

7 Sep 2006 6:20 PM

Coleen

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

Show quoteHide quote

"Mrozu" <grzesiek.mr***@gmail.com> wrote in message
news:1157569209.327404.131030@e3g2000cwe.googlegroups.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
>

7 Sep 2006 7:16 PM

Mrozu

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):
Show quoteHide quote

> 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" <grzesiek.mr***@gmail.com> wrote in message
> news:1157569209.327404.131030@e3g2000cwe.googlegroups.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
> >

7 Sep 2006 7:48 PM

Mrozu

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):
Show quoteHide quote

> 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" <grzesiek.mr***@gmail.com> wrote in message
> news:1157569209.327404.131030@e3g2000cwe.googlegroups.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
> >

7 Sep 2006 8:13 PM

Coleen

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?

Show quoteHide quote

"Mrozu" <grzesiek.mr***@gmail.com> wrote in message
news:1157658518.503720.132240@m79g2000cwm.googlegroups.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()
Show quoteHide quote

> >         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" <grzesiek.mr***@gmail.com> wrote in message
> > news:1157569209.327404.131030@e3g2000cwe.googlegroups.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
> > >
>