Importing more than one Excel FILE into Access

[Ricky1946]

Hello,
I need to import several Excel spreadsheet FILES into one Access database. Is it possible to differentiate between them in the database? Perhaps with VBA coding.
Can anybody give me an answer?
Regards
Ian

"To say 'thankyou' encourages others."

 

[johndweidauer]

yes.

create a folder for an ExcelImport Queue. everytime you want to import the excel files, iterate the files in that folder checking if they are .xls then perform the import. if you dont know the code, look it up, a ton of examples out there.

to do this, you will use the "File System Object" and the DoCmd function

 

[smitan]

Look up Threads 181-615452 and 181-608592
Could not find out how to make threadnumbers to a link, so use 'search on threadnumber' in stead.
Good Luck
Hans

 

[Ricky1946]

Thanks smitan,
I think that I should elaborate on the problem that I have. I have several Excel Workbooks that the user will need to import, at different times, into one Access database to modify and create reports. Each workbook has several worksheets.
These workbooks have to relate to a table previously created which contains the field names of Project Name, Project Number, Customer Name, Date of Start, Date of Completion etc. (these details will ideally come from the heading of the workbook concerned). The primary key of the created table has to match the foreign key of the imported table in order for the relationship to work – but the imported workbook does not contain the foreign key information.
As if that is not enough of a problem the user has to be able to carry out this function on the click of a few buttons. The user may or may not have much experience of the workings of Access.
I am sure that there is a way over this using VBA but at this stage I cannot get my head around it.
Can you help?


"To say 'thankyou' encourages others."

 

[smitan]

First of all I think the future is for all users to work in a multi-user (i.e. split into FE & BE) database. But that doesn't help you now

So, to get things straight, you have two tables, one tblProject with primkey projectnumber and one tblWorkBook with 'records' containing this projectnumber?
Please give some more info on tablenames and fieldnames.

You might send an example to my mailaddress.

Hans

 

[smitan]

Hi Ian,

As far as importing of the spreadsheets there is no problem at all.

Make a form in which you have a button and explanation next to it.
Also make a field in which you show progress. Nothing is more irritating then a process running a long time (I just made a similar thing, running for several minutes because of the size of the table) and a time glass showing and nothing happening.

Simple statements like:

X=rst.recordcount
Y=y+1 ‘ per record processed
TxtShowPercent= Y/X ‘textbox fx with 2 decimals and a percent format.

Do not forget a DoEvents statement in the looping, as you will not see a thing running if you don’t have one.

The system will be too occupied for that and the DoEvents gives the user time to even switch windows.

1. use DIR folder\*.xls and a loop with DIR to get through the folder with all(if several?) spreadsheets; putting the path and filename into strPathToYourXls
2. use DoCmd.TransferText acImportDelim, , "tblYourIntermediatTable", strPathToYourXls, True to import the spreadsheet
I, unfortunately, have no experience in how to get a hold on the various worksheets in the workbook, but some experimenting will give you the solution. Do remember that you can do most things in the immediate window
3. the tblYourIntermediatTable is deleted every time you start the process as that will give you a new table with all the column names you need.
4. the rest is a VBA-process of reading the data in your tblYourIntermediatTable and inserting that into the appropriate tables.

I suppose every record will have to do with more then one table as your workbook is not normalized at all.

I am a person that very much prefers to do things in code instead of via query’s. Like making a recordset and reading and writing the whole lot self, as I have the feeling that I have more control over things.

If you start your coding with a On Error Resume Next, you will be able to add many statements like:

DoSomething
If err.number <> 0 then stop

When the code stops, you have all the time to find out the reason why via the immediate window and make the coding more dedicated.
Trial and Error!

:-{) I learned (and taught other people during eleven years) that T&E is a bad way of coding, you should make all kind of schematics first and write ‘perfect’ code after. With nowadays way of coding and visual possibilities I think this is no longer the only truth.

Good Luck
Hans

 

[Ricky1946]

Thanks Smitan,
I have decided that I am not able to do this task for the customer as I have put too many hours in already and it is not getting resolved. I think it is better to be honest in these circumstances.
Thankyou very much for your input. It is very much appreciated.
Regards
Ian

&quot;To say 'thankyou' encourages others.&quot;

 

[smitan]

It's a wise man that knows his limits

Could I take the job?

Greetings
Hans

 

[Ricky1946]

Hello Hans,
I am hoping that he will accept the package without the Excel import facility as it is only a part of the package.
Anyway he couldn't afford your rates!!!!
Thanks
Ian

&quot;To say 'thankyou' encourages others.&quot;