Import Excel Spreadsheet Cell by Cell 1

[SgtJarrow]

I have a series of Excel spreadsheets that I have to inport into my database each month. Noe of these spreadsheets are in the same format, although they all have the same type of data. What I need to be able to do is step through each cell and import the data one cell at a time. I can then check this cell and place it where I need it in the table.

I can't get the syntax to work....I have no problem opening the file, and no problem placing my focus on the first field, but I can't walk through the worksheet.

I need to be able to start at Cell A1 and go to Cell Z1 (just to make sure I get them all.....I think there are only about 20 Cells) and then move to A2 to Z2, all the way down about 50 rows or so.

If anyone can help me with the syntax for moveing from cell to cell for 26 cells, then row by row for 50 rows, I would be greatful. Thanks. Programming isn't a profession of choice.
It's a profession of calling...
"Hey Programmer, your application broke again!"

Robert L. Johnson III, A+, Network+, MCP
Access Developer/Programmer

 

[jebry]

Hi!

There's more than one way to do this, you can set up a nested loop:

For i=1 to 26
For j=1 to 50
ExcelObject.Cell(Chr$(i+??) & Format(j))
Do your stuff
Next j
Next i

The ?? is the ASCII offset to get to the capital letters. You would need to look it up since I am not sure what it is. Alternatively you could set up a constant array of the letters of the alphabet and use letterarray(i) instead. Another alternative is to just link to the worksheets. That way you can use them just like any other table in Access and any updates are automatically reflected in your database.

hth
Jeff Bridgham
bridgham@purdue.edu

 

[SgtJarrow]

With just a little bit of tweaking, I made this work! Thanks a bunch! Programming isn't a profession of choice.
It's a profession of calling...
"Hey Programmer, your application broke again!"

Robert L. Johnson III, A+, Network+, MCP
Access Developer/Programmer