Link multiple workbooks by combobox.

PLEASE HELP! If anybody even knows part of this question please advise! Thanks

Hi Cozmo troll,

1. One solution would be opening the picked workbook (what do you plan, 200 new workbooks containing 1 sheet each or just a few new workbooks with let us say 50 sheets in? Both solutions possible) and copy the sheet data to a sheet in the main workbook instead of just activating the sheet. When saving changes copy the sheet data back to the workbook and clear the sheet in the main workbook. All easily made in VBA

2. There actually is a built in form function in Excel. (Data - Form). Just marking the desired data base area and activating the form will let you add and remove posts. Create a second combox to choose to which sheet you want to make changes. (Macro: Open workbook/sheet - mark data area - open form - save workbook - close workbook)

3. Not that I have heard of. Sorry..

I really do not know your skills, so I do not know how much help you need. Maybe the input above is enough. (And maybe I have got the problem all wrong..=). Feel free to ask.

Matthias

think the advice from Matthias is the right way to handle this

Thank you for the answer Mathhias! First of all I'll say that I'm definately not very advanced in my skills with macro. I want to have 4-5 workbooks with approx. 50-75 worksheets each. I'm not sure exactly what you mean when you save copy the sheet and save changes to another workbook, but if it will allow users to easily pull up the correct worksheet, and not need to navigate through any of the 4 "hidden" workbooks, then that would be absolutely fine. Please advise what I need to do to set this up. Same with your answer to question 2. It sounds like it will work. Thank you.

Will new sheets be added/removed or are the number of sheets constant?

Is 10 columns x 100 rows the maximum amount of data on a sheet?

Do as follows:
Create a new workbook and save it
Run this macro:

Sub CreateTextFiles()
'Saves the worksheets as textfiles (tab-delimited)
Dim rw As Integer
Dim Sh

    rw = 1
    For Each Sh In Worksheets
        ActiveWorkbook.SaveAs FileName:="C:\Folder\" & Sh.Name & ".txt", FileFormat:=xlText
        Workbook("NameOfTheNewWorkbook").Sheets("Sheet1").Range ("A" & rw)
        rw = rw + 1
    Next Sh
    ActiveWorkbook.Close
End Sub

This code creates a single textfile for each

The workbooks will be added to constantly. 10x100 is enough for the data on a sheet. Does that affect the code? I just tried the code and it didn't do anything. Let me give you the whole situation. We've got 20 different products. Each product has at least 5-20 different Purchase Orders used to order it each year. Each purchase order has an inspection that needs to be done. So what I want is to 1. Drop Box with all products in it. When you select the product, only P.O.'s that pertain to that product appear. When you select that P.O. only the inspection sheet that applies appears and can be used. Hopefully this makes sense? From there I want the person that does the P.O.'s to easily be able to come in and enter new P.O.'s and have them applied to the right product.

Thanks again for the help, I'll keep trying the code you sent me. Keep in mind, I'm not super advanced with macro.

Thank you.

Ignore the previous comment. I slipped the submit button by mistake..

Do as follows:
Open your current workbook
Create a new workbook, add the headers as stated on your current sheets on row 1 on sheet2, and save it
Copy this code (I assume you know where to put it(?)) and run the macro:

Sub CreateTextFiles()
'Saves the worksheets as textfiles (tab-delimited)
Dim rw As Integer
Dim Sh

Workbook("NameOfCurrentWorkbook").activate
rw = 1
For Each Sh In Worksheets
ActiveWorkbook.SaveAs FileName:="C:\Folder\" & Sh.Name & ".txt", FileFormat:=xlText
Workbook("NameOfTheNewWorkbook").Sheets("Sheet1").Range ("A" & rw)
rw = rw + 1
Next Sh
ActiveWorkbook.Close
End Sub

This code creates a single textfile for each sheet in your workbook. Yeah, I know you said you wanted 4 workbooks with 50 sheets in each. But if this will do for you it is better for two reasons: simpler programming, less disk space! Test it and then let me know if it is ok or not. It also lists all the sheet names in your new workbook.

