VB Forum / COM / July 2003

Hi guys, I'm a newbie in VB, and I'm trying to import some data from an
Excel File into a text file. I know this can be done inside Excel, but I
need a completely automated system.

After looking through pages after pages of MS documentation, they all
suggest that I use the DataSet object to store the data, here's the code,
straight from MSDN:

-------------------- Importing into a DataSet -----------------

Dim DS As System.Data.DataSet
Dim MyCommand As System.Data.OleDb.OleDbDataAdapter
Dim MyConnection As System.Data.OleDb.OleDbConnection

MyConnection = New System.Data.OleDb.OleDbConnection( _
     "provider=Microsoft.Jet.OLEDB.4.0; " & _
     "data source=C:\myData.XLS; " & _
     "Extended Properties=Excel 8.0;")
' Select the data from Sheet1 of the workbook.
MyCommand = New System.Data.OleDb.OleDbDataAdapter( _
     "select * from [Sheet1$]", MyConnection)

DS = New System.Data.DataSet()
MyCommand.Fill(DS)
MyConnection.Close()

--------------------- Outputting into a Text File ------------------

Private Sub PrintRows(ByVal myDataSet As DataSet)
  Dim myTable As DataTable
  Dim myRow As DataRow
  Dim myColumn As DataColumn
  ' For each table in the DataSet, print the row values.
  For Each myTable in myDataSet.Tables
     For Each myRow In myTable.Rows
        For Each myColumn in myTable.Columns
           myFile.WriteLine(myRow(myColumn))        ' just assume that
myFile is opened somewhere
        Next myColumn
     Next myRow
  Next myTable
End Sub

--------------------------------------------------------------------

OK, so here's my problem. Everything is fine with reading and writing,
except that this whole process seems to randomly drop a lot of the data
inside the Excel file. For example, it NEVER reads the 1st row, just simply
ignores it and treats the 2nd row as the first one. Another more serious one
is that it seems to have some sort of pattern-matching mechanism built-in.
If on a given column, I have:

100-0
100-1
100-2
1
2
3
100-3

Then the program will ONLY read in the "1, 2, 3", or sometimes it only reads
in the "100-0, 100-1, 100-2, 100-3". I have seen both cases occuring, and
have no idea why it does that. This is VERY bad, since I have no control
over the data I'm reading in.

Is there some simple way to just read in everything without VB trying to
filter it?

Please help!! Thank you in advance!!

Steven Pu