Excel to Access Import help

[AndyN]

Hoping someone can help here.

Ok i have three excel spreedsheets. In each spreadsheet there are a number of specific columns i need to import into a new table in access.

However i need to set it into a module as i will be recieving multiple sends of these three spreadsheets with different data but the same fields in.

I'll try and give a better description:

In workbook one contains hundreds of workstation names, and a few other columns which i have no interest in.

In workbook two there are the monitors which relate to these workstations and the workstation id and ohr coluns which i have no interest in

In workbook three there contains a list of all the software installed on each workstation.

I need to import the Computer name, Monitor name, software installed into a table.

I pretty sure i could do this manually, by sorting each workbook by Computer name and then pasting into a new workbook and importing into access, but this isnt a possibilty as these three spreadsheets will be sent hundreds of times, and the person recieving them is very novice in office and probably couldnt even do it manually.

Therefore i have created a button in access which will hopefully run the module - the user will select which spreadsheets to examine and a new table will be formed.

ANY IDEAS????

[AndyN]

Oh and i need a rule that will pick out any baseunits with missing Monitors, or don't have any software installed and to create a table of all the computer Ids.


If someone is able to help with this, i'll be sooooo gratefull. They might even get a pressy, although im not in the position to be able to pay for this. :(


:D thnks guys.

[shifty.ricky]

Ok.. your trying to get data currently in a excel doc into an access database?

WHy not save the excel file a a tsv / csv and inport into access? I am sure that it allows you to do this.

Could you make it a bit clearer....i dont get excatly what your trying to do.

[AndyN]

ok, we are recieving three spreadsheets from three different places. amongst all the columns are about 4 that i require.
Some are in one workbook, others in another.

These three workbooks will be sent on a regular occurance with new information in, but in the same format.

I need it to be possible for a member of my team to click a button in a access database, it will look in a specific folder for a specific file name. and import the specific columns of data, then move onto the next and import the specific columns from here, then move onto the next etc.

It cannot be move complicated than this, for the final user as she can only just use a computer.

These workbooks will be coming in everyday and in total they will be over 100,000 rows. but these will be broken into 100-200 at a time.

Cheers, sorry if i wasnt clear before

[Alan]

DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, "Project", "C:\Rates\Project.XLS", True

Import Excel spreadsheet called Project .XLS in the Rates folder on Drive C and append it to the Access table "Project", and the top row of the spreadsheet is to be treated as field names.

Replace "Project" with an input string, and the spreadsheet with an input string.

I assume you can pre-define names of spreadsheets and column names and possibly even the range of folders from which the spreadsheets can be found and could put this in a look-up table to restrict input errors.

There is an additional syntax element which allows you to specify a range on the sheet to import.

How are you going to stop the same spreadsheet from being imported twice?

[Alan]

Can we assume that, once the workstation and monitor information is in, it will remain static except for times when existing units are scrapped and new ones added?

So is the real monitoring work on the software on each workstation?

Why 100,000 rows? How many workstations are we talking about. If you could be more specific about the process, a solution is at hand!

[AndyN]

Alan, yes the information will remain static even if they are stolen, damaged etc.

106,000 workstations and the same number of monitors or there abouts.

To be specific its for a CMDB (config managment database)

The spreadsheets will have all the monitors and baseunits at a specific location across the country - the location will only be audited this once per location so the same spreadsheets wont be sent twice.