In your new workbook:
In sheet1 create a drop down-list (Input Range: the list of sheet/file names, Cell link D2)
In cell E2 write this formula: =INDEX(Range;D2) , where Range is the list of sheet names

Copy this code to your new workbook:

Sub GetSheetData()
Dim MainFile As String
Dim OpenFile As String

    MainFile = ActiveWorkbook.Name
    OpenFile = Range("E2").Value
    Workbooks.Open OpenFile & ".txt"
    With Workbooks(MainFile).Sheets("Sheet2")
        .Range("A2:J101") = Workbooks(OpenFile & ".txt").Sheets(OpenFile).Range("A1:J100").Value
        .Activate
    End With
End Sub

Sub AddItem()
    With ActiveWorkbook.Sheets("Sheet2")
        .Activate
        .Range("A1").CurrentRegion.Select
        .ShowDataForm
    End With
End Sub

Sub SaveChanges()
    With ActiveWorkbook.Sheets("Sheet2").Range("A2:J101")
        Workbooks(Range("E2").Value & ".txt").Sheets(Range("E2").Value).Range("A1:J100") = .Value
        .ClearContents
    End With
    With Workbooks(Range("E2").Value & ".txt")
        .Save
        .Close
    End With
End Sub

Sub CloseFile()
    Workbooks(Range("E2").Value & ".txt").Close
End Sub

Add the GetSheetData macro to your drop down list

Create three buttons and attach the AddItem, SaveChanges and CloseFile macros to them.

And that is it! (hopefully..)

To handle the tool:
*Choose a worksheet from the drop down list to import the data
*Press the AddItem button to open the form for adding or removing items in the imported file
*Press the SaveChanges button to save changes in an imported file. (and close down the file as well as clear sheet2)
*Press CloseFile to close an opened file

I have made a few assumptions:
*A sheet contains no more than 10 column and no more than 100 rows of data
*You only work with one sheet at a time
*The number of sheets are constant

Are they ok assumptions?

So, does all this match your needs?

-Matthias

Ahh..missed your comment before I passed this last comment..=) Time to go to bed now, but I´ll have a look at the new requirements tomorrow.

-Matthias

When I do the first part, it tells me sub or function not defined. It did however pull up my other workbook on the side. I can't do anything else because I don't have "the list" of my worksheets since this macro won't work. I think I'm following you for the most part, and it should work to make these files text only files. One question though... not all of these sheets are identical, does that matter? When you talk about copying headers from one sheet to the new one, I'm lost. Also adding the AddItem, SaveChanges, GetSheetData, etc... again let me tell you, I've worked with macro one other time prior to this, and that was just copying a macro and pasting it in... I don't have a clue what to do so if you could give me the whole thing that'd be great. If we can get this figured out very soon, I'll boost your points to 1000. Just to save us both some time, can you try to be very descriptive when telling me what to do? Thanks again.

Been very busy lately (but, hey, I guess that is what they pay me for at work..=)..

Since no one else has added comment to this question, why don´t you send me your file (cim95gm5@yahoo.com). That would make things smoother and I could probably come up with a correct solution quicker.

-Matthias

Have waited..and waited..but I guess this means you will not send me the file.. Means you probably will have to make som minor adjustments yourself. But I think we will work it out anyway.

Just need to know then: Where on each sheet (in which cell) is the product name and in which cell is the P.O. name/number?

-Matthias

I sent it twice to the e-mail you provided.

I'll send it again right now.

Your e-mail address is not accepting my file, it says it's too large. (it's zipped and 5mb) Regardless, I gu ess we'll have to do it this way unless you have a better idea.

For the templates... the product name is in B1, and the P.O. is selected in D2. On the form finder "Home" the Product is selected in A4, and the P.O. is selected in A7.

Thanks

if you don't mind i can receive and post it on my webgarage :) so anyone can download

Bruintje:

How do I do that?

mail it to mulbum [at] tiscali [dot] nl

ok it can be downloaded

http://www.bredlum.com/ee_temp/MASTER_INSPECTION.zip

