Hi
We are about to receive 500 excel spreadsheets from a client. The data has not been arranged in tabular form. Is it possible to import specific cells, say J10, M10, J15, M22 into an Access table using SQL or whatever? I saw a reply somewhere that indicated you had to create name ranges in excel and then import the range but that seems like a lot of work considering I'm dealing with 500 workbooks with up 8 specific worksheets in each. I'd love to be able to select specific cells from a specific spreadsheet and update an Access table.
The short answer is yes you can do what you like. How you do it depens on what you are doing and in what programme you doing it.
You can do it in Access using automation to start Excel, open the files(s) and update tables/fields with specific Cell data, or (as is my prefered method) you can run code on Excel to open the file(s) and update the Access tables using an ADO connection and/or recordsets.
I find using range names does have an advantage, but this is only practical if you define the speadsheet used youself (ie timesheets or similar), but if the spreadsheets ar received form a third party or generated from other data sources/databases then this is not practicale and would need the data to be in a standardised format.
It depends on which application you are familiar with and your level of VBA experiance.
I think that answers the question, but does it provide a solution?
MTB