Import from 3000 xl books

[Tvanduzee]

Hello

I have about 3000 xl workbooks that have been created over a period of several years. The workbooks contain 1 sheet each and represent 1 workorder each.
I am designing an access db to incorporate all the data.

Is there a way to have access cycle through all the workbooks and import the data? All cell values are consistent in xl. I was looking at maybe a vba script, but I don't have a clue where to start with it.


Ex:
Cells b2:b6, c2, c4, c6, d1, a8:k8, n2:r7 all contain the same type of data for each cell.
I will be creating a table to contain the imported info.

Thank you for your suggestions.

Terry

[help_me_with_access]

pretty simple, but there may be one hurdle. using excel objects in visual basic's environment and cycling through 3000 of them will probably crash the development environment completely. too much memory usage for sure.

so what you will want to do, what I do at least, is break the code every x iterations. what I usually do to stop vb from executing if i'm running a huge loop is put a message box popup based on iterations x / 1000 (or whatever increment results in the envrionment telling you "its not responding" for a period of time).

show a sample xl book. can you? do that, and you'll get some code back. deal?

[Tvanduzee]

pretty simple, but there may be one hurdle. using excel objects in visual basic's environment and cycling through 3000 of them will probably crash the development environment completely. too much memory usage for sure.

so what you will want to do, what I do at least, is break the code every x iterations. what I usually do to stop vb from executing if i'm running a huge loop is put a message box popup based on iterations x / 1000 (or whatever increment results in the envrionment telling you "its not responding" for a period of time).

show a sample xl book. can you? do that, and you'll get some code back. deal?

This file is pretty typical. I have no idea as to how to deal with the many items from A8:J182, but I'm suspecting that in access, I will create a "Workorders Details" table.
This will be a one time thing, so I don't think I will crash the system (I5 with 16GB ram). But I think it will be wise to iterate 1000 at a time.

Thank you so much
Terry

[help_me_with_access]

funny...I've seen this setup many times in small businesses. I can tell you now, what matters here is the format you want after it's all over with. data like this are not even close to acc-based formats that are workable. so, to me, that leaves you with 2 choices:

1) an ADO-based routine that will transfer one cell value at a time to any table you want at any cell placement you want.

2) a DAO-based routine that imports all the data but loops the workbooks many times over. (e.g. => several loops). what I'm getting at here, is this:

if you want your data in a specific format when it gets to access, you could section it out while combining all similar data at the same time when you're looping the workbooks. storage could either be in an access table or it could be in memory. probably wouldn't matter. I don't know how big string values can get, but the max is obviously based on the amount of memory your system can handle (the other obvious factors aside).

so that would be my suggestion. I don't have much to do today so I could probably show you how to set up the code routine. but believe it or not, most of the code you need for this entire thing, whichever method you choose, can be found on the web.

