What is the easiest way to loop through a set of rows in excel and add the
contents to a text file?

Philip,

Get the Excel file using OleDb in a dataset and than process that.

http://support.microsoft.com/default.aspx?scid=kb;EN-US;316934

I hope this helps,

Cor

Show quoteHide quote

I get the error : Column name 'REFERENCE' is defined for different mapping
types.

My code:

Public Function getIndexesFromQueryResult(ByVal xmldoc As XmlDocument) As
XmlDocument

        Dim XmlString As String
        XmlString = "<?xml version=""1.0"" encoding=""UTF-8"" ?>" & vbCrLf

        Dim reader As New XmlNodeReader(xmldoc)
        Dim ds As New DataSet
        ds.ReadXml(reader)
        Dim dr As DataRow
        For Each dr In ds.Tables(3).Rows
            XmlString &= "<INDEX NAME=""" & dr("name") & """>" & dr("data")
& "</INDEX>"
        Next

        Dim IndexXml As New XmlDocument
        IndexXml.InnerXml = XmlString
        Return IndexXml
    End Function

My xml file: (a file is encoded in mime where it says...)

<?xml version="1.0" encoding="UTF-8"?><RESPONSE
XMLID="b703a160-db69-432e-9e06-4615c192e3d9"><SUCCESS REQUESTID="0"
COMMAND="LOGIN" /><SUCCESS REQUESTID="1" COMMAND="DOCUMENT"><DOCUMENT
ID="$(#STANDARD)\TROOST,00000008,002" FIELDCOUNT="3" CREATION="1139911549"
EDITED="1141917788" ARCHIVED="1"><FIELD ID="10" NAME="DOSSIERNR"
TYPE="STRING" USE="USER" CODE="ANSI" ATTRIB="FieldID=1001"
SEGMID="0"><DATA><![CDATA[test]]></DATA></FIELD><FIELD ID="11" NAME="NAAM"
TYPE="STRING" USE="USER" CODE="ANSI" ATTRIB="FieldID=1002"
SEGMID="1"><DATA><![CDATA[test]]></DATA></FIELD><FIELD ID="12" NAME="2001"
TYPE="BLOB" USE="USER" CODE="BASE64" REFERENCE="0" ATTRIB="FieldID=2001"
SEGMID="2"><BLOBNAME>install.log</BLOBNAME><FILENAME>D:\EASYWARE\XMLWorkFolder\srvB7E.log</FILENAME><ORGFILENAME>install.log</ORGFILENAME><FILESIZE>86473</FILESIZE><DATETIME>1141989532</DATETIME><MD5>ed4354d70d59831a0bb16a024d4bed1e</MD5><REFERENCE>ed4354d70d59831a0bb16a024d4bed1e</REFERENCE><BLOBTYPE>unknown</BLOBTYPE><DATA><![CDATA[...==]]></DATA></FIELD></DOCUMENT></SUCCESS></RESPONSE>

Show quoteHide quote

Hi Philip,

Based on my understanding, you are following Cor's suggestion that use the
ADO.NET to retrieve data from a Excel xls file.
So I just have some confusion.
What is your goal?
If you want to get the index as a column, we can just select index from
excel file.
If I have any misunderstanding, please feel free to post here.


Best regards,

Peter Huang
Microsoft Online Partner Support

Get Secure! - www.microsoft.com/security
This posting is provided "AS IS" with no warranties, and confers no rights.

Peter,

I am following Cor's suggestion. Only I ran into an exception which I do not
know how to fix. If you can help me find the cause of the exception that
would be great.

Show quoteHide quote

Philip,

I was a while active with these problems, then I got the idea that Paul
Clement made a hobby from it.

I have not seen Paul for some days now.

This is a sample I once made myself from the complex of all those rows in
the sample given.

\\\
Dim ConnectionString As String
ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
      "Data Source=c:\test1\myExcel.xls;" & _
       "Extended Properties=""Excel 8.0;HDR=NO"""
Dim Conn As New System.Data.OleDb.OleDbConnection(ConnectionString)
Dim da As New System.Data.OleDb.OleDbDataAdapter _
  ("Select * from [Sheet1$]", Conn)
Dim ds As New DataSet
da.Fill(ds, "Sheet1")
///

If you want more, google than in this newsgroup and/or the newsgroup General
like this.

http://groups.google.com/group/microsoft.public.dotnet.languages.vb/search?group=microsoft.public.dotnet.languages.vb&q=paul+clement+oledb+excel&qt_g=1&searchnow=Search+this+group

I hope this helps,

Cor

Thanks.

I will try this immediately, however I already have a question waiting ;-)

Do I have to save the workbook to file first and then read it from file? Or
can I put the current workbook in the connectionstring?

Show quoteHide quote

Hi Philip,

Yes, you need to save the workbook first before you want to read it with
ASO.NET.

Also it seems that I am working with you on a related issue in the
newsgroup below.
Subject: Re: Create application to extract data from excel sheet
Newsgroups: microsoft.public.dotnet.languages.vb

If the issue is related, we can discuss together that thread.

Thanks!

Best regards,

Peter Huang
Microsoft Online Partner Support

Get Secure! - www.microsoft.com/security
This posting is provided "AS IS" with no warranties, and confers no rights.

Create application to extract data from excel sheet
How can we mail some file without mentioning the SMTP server addre
Treeview population directly from MSAccess
How to create a Service to Log Application Names
Re: "Visual Web Developer does not support creating Web sites on a
Macros too slow !
Datagrid with comboboxes
storing mysql data to a random access file
debug problem
How to have the same info on two menus