I want to selectively copy Excel worksheets and columns to a SQL 2000
database using VBA. Specifically, I want to copy data from any worksheet
whose name matches a table in the database and copy the data whose column
header matches a column name in the table. I ignore worksheet names and
column names that don't match.

The program I have written to this point generates DTS tasks to copy
worksheets to corresponding tables, but it assumes the worksheet has a
column that matches every column in the table. However, I have no control
over the contents of the customer's spreadsheet and it may contain more or
fewer columns than in my table. Is there some way I can get the column
names from the spreadsheet so I can programatically build the proper
DataPump task?

TIA,
William Schmidt

William,

Assuming that you want to avoid Excel Automation, you could retrieve the
column names from the source worksheets using ADO or ADOX, assuming the
source data is in the tabular format that the Excel driver recognizes as a
table. You could retrieve an empty recordset using ADO and check its Fields
collection for column names.

-Doug

--
Douglas Laudenschlager
Microsoft SQL Server documentation team
Redmond, Washington, USA

This posting is provided "AS IS" with no warranties, and confers no rights.

"Wm" <wschmidt (AT) egginc_SpamBlocker_ (DOT) com> wrote

Doug,

Sorry for the delayed response -- I was out last week. At first I was of
the mind that DTS was exactly what I needed to use. But as I got into the
details of my application, I gave up on it. I rewrote this function using
ADO, exactly as you suggest. It worked out even better because I could
reuse some of my existing code for checking data validity and dependencies.

Thanks for responding. Microsoft notes is a great resource when the
documentation stops short. Oops -- no offense intended!

William

"Douglas Laudenschlager [MS]" <douglasl (AT) online (DOT) microsoft.com> wrote in
message news:Ocj7JXxUEHA.3692 (AT) TK2MSFTNGP09 (DOT) phx.gbl...

"Douglas Laudenschlager [MS]" wrote ...

Preferable IMO for the OP to use ADO's OpenSchema method, specifying
adSchemaColumns, for both Excel *and* SQL Server and comparing the two
recordsets e.g. by filtering on TABLE_NAME and COLUMN_NAME. This
approach requires only two round trips i.e. one hit to each data
source.

Even better, as the OP is only interested in worksheets, could be to
use the old ODBC Microsoft Excel Driver (rather than the newer OLE DB
Provider for Jet) for the Excel source. Then, using a recordset for
tables, separate from one for columns, they could filter for
TABLE_TYPE='SYSTEM TABLE' to filter out workbook-level defined Names
('named ranges').

Jamie.

--