so how does it go with the format you're needing here in acc? I can tell you right now that if you want any sort of relational structure to these workbooks, especially if they have 3 tabs each, will take quite a while. that's a lot of data regardless of the resources you have available! It looks simple in Excel, but the work in Acc is all about data separation...kind of like code separation in the .NET framework stuff (if you're familiar with that).

[Tvanduzee]

Sorry for the delay, I was voluteering at a bible camp (bbq ing).
Would it be easier to compile all the info into rows within Excel, and then just do an import of the data?
It seems to me that between ado and dao, that ado would be the fastest/easiest.
I think for the access tables, I will need to create a Workorders table, and then a details table for the transactions for each workorder. What do you think?

Only the first tab of each book would need to be brought to access. The rest of the tabs deal with wages and inventory, which I will be integrating with access anyways. Previous inventory does not matter, because it is what they have on hand going forward that they will be concerned with. Wages/hrs, they will have already dealt with that in Simply Accounting. But the other info on the sheet is what they need (for look up purposes). They need to be able to look up specific data from any work order for reference and rememberance.

I did try to write some code in excel that would bring the cell values into a row, but it was big and bulky and could not get it to offset to the next row for the next worksheet(workbook).

Thank you
Terry

[help_me_with_access]

I was voluteering at a bible camp (bbq ing).

which camp? I've never done one, but am part of a rather large church around here.

Would it be easier to compile all the info into rows within Excel, and then just do an import of the data?

YES!! the reason is obvious, but only do that if you know what you want in the acc program. otherwise, more work for you.

It seems to me that between ado and dao, that ado would be the fastest/easiest.

not really. there's no difference, really. a similar comparison would be green apples to red apples. nothing doin. anyway, as far as today's web technology is concerned, those libraries are LONG gone and out of the picture! Although ADO still plays roles in .NET applications, it might disappear someday. if from nothing else, from people saying it's too old to be used...

I think for the access tables, I will need to create a Workorders table, and then a details table for the transactions for each workorder. What do you think?

sounds fine. but only unless you're tracking work orders only and nothing else with it (like your other relevant business data). I've never heard of that. wouldn't seem to be a point to it.

Only the first tab of each book would need to be brought to access. They need to be able to look up specific data from any work order for reference and rememberance.

well if that's the case, probably isn't going to be tough to get done.

I did try to write some code in excel that would bring the cell values into a row, but it was big and bulky and could not get it to offset to the next row for the next worksheet(workbook).

I could probably do that for you. I don't like working for others without a fee, but like I said this can be found anywhere on the web. Plus, I'm trusting you're a good Christian boy (or man?).

if you want Terry, show me a diagram of the data you want in access and how you want it. or show me a diagram of the desired re-arranged Excel data. Do you care to do that?

[Tvanduzee]

It is the Pleasantview bible camp at sandy beach - I used to attend the 1st baptist here is Lloydminster, but have moved to LGF.

Here's my situation. I am just in the midst of designing a db. I have been asked to allow the staff to lookup past work orders (Hense the workorders/workorders details tables.
Currently, they have, as you know, one workbook for each workorder with one larger Workbook with hyperlinks to each workbook. This workbook also links cells to each workbook (lots of work for each work order). I suggested going to access because the larger workbook keeps crashing (too much to link when it opens), and it continually goes corrupt. So with an access db, all data can be all in one place (Access db). They have also asked that Iintegrate the DB with inventory and with Employee hrs and charges for labor. The inventory part, I was looking at Microsofts Desktop inventory DB, and integrating the Workorders into the same db so that as inventory is used on the workorders, it can be subtracted from inventory.

I also am going to create a workorder creation report, so that they can print off workorders ahead of time. I know this kind of throws a loop into data integrity, but they need to have the workorders ready for the guys doing the work. They take the workorders (on paper), fill them out, and the office enters the workorders into the computer.
I will try to do a rough diagram of the db I am planning.

Yes, I am a good christian man - with only one "true" love - Christ.

Thank you so much
Terry

[help_me_with_access]

no problem, sir.

by the way, I'm not sure who "they" are (which you speak of). but the only thing about Microsoft Access that you need to understand is that it's more susceptible to corruption. some of that comes from novice users that program an app in such a way that eventually causes it. but it's also a "small guy" program that is marketed by MS, which also makes it a target for corruption.

I only mention because all applications get corrupted at one point or another. Don't be worried about that, but certainly with this program if you don't make daily (or even 2x-daily) backups of the BE part of the application, you're probably asking for it.

It's just part of the game. Actually, as I've been hearing lately...Java and Oracle are the way to go if you want stability and performance with minimal surprises. The job market in many parts of the USA seems to be Java-based. I know Larry Ellison is good, but I don't know much else about him. However, no-one has apparently bumped him off his perch since the beginning. To me, that's a sign of a stable company!

Although I did read that he finally settled that stupid argument he had for years with his neighbors regarding some trees or something that was blocking his view of the ocean.....poor baby!

[Tvanduzee]

Sorry,

Here is a jpg of the relationships page.
I have not yet put the inventory into it (Microsofts Desktop Inventory Template).

Thank You
Terry

[Tvanduzee]

Yes, I know when access begins to approach 1.5gb or more, it is very suseptible to corruption.
When I lived in NC for 7 years, I worked for a university maintaining their frontend (Access), they used oracle and sql for their backend. I did take an oracle course in 2000, version 8, right before they came out with 8i, which was the introduction to their web/java based version. The company that will used this db is a small company and will not use oracle or sql. I can't even talk them into getting a "Server". This db will be shared from an windows 7 file server/workstation.

If you need the db, please let me know. As mentioned, I have not yet put the MS inventory db in.
btw, you say "a fee", what level of a fee were you thinking?

Thank you
Terry

[help_me_with_access]

Yes, I know when access begins to approach 1.5gb or more, it is very suseptible to corruption.

it's always possible. max size for the files is still 2 GB, but that doesn't have anything to do with the system you're on (I don't think, anyway. too early in the morning to answer that one!)