PLEASE ANYBODY... Is there anybody else that understands what GoBanana is trying to show... I need to get this working ASAP... I'll give 500 points to him for helping me this far and the same to anybody who can help me finish more quickly. Thank you.

Cozmo_troll,

Have just downloaded the file (was unable to do it last night from home unfortunately, and besides 5 Mb on a modem ...naaah..)

Looked quite different than I had imagined (and very different from the impression I first got from your question..=), so being able to look at it will save us a lot of questions.

About the multiple item P.O. pages:
How is it decided if there is multiple items on a P.O.? Is it just for 3B-0303, 3B-0403 and 3B-0503 or will there be more? Would it be possible make the selection on the Formfinder page?

And since some pages are protected I will need the password (to understand the model and to make necessary changes)

-Matthias

Bruintje,

Thanks for the support with the file!

-Matthias

anytime

Another question; Is it ok to slightly change the layout of the form? And also is it ok to show the full model name on the form CR1000Boss istead of just CR1000 (So the layout change is just to make it possible to write the full model name)

-Matthias (keeps working..)

..and another one. Adding new P.O.'s, how is that supposed to be done. I mean, there are different forms for different models. Would you like to have empty forms for all models in the workbook, or do you keep them somewhere else?

By the way, do the products remain the same? (no adding or removing of products)

-matthias

Mathias:

Thanks for your continued support... to answer questsions...
1.Password: inspection
2.Multiple P.O.'s: It's decided by our buyer, but I suppose we could put that on the form finder if we have to. And there will be more (of everything: multiple p.o.'s, model #'s, etc.) that need to be added in the future.
3.It would be fine to alter the forms, and use the entire name as stated.
4.Yes, I have templates that can be saved elsewhere so when we add a new p.o. we just use a template and fill in the relevant, and known information. If it's possible at all, I would like to create an area where the buyer could enter the new P.O. and the products on it and a template would automaticlly be created. (not sure if that's possible, but just thought I'd try.)
5. Again, products may be added at any time.
Feel free to ask away... I really appreciate all the help.
-cozmo

Ok...

I've been working on this all morning, trying to make it less complicated. I'm not sure if I've achieved that or not. What I have is...

Workbook 1: This is what I want to use as my HOME/OPERATION workbook. This is the only workbook I want viewer to see.
This has options for the viewer to choose...
Ex. View or Update Current Order
    Create New P.O.
    Add New Product
    etc.
Each of these items has it's own workbook with numerous pages to be stored there, but not navigated through.

When the user clicks Create New P.O., I want the program to pull up the page that corresponds to that in it's workbook. They will add the information needed (Product, Qty, Ship Date, P.O. #, etc.) when they are done they will "submit" the new P.O. Now... when they "submit" it, I'm hoping that there's a way that it will create new pages that pertain to it.. (ex. a new inspection sheet for it, multiple item page, etc.)

Other pages I would like to react very similar, so if we can figure out how to do this type of thing, I can follow the formula and just edit it to match the applicable info.

I think this new workbook I've created will keep things a lot cleaner, and less memory will be needed to operate it. If you want me to e-mail you these books, just let me know and I'll do it right away... (it's still very similar to the one I sent yesterday, just broken down better)

Thanks

Yes, do send me the updated file. Anything less than 58 Mb is better=) If my mail still do not accept the file because it is too large, try EMGE@memo.ikea.com. I think it will accept larger files..

-Matthias

About the multiple P.O.'s
The idea with those pages is to find and activate the choosen form, just like you do on the FormFinder page, right?
My ideas this far is to save every single P.O. as a separate file and name it for example CR1000Boss3B-0403 or AR44T1-11003. Then by selecting a model and a P.O. on the FormFinder sheet the specific form is activated. Am I on the right track, or have I missed something..?

About creating new P.O's the way you suggest
That could possibly be done, we just need to specify the condition that guides the user to the correct template. Is it one template per model for example?

-Matthias

You're exactly on track... On both questions.

There is only one template per model #.

