import one column of many sheets from Excel into link SQL table

[webcat]

Hi,

I'm fairly new to access and SQL, but have a background in other DB apps.

here's my problem

I need to allow users to choose a file for import (could be anywhere), then, i need to determine how many sheets are in that excel file, and import any numerics on column G from ALL of the sheets.

there could be 1 sheet, or there could be 100

there could be 1 number in column G, or there could be 10


I've seen a few posts around this and other forums about how to pull in an excel sheet, but i'm having difficulty pulling it all together.

by some chance, has anyone done this specific task before? or can anyone point me in the right direction?


As I say, i have some experience with VBA, Access and SQL, but not enough to quickly do what I need to do!

thanks for any help you can offer.


Adam.

 

[Moniker]

Look into (and by that, I mean search on) the Excel Object and how it works. Briefly, you'll need a refernce to Excel (Tools -> References in the code window and add that reference) and you'll need to reference the worksheet objects within the workbook object to perform what you want. I know I've written about how to do this before; the quickest one I could find was here, and it should give you a general reference as to how to set the connection to Excel and get at the data you need.