Hi Fred

Unless I misunderstood what you said, I think this sounds a good idea for
individual cells, but I may have been a bit vague in my original post -
sorry!  When I referred to 'Cells' I actually meant 'Columns of Cells'.

The file being imported is around 70 columns wide and contains about 24,000
records.  I need to import entire columns of cells (all 24,000) by their
ordinal reference such as A, F, H rather than the individual cells, A5, F15,
H3.

Also, the Spreadsheet being imported is itself an automated export from
another system which I don't have any control over.  I can't add anything to
the file beforehand.

Regards

Robin

Hi Robin,

Two possibilities to import only the data you need:

1) Get selected columns only, using a query into the worksheet (you have
to specify a contiguous range on the worksheet, but don't have to import
all the columns). The syntax is along these lines:

SELECT F1 AS MyField1, F3 AS MyField2, F25 AS MyField3
 INTO MyNewTable
 FROM [Excel 8.0;HDR=No;database=C:\Book.xls;].[Sheet1$]
 WHERE F2 = 'Something'
;

or

SELECT Field1, Field3, Field25
 INTO MyNewTable
 FROM [Excel 8.0;HDR=Yes;database=C:\Book.xls;].[Sheet1$A2:E2000]
 WHERE F2 < 200
;

Note the default field names F1, F2... if there are no column headers in
the Excel range. You can use a range name instead of a sheet name; in
that case don't use the $.

The problem with (1) is that as far as I know it still has to pull all
the data across the network in order to decide what needs to be
imported. So:

2) Use a script on the server to run a query that extracts only the data
you need, and then just download that. This doesn't mean running Excel
or Access on the server (which might upset your netadmin people),
although it does need to be a Windows server. The script,  in any
OLE-aware language, can use the DAO or ADO libraries to run the query
and put the results into a text file, workbook or Access database.

BTW, is the 140MB spreadsheet the actual data store, or is it merely
output from a proper database? If the former, it's alarming; if the
latter, could you get the DB admins to create a query for you and give
you access to it?

--
John Nurick [Microsoft Access MVP]

Please respond in the newgroup and not by email.