Also... not to try and overwhelm you anymore, but I'm wondering if an active/innactive box could be added to each p.o. so that someone could come in and easily "check the status of an active order".

Ooh..for once=)

Need the password for the master file

About the P.O. file
Where to put the indata (Qty, factory..etc) when submitting?

Writing data in more than one row means it is a multiple item P.O., right? (then we do not manually need to enter that..)

But, as I mentioned earlier, since we save every P.O as "ModelNameP.O." we do not need to bother whether it is a multiple item or not?

And the status is active if the date, when viewing, has not passed the "Ship Close" date you state when creating a P.O. (and that I guess can be changed)? If so, it would be possible. If the date is easily seen on the P.O. we could just format the date to be shown in a perticular color (by conditional famatting) when active and another when not active. In that way we do not need any extra active/inactive box.

Also... not to try and overwhelm you anymore, but I'm wondering if an active/innactive box could be added to each p.o. so that someone could come in and easily "check the status of an active order".

Password: biggame
1. The data should add that P.O. to the list of model #'s, therefore creating another p.o. to the drop down list.
2. Right, more than one row= multiple item P.O. ok, I might have misunderstood before... about the naming each P.O. Each P.O. needs to have the original name, which will be the #. Each sheet however can be named by the Model Name/P.O.# like in my first Master Inspection.
3.Not necessarily on the date... it is active as long as it takes to receive the product after it is shipped, and as long as it takes to inspect. I was thinking of a check box or bullet hole or something that when selected entered the P.O. number into an "active state". SEe next e-mail that I'm sending now.

What happens is, it's shipped and we follow a tracking # on it so our receiving dept. knows when it comes in, next we inspect, then it can be innactive and only used for later viewing.

Check the status on a specific P.O. or in an overview?

Check on the status of the shipments...

They get shipped from our vendor-we get a tracking #-we track it (for up to 2 months)-it arrives-we inspect it. That's what we check status on. We have up to 25 shippments in queue at one time that need to easily be checked on by our receiving dept.

When submitting the New P.O., where do you want to write the data Qty, Factory, Ship Open, Ship Close and # C'? To the new P.O. I suppose, but to which cells?

By the way, in the last mail you sent you say: Inspection would contain all inspection reports. But...was not that the initial problem; the file gets too large? I mean, the inspection templets alone is 58mb and there will be more inspection reports than inspection templetes!

I then find the Inspection templetes file to be a bad idea. Because since it takes a while opening a file that large it will slow down the operation. An alternative would be saving templetes individual files but in a Inspection Templetes folder. The same goes for the inspection reports, keeping them in one file will slow down the operation because of the time it takes to open and close the file. Save them individually as well and keep them in a Inspection report folder.

Hmm..I really do not see the advantages of having master, inspection and P.O. as 3 different workbooks. Why not have them all in one workbook. We could always hide them when not active. If we do it your way either you have 3-4 workbooks open or you will have to open and close them all the time. Like I said before; it will only slow down the performance.

So my suggestion is to have all control sheets in one workbook.

Or maybe I have missed som advantage of separating the workbooks?

-Matthias (pondering..)

Think the problem is under control now, especially if we go for my latest suggestions. Much of the code is written and the rest is thought out.

Just need the answer for the latest questions (and..ehrm..for some new ones probably=) and to settle everything (that is, no more layout changes..)

Column D-L in Order Status are updated manually, right?

Matthias:

Ok, Ok, you're a lot smarter than me on the size of the files. I agree that it would be much easier to have them each as individual files in a folder and then put all of the controlling sheets into one, with only the active one being hidden. :) The data entered into the p.o. would go into a template (just like the templates for inspection, only they would be smaller and contain only the info on the p.o.) I haven't created that yet, but if you can set it up to link to certain cells in a new worksheet, I think I should be able to work around what you use, or I think I know how to change the cell name in the macro. (hopefully:-)) Sounds like  you're doing great, let me know if there's any other questions.

Thank you!

Just wondering how the progress is coming? Also... I forgot to answer one of you questions... Column D-L will be entered manually.

