Hi, I have been asked to look at this issue and I am unsure if it can be done, if it can be done then how is it possible for it to be done...
I am wanting to import data from 5 different sheets in an excel spreadsheet. All the data is on the same layout on each worksheet from a range of A21:BF42, A76:BF97 & A131:BE152.
I need to import this information on a weekly basis, to an import table in a database and then I can query the information from there. My final problem is that the excel spreadsheet that I want to extract the data from is sent from an outside source and has been password protected.
Look at "DemoVidi1A2000.zip" (attachment).
Here you have got EXCEL "Vidi_1" and Access mdb "DemoVidi1a2000.mdb".
In MDB look at table. There are a link on EXCEL table, you MUST TO DO A
RELINK THIS EXCEL TABLE, (depending on your Path), and you have got an
access table "tblVidiAcc" (empty table).
You have got a "Query1MakeTable". Run this query, and see in "tblVidiAcc".
Adapt it on your mdb.
MStef-ZG, Thanks for that example. I am still concerned about the spreadsheet being password protected and that is there a way around linking or importing data to a database table even though the spreadsheet is protected, also using code like this "DoCmd.TransferSpreadsheet acImport," only inputs the first worksheet, how can I get around this and specify which worksheet I want to import as I have 5 sheets to import from about 20 alltogether.
MStef-ZG, Thanks for the examples but its not really what I am looking for...
I have rethought the whole thing and have now got all the data into ranges on an excel spreadsheet. What I want to do now is using a form in my database, bring in each range and import it into a individual tables.
I have used this code below before to link a spreadsheet to a table which works fine but in this case I want only to import ranges of data as there are a number on the spreadsheet that all need importing to individual tables.
I asked for how much data was within your spreadsheet, if it's only 20 or so rows then why not just import the whole spreadsheet into a table and then query the "ranges" you need? Do you get what I mean?
However as you might recognise I don't specify the exact dept of the range. So I now import from beginning till end of file.
However, if I'd import a text file, I can enter the exact value range from where to start and where to finish, this might be applicable to excel too I'd have to look into that as I'm facing a simular problem atm.
Hope this helps you somewhat, I'd like to have the answer myself.