Member Avatar for G_Waddell

Hi,

I'm writing a routine to import salesorders from an Excel sheet into Sage. The Sheet is broken down by product rather than by order so I want to use OLEDB to connect and run a query against it sorting by Order Number to get the items back per order.

The issue I have is that I want to check that there is a valid worksheet in the Excel Document before proceeding. I plan in looping through the spreadsheets and check the columns in each one to ensure they are in a valid format.

In order to do this I was planning on using GetOleDbSchemaTable but I don't get any tables back... The test workbook I'm using has three sheeets in it and sheet 1 is valid.
Here is a sample of my code:

'connstr passed into function
dim conn as new data.OleDb.OleDbConnection(ConnStr)
dim Dt as datatable
conn.open()
Dt = conn.GetOleDbSchemaTable(System.Data.OleDb.OleDbSchemaGuid.Columns, New Object() {Nothing, Nothing, Nothing, "TABLE"})
IF DT.rows.count = 0 then 'always has rows.count =0
'process each table
end if

Why do I never get any rows? i think I may be getting the GetOleDbSchemaTable wrong but every example I've seen it looks ok.

  • 1 Contributor
  • 1 Reply
  • 1K Views
  • 7 Hours Discussion Span
  • Latest Post Latest Post by G_Waddell
Member Avatar for G_Waddell

Hi,

I've kind of solved it, I just don't filter for anything....

Dt = conn.GetOleDbSchemaTable(System.Data.OleDb.OleDbSchemaGuid.Columns,Nothing)
Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.