Thank you!

Of course there are other questions (never run of them..=)
And no wonder I did not get where to put the P.O. data..

Just to make things clear to myself how it works. (Correct me if I have got it wrong.)
Let us say you enter a new P.O. in the New P.O. sheet. 3 lines, that is three different products. When you press submit you want it to create 1 P.O. with ALL info entered (can I assume you want the data in this new P.O. sheet in a table as entered in the New P.O. sheet?) and 3 inspection sheets, based on modelname and P.O.#

Is there, by the way, only one P.O. templete?

In HOME sheet:
When you choose View or update an existing order you somehow want to choose from a combobox one of ALL the P.O.'s that exist and open this?

Enter a new purchase order; you will just be transported to the "New P.O." sheet

Add a new product; you will be asked for the FULL product name and it will simply be added to the "P.O.'s" sheet

Edit an existing templete; ..what templete do you have in view? And it is just changing the templete, not entering any data?

View status on Active orders; goto the "Active order status" sheet

So..from where do you choose to view an inspection? Yeah..from the Form finder. So we need another link to that sheet on the HOME page(unless any of my previous assumtions on the function of that sheet was wrong..)

Like I told you more then half of the programming is done, and the rest is already thought out. If I got it all right this far (and you do not come up with some new stuff to implement as well..;) I think, despite that I have some thing to take care of this weekend, I will finish it during the weekend. But I will not promise you anything.

Hmm..also somewhat depending on if you are online sometimes during the weekend to answer my (most likely to come..) questions

You do have the first part very clear. You can assume that the p.o. data goes into a table (one that I will create) again, you can make it link to any cell on the new sheet and I'll make it work.
Yes, there is only one P.O. template.

In HOME SHEET:
1. View or update: This is where I saw it linked to the FormFinder.
2.Yes
3.This will be another template (like the new p.o. template) it will add the name to the P.O.'s sheet and if possible direct you to create a new template. (if not, it's ok too.)
4. I wanted this to link you to the main "template" for each product... say I wanted to add a "Hot Zone" item to a template, I could do that from here instead of having to find the "hidden folder" somewhere.
5. Yes, exactly.

Thanks again... I don't want to rush you, but any idea when I can see the progress??? :) I'm excited to see this work.

Your last message came while I was typing... sorry for asking the same question. I can be in the office at any time that you desire over the weekend. Do you know certain times that would be a possibility, rather than others?? Anytime this weekend would be perfect, I'm supposed to be introducing this sometime on Monday morning... if possible. Also... I'll give you more points for being such a great help.

Really do not know when I will work with it..(Now for an hour at least and probably tomorrow at this time, and a couple of hours ahead of that)

Status
What is left to do is:
*Macro dealing with active/inactive state
*Macro for hiding inactive sheets (takes like...2 minutes)
*Modify the code that will turn the existing inspections files into sperate files.
*Testing (to see if it really works and if i possibly have forgot any function)


One question though: Don´t we need a link in HOME to open up existing P.O.'s to be able to for exampel turn on/off the active state?

Have you finished the P.O. templete yet? If that is the case, send it to me.

I just sent you the P.O. template, Yes I suppose we do need a link in HOME to view or edit existing P.O.'s linking the user to this page. I'm not sure if these check boxes will work to activate the p.o. If not, I was thinking we could enter "Active" in the cell if it is active and then do some kind of conditional formatting so it shows up in the active P.O. page if that cell says "Active" you know what I mean, right?
Status: It looks like you're doing really good... hopefully I can see it soon :) Also, what time zone are you in? When you say tomorrow at this time: I don't know what time that is here.

Matthias:

I'm not sure if you're working on the file now or if you're done?? I'll be in the office for about 1 hour now so if you have any questions feel free to ask. Also... if possible, can you send me the file (completed or otherwise) by tomorrow morning? (7:30 am CST) I would like to see it and also show our group at a meeting tomorrow. What you had left yesterday sounds like you're almost done, so hopefully it should be ok.

Thanks again! :)

Excellent work!