dcsimg
Results 1 to 13 of 13

Thread: Export Dataset to Excel Attachment

  1. #1

    Thread Starter
    Member
    Join Date
    Aug 2008
    Posts
    44

    Question Export Dataset to Excel Attachment

    Hi all,

    I need to give users of my asp.net/vb.net web application the ability to click a button which will then send them an e-mail with the contents of a SQL query attached as an Excel e-mail attachment.

    I can manage all of this apart from getting the contents of a VB dataset object into an Excel file on my server.

    I know how to export a gridview to Excel at the client end, but my boss has been adamant that he wants the button to send an e-mail with the results attached - I have no need to display a gridview at all.

    Can anyone help me in exporting the contents of a datareader or dataset directly into an Excel file at the server side?

  2. #2
    Super Moderator Shaggy Hiker's Avatar
    Join Date
    Aug 2002
    Location
    Idaho
    Posts
    34,918

    Re: Export Dataset to Excel Attachment

    I'm not sure about the serverside, but over in the .NET codebank I have a class that exports datatables and possible datareaders (or maybe it was dataviews) to Excel. It would require that the server have the PIAs on it, though. I don't know that there is any way around that, though, since the PIA's are where you get the Excel types from.
    My usual boring signature: Nothing

  3. #3

  4. #4
    I'm about to be a PowerPoster! kleinma's Avatar
    Join Date
    Nov 2001
    Location
    NJ - USA (Near NYC)
    Posts
    23,383

    Re: Export Dataset to Excel Attachment

    Quote Originally Posted by Shaggy Hiker View Post
    I'm not sure about the serverside, but over in the .NET codebank I have a class that exports datatables and possible datareaders (or maybe it was dataviews) to Excel. It would require that the server have the PIAs on it, though. I don't know that there is any way around that, though, since the PIA's are where you get the Excel types from.
    You would also actually need excel on the server, since the PIAs are just the bridge between the actual excel app and the web app.

    An alternative if this is not possible, would be to output them as CSV files, which can be opened in excel. This can be done with no additional references.

  5. #5
    Karen Payne MVP kareninstructor's Avatar
    Join Date
    Jun 2008
    Location
    Oregon
    Posts
    6,523

    Re: Export Dataset to Excel Attachment

    Going in the direction of what kleinma suggested take a look at the following method.

    http://blogs.msdn.com/b/bethmassi/ar...nq-to-xml.aspx

    You create an xml version of an excel file which is your template. Once you have this template open it in notepad, copy the contents to your form (see example below) Make changes as per the article above. Beth uses SQL-Server as a data source while the example below obtains data from MS-Access or a text file to build the xml representation of an Excel 2007 file. If you need a working example I can down grade my VS2010 example to VS2008, let me know.

  6. #6
    Karen Payne MVP kareninstructor's Avatar
    Join Date
    Jun 2008
    Location
    Oregon
    Posts
    6,523

    Re: Export Dataset to Excel Attachment

    Code:
    Imports <xmlns="urn:schemas-microsoft-com:office:spreadsheet">
    Imports <xmlns:o="urn:schemas-microsoft-com:office:office">
    Imports <xmlns:x="urn:schemas-microsoft-com:office:excel">
    Imports <xmlns:ss="urn:schemas-microsoft-com:office:spreadsheet">
    Imports <xmlns:html="http://www.w3.org/TR/REC-html40">
    Public Class Form1
        Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
            Dim Lines = _
            ( _
                From line In IO.File.ReadAllLines("NamesByComma.txt") _
                Where line.Length > 0 _
                Let Items = line.Split(","c) _
                Select New With {.Name = Items(0), .State = Items(1)} _
            ).ToList
    
            Dim Persons = From P In Lines Select _
                          <Row>
                              <Cell><Data ss:Type="String"><%= P.Name %></Data></Cell>
                              <Cell><Data ss:Type="String"><%= P.State %></Data></Cell>
                          </Row>
    
            Dim sheet = _
            <?xml version="1.0"?>
            <?mso-application progid="Excel.Sheet"?>
            <Workbook xmlns="urn:schemas-microsoft-com:office:spreadsheet"
                xmlns:o="urn:schemas-microsoft-com:office:office"
                xmlns:x="urn:schemas-microsoft-com:office:excel"
                xmlns:ss="urn:schemas-microsoft-com:office:spreadsheet"
                xmlns:html="http://www.w3.org/TR/REC-html40">
                <DocumentProperties xmlns="urn:schemas-microsoft-com:office:office">
                    <Author>kevininstructor</Author>
                    <LastAuthor>kevininstructor</LastAuthor>
                    <Created>2011-06-22T02:26:45Z</Created>
                    <Version>12.00</Version>
                </DocumentProperties>
                <ExcelWorkbook xmlns="urn:schemas-microsoft-com:office:excel">
                    <WindowHeight>12300</WindowHeight>
                    <WindowWidth>21075</WindowWidth>
                    <WindowTopX>240</WindowTopX>
                    <WindowTopY>75</WindowTopY>
                    <ProtectStructure>False</ProtectStructure>
                    <ProtectWindows>False</ProtectWindows>
                </ExcelWorkbook>
                <Styles>
                    <Style ss:ID="Default" ss:Name="Normal">
                        <Alignment ss:Vertical="Bottom"/>
                        <Borders/>
                        <Font ss:FontName="Calibri" x:Family="Swiss" ss:Size="11" ss:Color="#000000"/>
                        <Interior/>
                        <NumberFormat/>
                        <Protection/>
                    </Style>
                    <Style ss:ID="s62">
                        <Font ss:FontName="Calibri" x:Family="Swiss" ss:Size="11" ss:Color="#000000"
                            ss:Bold="1"/>
                    </Style>
                </Styles>
                <Worksheet ss:Name="People">
                    <Table ss:ExpandedColumnCount="2" ss:ExpandedRowCount=<%= Persons.Count + 1 %> x:FullColumns="1"
                        x:FullRows="1" ss:DefaultRowHeight="15">
                        <Row>
                            <Cell ss:StyleID="s62"><Data ss:Type="String">Name</Data></Cell>
                            <Cell ss:StyleID="s62"><Data ss:Type="String">State</Data></Cell>
                        </Row>
                        <%= Persons %>
                    </Table>
                    <WorksheetOptions xmlns="urn:schemas-microsoft-com:office:excel">
                        <PageSetup>
                            <Header x:Margin="0.3"/>
                            <Footer x:Margin="0.3"/>
                            <PageMargins x:Bottom="0.75" x:Left="0.7" x:Right="0.7" x:Top="0.75"/>
                        </PageSetup>
                        <Print>
                            <ValidPrinterInfo/>
                            <HorizontalResolution>600</HorizontalResolution>
                            <VerticalResolution>600</VerticalResolution>
                        </Print>
                        <Selected/>
                        <Panes>
                            <Pane>
                                <Number>3</Number>
                                <ActiveRow>4</ActiveRow>
                                <ActiveCol>1</ActiveCol>
                            </Pane>
                        </Panes>
                        <ProtectObjects>False</ProtectObjects>
                        <ProtectScenarios>False</ProtectScenarios>
                    </WorksheetOptions>
                </Worksheet>
                <Worksheet ss:Name="Sheet2">
                    <Table ss:ExpandedColumnCount="1" ss:ExpandedRowCount="1" x:FullColumns="1"
                        x:FullRows="1" ss:DefaultRowHeight="15">
                    </Table>
                    <WorksheetOptions xmlns="urn:schemas-microsoft-com:office:excel">
                        <PageSetup>
                            <Header x:Margin="0.3"/>
                            <Footer x:Margin="0.3"/>
                            <PageMargins x:Bottom="0.75" x:Left="0.7" x:Right="0.7" x:Top="0.75"/>
                        </PageSetup>
                        <ProtectObjects>False</ProtectObjects>
                        <ProtectScenarios>False</ProtectScenarios>
                    </WorksheetOptions>
                </Worksheet>
                <Worksheet ss:Name="Sheet3">
                    <Table ss:ExpandedColumnCount="1" ss:ExpandedRowCount="1" x:FullColumns="1"
                        x:FullRows="1" ss:DefaultRowHeight="15">
                    </Table>
                    <WorksheetOptions xmlns="urn:schemas-microsoft-com:office:excel">
                        <PageSetup>
                            <Header x:Margin="0.3"/>
                            <Footer x:Margin="0.3"/>
                            <PageMargins x:Bottom="0.75" x:Left="0.7" x:Right="0.7" x:Top="0.75"/>
                        </PageSetup>
                        <ProtectObjects>False</ProtectObjects>
                        <ProtectScenarios>False</ProtectScenarios>
                    </WorksheetOptions>
                </Worksheet>
            </Workbook>
    
            sheet.Save("Persons.xml")
        End Sub

  7. #7
    Karen Payne MVP kareninstructor's Avatar
    Join Date
    Jun 2008
    Location
    Oregon
    Posts
    6,523

    Re: Export Dataset to Excel Attachment

    Code:
        ''' <summary>
        ''' Build Excel worksheet from tab delimited text file
        ''' </summary>
        ''' <param name="sender"></param>
        ''' <param name="e"></param>
        ''' <remarks></remarks>
        Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button2.Click
            Dim Lines = _
            ( _
                From line In IO.File.ReadAllLines("NamesByTab.txt") _
                Where line.Length > 0 _
                Let Items = line.Split(CChar(System.Text.RegularExpressions.Regex.Unescape("\t"))) _
                Select New With {.Name = Items(0), .State = Items(1)} _
            ).ToList
    
            Dim Persons = From P In Lines Select _
                          <Row>
                              <Cell><Data ss:Type="String"><%= P.Name %></Data></Cell>
                              <Cell><Data ss:Type="String"><%= P.State %></Data></Cell>
                          </Row>
    
            Dim sheet = _
            <?xml version="1.0"?>
            <?mso-application progid="Excel.Sheet"?>
            <Workbook xmlns="urn:schemas-microsoft-com:office:spreadsheet"
                xmlns:o="urn:schemas-microsoft-com:office:office"
                xmlns:x="urn:schemas-microsoft-com:office:excel"
                xmlns:ss="urn:schemas-microsoft-com:office:spreadsheet"
                xmlns:html="http://www.w3.org/TR/REC-html40">
                <DocumentProperties xmlns="urn:schemas-microsoft-com:office:office">
                    <Author>kevininstructor</Author>
                    <LastAuthor>kevininstructor</LastAuthor>
                    <Created>2011-06-22T02:26:45Z</Created>
                    <Version>12.00</Version>
                </DocumentProperties>
                <ExcelWorkbook xmlns="urn:schemas-microsoft-com:office:excel">
                    <WindowHeight>12300</WindowHeight>
                    <WindowWidth>21075</WindowWidth>
                    <WindowTopX>240</WindowTopX>
                    <WindowTopY>75</WindowTopY>
                    <ProtectStructure>False</ProtectStructure>
                    <ProtectWindows>False</ProtectWindows>
                </ExcelWorkbook>
                <Styles>
                    <Style ss:ID="Default" ss:Name="Normal">
                        <Alignment ss:Vertical="Bottom"/>
                        <Borders/>
                        <Font ss:FontName="Calibri" x:Family="Swiss" ss:Size="11" ss:Color="#000000"/>
                        <Interior/>
                        <NumberFormat/>
                        <Protection/>
                    </Style>
                    <Style ss:ID="s62">
                        <Font ss:FontName="Calibri" x:Family="Swiss" ss:Size="11" ss:Color="#000000"
                            ss:Bold="1"/>
                    </Style>
                </Styles>
                <Worksheet ss:Name="People">
                    <Table ss:ExpandedColumnCount="2" ss:ExpandedRowCount=<%= Persons.Count + 1 %> x:FullColumns="1"
                        x:FullRows="1" ss:DefaultRowHeight="15">
                        <Row>
                            <Cell ss:StyleID="s62"><Data ss:Type="String">Name</Data></Cell>
                            <Cell ss:StyleID="s62"><Data ss:Type="String">State</Data></Cell>
                        </Row>
                        <%= Persons %>
                    </Table>
                    <WorksheetOptions xmlns="urn:schemas-microsoft-com:office:excel">
                        <PageSetup>
                            <Header x:Margin="0.3"/>
                            <Footer x:Margin="0.3"/>
                            <PageMargins x:Bottom="0.75" x:Left="0.7" x:Right="0.7" x:Top="0.75"/>
                        </PageSetup>
                        <Print>
                            <ValidPrinterInfo/>
                            <HorizontalResolution>600</HorizontalResolution>
                            <VerticalResolution>600</VerticalResolution>
                        </Print>
                        <Selected/>
                        <Panes>
                            <Pane>
                                <Number>3</Number>
                                <ActiveRow>4</ActiveRow>
                                <ActiveCol>1</ActiveCol>
                            </Pane>
                        </Panes>
                        <ProtectObjects>False</ProtectObjects>
                        <ProtectScenarios>False</ProtectScenarios>
                    </WorksheetOptions>
                </Worksheet>
                <Worksheet ss:Name="Sheet2">
                    <Table ss:ExpandedColumnCount="1" ss:ExpandedRowCount="1" x:FullColumns="1"
                        x:FullRows="1" ss:DefaultRowHeight="15">
                    </Table>
                    <WorksheetOptions xmlns="urn:schemas-microsoft-com:office:excel">
                        <PageSetup>
                            <Header x:Margin="0.3"/>
                            <Footer x:Margin="0.3"/>
                            <PageMargins x:Bottom="0.75" x:Left="0.7" x:Right="0.7" x:Top="0.75"/>
                        </PageSetup>
                        <ProtectObjects>False</ProtectObjects>
                        <ProtectScenarios>False</ProtectScenarios>
                    </WorksheetOptions>
                </Worksheet>
                <Worksheet ss:Name="Sheet3">
                    <Table ss:ExpandedColumnCount="1" ss:ExpandedRowCount="1" x:FullColumns="1"
                        x:FullRows="1" ss:DefaultRowHeight="15">
                    </Table>
                    <WorksheetOptions xmlns="urn:schemas-microsoft-com:office:excel">
                        <PageSetup>
                            <Header x:Margin="0.3"/>
                            <Footer x:Margin="0.3"/>
                            <PageMargins x:Bottom="0.75" x:Left="0.7" x:Right="0.7" x:Top="0.75"/>
                        </PageSetup>
                        <ProtectObjects>False</ProtectObjects>
                        <ProtectScenarios>False</ProtectScenarios>
                    </WorksheetOptions>
                </Worksheet>
            </Workbook>
    
            sheet.Save("Persons1.xml")
        End Sub
    
        Private Sub cmdDataTableSource_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmdDataTableSource.Click
    
            Dim Persons = From P In CType(DataGridView1.DataSource, DataTable).AsEnumerable Select _
                          <Row>
                              <Cell><Data ss:Type="String"><%= P.Field(Of String)("FirstName") %></Data></Cell>
                              <Cell><Data ss:Type="String"><%= P.Field(Of String)("LastName") %></Data></Cell>
                          </Row>
    
            Dim sheet = _
            <?xml version="1.0"?>
            <?mso-application progid="Excel.Sheet"?>
            <Workbook xmlns="urn:schemas-microsoft-com:office:spreadsheet"
                xmlns:o="urn:schemas-microsoft-com:office:office"
                xmlns:x="urn:schemas-microsoft-com:office:excel"
                xmlns:ss="urn:schemas-microsoft-com:office:spreadsheet"
                xmlns:html="http://www.w3.org/TR/REC-html40">
                <DocumentProperties xmlns="urn:schemas-microsoft-com:office:office">
                    <Author>kevininstructor</Author>
                    <LastAuthor>kevininstructor</LastAuthor>
                    <Created>2011-06-22T02:26:45Z</Created>
                    <Version>12.00</Version>
                </DocumentProperties>
                <ExcelWorkbook xmlns="urn:schemas-microsoft-com:office:excel">
                    <WindowHeight>12300</WindowHeight>
                    <WindowWidth>21075</WindowWidth>
                    <WindowTopX>240</WindowTopX>
                    <WindowTopY>75</WindowTopY>
                    <ProtectStructure>False</ProtectStructure>
                    <ProtectWindows>False</ProtectWindows>
                </ExcelWorkbook>
                <Styles>
                    <Style ss:ID="Default" ss:Name="Normal">
                        <Alignment ss:Vertical="Bottom"/>
                        <Borders/>
                        <Font ss:FontName="Calibri" x:Family="Swiss" ss:Size="11" ss:Color="#000000"/>
                        <Interior/>
                        <NumberFormat/>
                        <Protection/>
                    </Style>
                    <Style ss:ID="s62">
                        <Font ss:FontName="Calibri" x:Family="Swiss" ss:Size="11" ss:Color="#000000"
                            ss:Bold="1"/>
                    </Style>
                </Styles>
                <Worksheet ss:Name="People">
                    <Table ss:ExpandedColumnCount="2" ss:ExpandedRowCount=<%= Persons.Count + 1 %> x:FullColumns="1"
                        x:FullRows="1" ss:DefaultRowHeight="15">
                        <Row>
                            <Cell ss:StyleID="s62"><Data ss:Type="String">First</Data></Cell>
                            <Cell ss:StyleID="s62"><Data ss:Type="String">Last</Data></Cell>
                        </Row>
                        <%= Persons %>
                    </Table>
                    <WorksheetOptions xmlns="urn:schemas-microsoft-com:office:excel">
                        <PageSetup>
                            <Header x:Margin="0.3"/>
                            <Footer x:Margin="0.3"/>
                            <PageMargins x:Bottom="0.75" x:Left="0.7" x:Right="0.7" x:Top="0.75"/>
                        </PageSetup>
                        <Print>
                            <ValidPrinterInfo/>
                            <HorizontalResolution>600</HorizontalResolution>
                            <VerticalResolution>600</VerticalResolution>
                        </Print>
                        <Selected/>
                        <Panes>
                            <Pane>
                                <Number>3</Number>
                                <ActiveRow>4</ActiveRow>
                                <ActiveCol>1</ActiveCol>
                            </Pane>
                        </Panes>
                        <ProtectObjects>False</ProtectObjects>
                        <ProtectScenarios>False</ProtectScenarios>
                    </WorksheetOptions>
                </Worksheet>
                <Worksheet ss:Name="Sheet2">
                    <Table ss:ExpandedColumnCount="1" ss:ExpandedRowCount="1" x:FullColumns="1"
                        x:FullRows="1" ss:DefaultRowHeight="15">
                    </Table>
                    <WorksheetOptions xmlns="urn:schemas-microsoft-com:office:excel">
                        <PageSetup>
                            <Header x:Margin="0.3"/>
                            <Footer x:Margin="0.3"/>
                            <PageMargins x:Bottom="0.75" x:Left="0.7" x:Right="0.7" x:Top="0.75"/>
                        </PageSetup>
                        <ProtectObjects>False</ProtectObjects>
                        <ProtectScenarios>False</ProtectScenarios>
                    </WorksheetOptions>
                </Worksheet>
                <Worksheet ss:Name="Sheet3">
                    <Table ss:ExpandedColumnCount="1" ss:ExpandedRowCount="1" x:FullColumns="1"
                        x:FullRows="1" ss:DefaultRowHeight="15">
                    </Table>
                    <WorksheetOptions xmlns="urn:schemas-microsoft-com:office:excel">
                        <PageSetup>
                            <Header x:Margin="0.3"/>
                            <Footer x:Margin="0.3"/>
                            <PageMargins x:Bottom="0.75" x:Left="0.7" x:Right="0.7" x:Top="0.75"/>
                        </PageSetup>
                        <ProtectObjects>False</ProtectObjects>
                        <ProtectScenarios>False</ProtectScenarios>
                    </WorksheetOptions>
                </Worksheet>
            </Workbook>
    
            sheet.Save("Persons_dt.xml")
        End Sub
    
        Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
            Using cn As New OleDb.OleDbConnection With
                {
                    .ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=People.accdb"
                }
                Using cmd As New OleDb.OleDbCommand With
                    {
                        .Connection = cn,
                        .CommandText = "SELECT TOP 10 * FROM Person"
                    }
                    Dim dt As New DataTable
                    cn.Open()
                    dt.Load(cmd.ExecuteReader)
                    TextBox1.Text = dt.Rows.Count.ToString
                    DataGridView1.SuspendLayout()
                    Try
                        DataGridView1.DataSource = dt
                    Finally
                        DataGridView1.ResumeLayout()
                    End Try
                End Using
            End Using
        End Sub
    End Class

  8. #8
    ASP.NET Moderator gep13's Avatar
    Join Date
    Nov 2004
    Location
    The Granite City
    Posts
    21,966

    Re: Export Dataset to Excel Attachment

    It is always weird to me to see XML written directly into the code like that

    Gary

  9. #9
    Frenzied Member brin351's Avatar
    Join Date
    Mar 2007
    Location
    Land Down Under
    Posts
    1,293

    Re: Export Dataset to Excel Attachment

    I had to do the same thing but ftp the excell file from server to server. I didn't want to install excel on the server so the way I hacked around that was to have a blank excell file setup the way I needed in a folder, did a file.copy, dumped the data in the columns, saved it and ftp it.

    I'd have to dig around to find that code but can do if you need it.
    The problem with computers is their nature is pure logic. Just once I'd like my computer to do something deluded.

  10. #10

    Thread Starter
    Member
    Join Date
    Aug 2008
    Posts
    44

    Re: Export Dataset to Excel Attachment

    Quote Originally Posted by brin351 View Post
    I had to do the same thing but ftp the excell file from server to server. I didn't want to install excel on the server so the way I hacked around that was to have a blank excell file setup the way I needed in a folder, did a file.copy, dumped the data in the columns, saved it and ftp it.

    I'd have to dig around to find that code but can do if you need it.
    That sounds like it could be very helpful. If you wouldn't mind digging out the code, that'd be good!

  11. #11
    Frenzied Member brin351's Avatar
    Join Date
    Mar 2007
    Location
    Land Down Under
    Posts
    1,293

    Re: Export Dataset to Excel Attachment

    I had a quick hunt for it but I think it's on another computer. It's the weekend and am about to go out so I'll get it for you tomorrow.
    The problem with computers is their nature is pure logic. Just once I'd like my computer to do something deluded.

  12. #12
    Frenzied Member brin351's Avatar
    Join Date
    Mar 2007
    Location
    Land Down Under
    Posts
    1,293

    Re: Export Dataset to Excel Attachment

    Here is the code, I edited it to remove stuff that is irrelevant to you so hopefully it makes sence. The sub expects that your excel file (copied) is ready to go so just pass in your dataReader.

    The query (strSQL) expects the excell file has a single sheet with the first row has column names. Because excel determines the dataType from the first few rows of data entered I populated the 2nd row of the excell sheet with appropate data and therefore the 1st row read from the dataReader does an update of this row/data, subsequent rows do inserts.... > long text field could throw errors if the first few rows where dataTyped as short text, so the first row of data in the excel file (second row after column names) had a few hundred chars of text in that field/column.

    That's about it, any questions I'm happy to help...

    Code:
        Sub writeToExcel(ByVal reader As OleDbDataReader)
    
    
            Dim connStr As String = "Provider=Microsoft.Jet.Oledb.4.0;Data Source=" & _
            Server.MapPath("pathToExcelFile.xls") & ";Extended Properties=""Excel 8.0;HDR=YES"""
            Dim strSQL As String
    
            Dim dbConn As New OleDbConnection(connStr)
    
            Try
    
    
                dbConn.Open()
    
                Dim i As Integer = 0
                While reader.Read
    
    
                    If i = 0 Then
                        'this is first record so update first row in excel with this record ---- Note WHERE clause > First column in excel file is "Photo_No", first record of this column : value is "deleteThis"
                        strSQL = "UPDATE [sheet1$] SET [Photo_No] = @photo, [Category (refer Catergory Index)] = @category, " & _
                        " [SellingPrice ($)] = @price, [Qualifier (each, ono)] = @priceAdd, [Ad Heading] = @heading, [Ad Text] = @text " & _
                        " WHERE [Photo_No] = 'deleteThis'"
                        Dim cmd2 As New OleDbCommand(strSQL, dbConn)
                        cmd2.Parameters.AddWithValue("@photo", reader.Item("ID").ToString & "_" & newPhotoName)
                        cmd2.Parameters.AddWithValue("@category", category)
                        cmd2.Parameters.AddWithValue("@price", reader.Item("price").ToString)
                        cmd2.Parameters.AddWithValue("@priceAdd", reader.Item("priceAdd").ToString)
                        cmd2.Parameters.AddWithValue("@heading", head)
                        cmd2.Parameters.AddWithValue("@text", txt)
                        cmd2.ExecuteNonQuery()
    
                    Else
    
                        strSQL = "INSERT INTO [sheet1$] ([Photo_No], [Category (refer Catergory Index)], [SellingPrice ($)], [Qualifier (each, ono)], [Ad Heading], [Ad Text])" & _
                        " values (@photo, @category, @price, @priceAdd, @heading, @text) "
                        Dim cmd As New OleDbCommand(strSQL, dbConn)
                        cmd.Parameters.AddWithValue("@photo", reader.Item("ID").ToString & "_" & newPhotoName)
                        cmd.Parameters.AddWithValue("@category", category)
                        cmd.Parameters.AddWithValue("@price", reader.Item("price").ToString)
                        cmd.Parameters.AddWithValue("@priceAdd", reader.Item("priceAdd").ToString)
                        cmd.Parameters.AddWithValue("@heading", head)
                        cmd.Parameters.AddWithValue("@text", txt)
                        cmd.ExecuteNonQuery()
    
                    End If
    
                    i += 1
    
    
                End While
    
            Catch ex As Exception
    
            End Try
    
    
            dbConn.Close()
    
    
        End Sub
    Last edited by brin351; Jul 16th, 2012 at 03:05 AM.
    The problem with computers is their nature is pure logic. Just once I'd like my computer to do something deluded.

  13. #13

    Thread Starter
    Member
    Join Date
    Aug 2008
    Posts
    44

    Re: Export Dataset to Excel Attachment

    Thanks Brin351. In the meanwhile, I've gone with dumping the results into a plain text csv file, but if the boss doesn't go for it, I'll be back to try out your code.

    Thanks for your help, everyone.

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Featured


Click Here to Expand Forum to Full Width