Workspace w/options?

Posted by Kristy on October 30, 2001 9:53 AM

First of all, sorry if the subject is a little confusing...I'm not quite sure as to what this would be called.

Anyway, I currently work with 3 different workbooks when I receive a certain form. There is a different workbook for each series year. Right now, I have a workspace set up to open the 2 I always use, plus the 2001 workbook.

What I am wondering is if I can set up a workspace to open the 2 that are always used, but bring up a window asking what series year workbook they want to open.

For example, it would bring up a list of 2001-B, 2000-A, etc. or have the option of starting a new one if it is a new letter series.

Of course, not being a code connoisseur myself, knowing my luck this is not even remotely possible.

If anyone has any suggestions, I'd like to hear them.
Thanks!

Posted by Bob Umlas on October 30, 2001 9:59 AM

Your best bet (IMHO) is to have a workbook whose sole purpose is to open the 2 you want & then prompt for the 3rd. Something like this should do:
Private Sub Workbook_Open()
Workbooks("Yourfirstone").Open
Workbooks("YourSecondOne").Open
Which = Application.GetOpenFileName() 'prompts user to open 3rd
workbooks(which).Open
End Sub

Posted by Kristy on October 30, 2001 10:20 AM

Ok...I thought I understood that up until 'which.' However, when I cut/pasted it in Excel, nothing happened. I assume I entered something wrong. I have no VBA skills whatsoever.

If you could maybe go a little slower, or at least tell me how I should enter it into a workbook to make this work, I would be ever so grateful.

Thanks again!

Posted by Juan Pablo on October 31, 2001 4:23 AM

You need to copy this to your workbook module (Go to VB Editor and doble click on the little workbook icon.

The code will be run when the workbook is opened.

Juan Pablo

Posted by Kristy on October 31, 2001 6:18 AM

Still not working...

I've pasted the code under 'This Workbook' (I assume that is what you meant by workbook icon as I couldn't see any others).

I added the path to the 2 files that I want to open every time and saved it.

When I opened the workbook again, it started to run, but then I got "Run-time error '9', subscript out of range. I hit debug, and it highlighted the line where I entered the first workbook file path.

Any ideas?
Thanks.

Posted by Juan Pablo on October 31, 2001 7:42 AM

Can you post what your code looks like ?

Posted by Kristy on October 31, 2001 7:52 AM

Re: Can you post what your code looks like ?

Thanks, Juan.

Here's what I copy/pasted from here, except for adding the file paths. I assume it might just be something little that I did, probably w/the file stuff (*sigh* I wish I could understand this junk instead of having to have you guys write it for me:))

Private Sub Workbook_Open()
Workbooks("C:\WINDOWS\Desktop\E.C.R\U.P.G\UPG LIST REVISED.xls").Open
Workbooks("C:\WINDOWS\Desktop\E.C.R\MAIN DIRECTORY.xls").Open
Which = Application.GetOpenFilename() 'prompts user to open 3rd workbooks(which).Open

End Sub

Posted by Juan Pablo on October 31, 2001 8:33 AM

Re: Can you post what your code looks like ?

Try with

Private Sub Workbook_Open()
Workbooks.Open "C:\WINDOWS\Desktop\E.C.R\U.P.G\UPG LIST REVISED.xls"
Workbooks.Open "C:\WINDOWS\Desktop\E.C.R\MAIN DIRECTORY.xls"
Which = Application.GetOpenFilename() 'prompts user to open 3rd

Workbooks.Open Which

End Sub

It's not difficult to learn this, just put the cursor, for example, in "Open" and press F1, that should tell you what it does, some examples, etc.

Juan Pablo

Posted by Kristy on October 31, 2001 9:40 AM

Just one more little question...

Oh, OK. So really the only problem with that was that the code I had copied was a little off. Spiffy.

That did finally work, BTW, thanks!

One little thing, though. Is there any way I can have it already be in the right directory when the open message box pops up? Right now, it just goes to the default.

Thanks again!
Kristy

Posted by Juan Pablo on October 31, 2001 10:26 AM

Re: Just one more little question...

Use the ChDir command to change directories BEFORE you ask for the file, that is before the GetOpenFileName()...

something like

ChDir "D:\TMP"

If you need to change drives use ChDrive first, and then use ChDir.

Juan Pablo

Posted by Kristy on October 31, 2001 10:46 AM

Got it! Thanks, Juan! (nt)