Importing Specific Cells

[Dave_D]

I have a spreadsheet where the data resides in specific cells which needs to be imported into a newly created table. The spreadsheet is static, so I know specifically where each cells data resides. The spreadsheet has no specific heading for table columns. What I need to know, can I import specifically say "E3" into tablename.field1, then import say "G3" into tablename.field2. After row 3 in my spreadsheet, the data resides in ranges. Is this do able? I'm using the TransferSpreadsheet command.

Thank You!

[June7]

Not with TransferSpreadsheet method.

This requires opening and manipulating the spreadsheet as an object in VBA. See if this helps http://www.accessmvp.com/KDSnell/EXC...m#WriteFileRst

[CJ_London]

I have to disagree with June7, you can specify a range for importing data with transferspreadsheet - but you can't use a range for exporting data.

I can only speculate what you are trying to do but you would need to use multiple transferspreadsheets and perhaps use code to store the E3 and G3 values in temporary tables for subsequent appending or updating to the 'proper' table.

One of the tricks I've used in the past is to use transferspreadsheet to link to an excel workbook, then providing you can define in sql where your E3 and G3 values are then you can use a query with subqueries or domain functions to pick up these values

So say C3 says Date and D3 has a date value, F3 says customer and G3 has the customer name. Your data headers are on row 4 and data starts on row 5 and the first column of data is numeric

You would link without column headings so these will default to field1, field2, field3 etc

A basic select query (which you can then change to an update or append) would be

SELECT DLookup("Field4","xlsTable","Field3='Date'") as InvDate, DLookup("Field7","xlsTable","Field6='Customer'") As CustomerName, Field1, Field2, Field3....
FROM xlsTable
WHERE isnumeric(Field1)=true

However, I also agree with June7 that manipulating a spreadsheet as an object is a good way to go

[June7]

I don't think TransferSpreadsheet would work with discontinuous range, but could be wrong about that.

And if the spreadsheet structure is simple enough, linking is definitely an option.