Getting data from a weird excel setup into access?

[tc197]

Thanks for all the help, I have a lot of data I would like to transfer from excel sheets into access but the way the excel sheets are setup is going to make this difficult. I'll attach what the excel sheet looks like but basically the way all of the data over the years has been saved is in this form created in excel. Rather than have the form dump the data into tables they filled in this excel form, saved it so the data is shown in the form, and did this for every single record. What I have now is hundreds of these excel files filled in with data and I was wondering if it was possible to get it into access in any reasonable way. The forms are all identical in terms of set-up but obviously the data recorded changes. Any tip that would save me having to copy each piece of data individually over by hand would be much appreciated!

[John_G]

Hi -

You didn't say which worksheet was the one you were referring to, but it doesn't really matter. If all the Excel files have identical structure, then you can attach the two worksheets as linked tables, parse the linked tables using VBA, and transfer the data to Access tables. You will need to look at a sample linked Excel file to see what the field names corresponding to the Excel columns are.

HTH

John

[tc197]

Hi -

You didn't say which worksheet was the one you were referring to, but it doesn't really matter. If all the Excel files have identical structure, then you can attach the two worksheets as linked tables, parse the linked tables using VBA, and transfer the data to Access tables. You will need to look at a sample linked Excel file to see what the field names corresponding to the Excel columns are.

HTH

John

By worksheet do you mean which page of the excel file I attached? If so page 1 (forgot there were two pages) and what would be my second worksheet that I am linking? Am I linking the excel sheet to the Access sheet?

[John_G]

Since the data you need is on page 1, you only need to link to that one.

You are linking the Excel worksheet to the Access database as a linked table. Because of the way that table/spreadsheet is structured, you are probably going to have to use VBA to work with it, and convert text data to numeric where necessary.

Once you get one of the Excel files working, and if you feel *really* ambitious, you could automate the whole thing and have a VBA module to do all the Excel files automatically, without having to delete and relink each one manually. (!)

Bu start with one and get it working first!

HTH

John

[tc197]

Since the data you need is on page 1, you only need to link to that one.

You are linking the Excel worksheet to the Access database as a linked table. Because of the way that table/spreadsheet is structured, you are probably going to have to use VBA to work with it, and convert text data to numeric where necessary.

Once you get one of the Excel files working, and if you feel *really* ambitious, you could automate the whole thing and have a VBA module to do all the Excel files automatically, without having to delete and relink each one manually. (!)

Bu start with one and get it working first!

HTH

John

Is the VBA behind this rather basic? I have zero knowledge of VBA and I believe this is day 5 of Access for me. Would somebody be willing to help/guide me through writing some VBA code to accomplish this data transfer?

[John_G]

As VBA procedures go, it is probably relatively easy for experienced programmers, but for someone with no experience it's a different matter, of course.

Have you got all your tables and the relationships between them sorted out yet? As a part of that process, you need also to determine what it is that you want your database to do, in terms of reports or on-screen information (that will often influence how the database is structured).

Take the time to do this analysis now, early in the development process. It can save you hours of grief later on.

J.

[tc197]

As VBA procedures go, it is probably relatively easy for experienced programmers, but for someone with no experience it's a different matter, of course.

Have you got all your tables and the relationships between them sorted out yet? As a part of that process, you need also to determine what it is that you want your database to do, in terms of reports or on-screen information (that will often influence how the database is structured).

Take the time to do this analysis now, early in the development process. It can save you hours of grief later on.

J.

The database itself is almost exactly how I want it. I'm trying to figure out one last little formatting thing involving the input mask before I create the rest of my forms. (They are all derived from the main form I have set up already- copy, paste, delete a few things, I just don't want to create 6 forms and then have to go back in and change the input mask/default value issue on each one of them). The forms deliver data to my tables as I would like, I have created two reports that generate based off a user prompt and those work fine. I've also split the database so that the backend has the tables and has the two report options and the front end and back end communicate successfully. Aside from adding the remaining forms I think I'm just about done with changes to the database. I haven't done anything with the excel files yet and have only entered in some fake data to make sure everything was functioning.

I don't mind waiting to do this data thing until the forms are finished as well, I more so wanted to figure out if it was even do-able and how hard it was going to be to accomplish.

[tc197]

I have my database set up how I would like it and now I'm trying to figure out how to get this existing data in without typing it line by line. If I link the tables will the data remain after I delete the link and where will I need to use VBA?