VSTO 2005 - Read Range from excel

[Guest]

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

 

[Cor Ligthert [MVP]]

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

 

[Guest]

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>

 

[Peter Huang [MSFT]]

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.

 

[Guest]

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.

 

[Cor Ligthert [MVP]]

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/micr...ledb+excel&qt_g=1&searchnow=Search+this+group

I hope this helps,

Cor

 

[Guest]

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?

 

[Peter Huang [MSFT]]

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.