The company that will used this db is a small company and will not use oracle or sql. I can't even talk them into getting a "Server". This db will be shared from an windows 7 file server/workstation.

there's nothing wrong with that, but do be aware that I'm betting not all win 7/access bugs have been documented on the web yet. Win 7 is still relatively new.

btw, you say "a fee", what level of a fee were you thinking?

for projects to work on, I charge by the hour, but the way I do it is to estimate the hrs of work it takes in the beginning and charge a flat fee. I've had way too much speculation from people as to IF I'm actually working for them or not when it comes to hour by hour. Pricing is a tough thing to deal with. But at any rate, I'm talking about actual projects. When we started this conversation, all you needed was code to transfer data from xl to acc. I personally don't call that a project. But help doing the full integration of this (with your inventory, work order stuff and any other business items that need to be setup), I might ask for something. That takes quite a bit of time to setup, implement, etc...

Let me get back to you on your diagram...shouldn't be long but I'm headed out the door at the moment.

thanks much! by the way, fees about projects and such should be discussed through instant messaging here. If you feel like you're going to need help in that manner, send me a PM here. I already sent you another kind of request, so if you didn't see that you might change your setting in the CP.

[help_me_with_access]

by the way, at first glance at the picture, 2 things need to change:

1) EMPLOYEES need to be linked to WO, not the details table. your 3 tables should appear as 3 tiers of data blocks.
2) if each order = 1 invoice, delete the invoice number from the details table. that's redundant.

and actually, now that I say this, other than those 2 things the relays in the picture look GREAT! don't see anything else wrong with it. naming conventions are irrelevant in this case unless it will get so big that you start forgetting which field name represents which field name it is linked to in another table!

[Tvanduzee]

Sorry about that, you know how work can get busy and prevent you from getting some things done.
I think the only things I will need help with at the moment is getting the data into access, and may some design tips. As mentioned, I will need to put the inventory in as well, and I know that working with MS templates can be tricky.

Yes, I can see pricing for projects could get tough.
If I need help beyond what I have asked, I would be happy to pay you for some assistance - thank you.

Thanks so much
Terry

[Tvanduzee]

Im looking at the EMPLOYEES/WO Relationship and I can't see how the relationship will work. WO table has the primary key as Workorder, which is unique (no duplicates), but each workorder can have more than 1 employee work on it. For example, the Workorder could need a welder, laborer and a machinist.

[help_me_with_access]

For example, the Workorder could need a welder, laborer and a machinist.

well see, that's why I asked why you're going to want to see in the output format. the scenario is tough to put into a simple hierarchy situation, which most databases should operate on anyway.

but the way you might want to deal with this is to give the following hiearchy of tables:

1) LEVEL 0 table - EMPLOYEES (free-standing table - used for lookup purposes only through other objs like forms)
2) LEVEL 1 table - WORKORDERS (only has info like company, id, start date, end date, etc...*no details about the subsequent tasks performed between the dates)
3) LEVEL 2 table - WO_DETAILS (this is where all the task records will go into. the foreign keys here would be *wo_id* from WORKORDERS and *emp_id* from EMPLOYEES.

there shouldn't be anything else needed.

but in order to get this simple setup started, you have to re-format before import, OR re-format after you import. setting the relationships will be last. LEVEL 0 tables (at least in my own stories!) are free-standing or *stand-alone* tables that don't have children (flow-down tables or lower level tables) directly associated with them. LEVEL 1 tables are the bosses, all other levels of tables follow the lead from the these guys.

I could probably write you a script here soon for import, you want me to assist with that? Had to ask again, it's been a while since we talked about it. 2 options - script the re-format in excel or script it in access by importing all your sheets as is. Personally, if all your xl sheets are *exactly* the same, I would rather give you some code that you can run on all of them at once. and the code should be run from an outside source (like a new workbook). Or the visual basic 6 IDE if you have it available. but I'm assuming you do not...let me know.