Data Access Problems with Excel and DAO

[wallas]

I need to import data from excel, process it and write it to a SQL server 7 table.

Problems:
1. The DAO control indicates that i have over a thousand rows of data in the spreadsheet than i actually have. ihave only got 210 records, so i'm baffled

2. For some reason i keep getting errors that imply that there are NULL values in the spreadsheet. this is absolutely untrue.

3. I tried using ADO because i understand DAOs have limitations, but no joy.

I have only just started programming in VB and this is my first project. Someone please Help

 

[siqual]

A couple of things you might try. A caveat or two: As I am sure you are most painfully aware there are several approaches to almost any given problem. What works depends on many different things as well as a healthy dose of experience. The solution you chose may not be the most efficiency or elegant, but it is one you are familiar with and you know from experiece works.

OK, with that in mind. Make sure you have set up the reference to Excel (do this from the menu bar Project/References...). Once you have the reference to the excel library you can then declare a variable in the subroutine that is processing the data to be a spreadsheet or for that matter any part of a spreadsheet ( range, cell, etc.) From there you can use almost any of the spreadsheet functions/properties,etc. For example the CurrentRegion property or perhaps something along the lines of Range("A1",Range("A1&quot.End(xldown)) which should select all the cells from A1 to the last cell in the column. You can count the number of rows with Range("A1",Range("A1&quot.End(xldown)).count. This value can be assigned to a counter and used in a for next loop. It sounds like to me you need to process the data a cell at a time, yes?? (You can also get the row count from ActiveCell.CurrentRegion.Rows.Count)

Hope this helps.


ttfn
dan Dan Grogan
dan@siqual.com

"Absit prudentia nil rei publicae profitur."
Without common sense you ain't gonna have nothing.