Excel VBA - Open sheet within current workbook | Excel forum

Resolved · High Priority · Version 2003

Mon 26 Jul 2010, 14:20 Delegate Ziggy said...

Ziggy has attended:
Excel VBA Intro Intermediate course
Excel VBA Advanced course

Excel VBA - Open sheet within current workbook

Good Afternoon

I was told during the VBA Advanced course today that my query was a simple one and if I posted it on here someone would get back to me.

The problem is as follows:

Following the Intro VBA course I developed a great application for my company - works like a charm. This application needs to be applied to about 80% of the company's work-load. Now I could of course place the code into EACH of the workbooks, however I would prefer to treat this application almost like a bespoke piece of software - opening workbooks within the application, editing them, doing whatever, saving them, and then leaving the application workbook unchanged.

So I would like to add a bit of code that;
- opens up the window one sees if they select "Open File" in excel/microsoft generally, allowing me to therefore navigate and select a file.
- On open "pulls" two sheets (always labeled the same, "sheet1" and "sheet2", will do for an example) into the open application wookbook - thus allowing the rest of the application to perform its task.
- On save, saves any changes to the two worksheets back to the original file from which they came.

I already have a custom save routine running in the application workbook in order to "force" macro enable on open, so I can probably work out the save component of this request if an example of the first two elements is offered.

If someone could offer a solution to this I would be very grateful. If only beacuse it will reduce the need to maintain hundreds of codes that all do the same, as well as reducing the size of each of the individual files.

Many thanks in advance

Ziggy

Wed 28 Jul 2010, 12:16 Trainer Stephen said...

RE: Excel VBA - Open sheet within current workbook

Hi Ziggy

Thanks for your question

I'm afraid that when you posed the question during the course I misunderstood your requirements, and they are rather more demanding than I initially thought.
The following code will open the File Dialog form



 With Application.FileDialog(msoFileDialogOpen)

        .AllowMultiSelect = True

        .Show

   

 End With

However pulling sheets across to other workbooks etc. requires further coding and is too complex to be covered on the forum.

Regards

Stephen

Wed 28 Jul 2010, 13:08 Delegate Ziggy said...

RE: Excel VBA - Open sheet within current workbook

Hello Stephen

That code seems to deal perfectly with element 1.

I understand that perhaps the second element in its entirety is a touch too expansive for the forum - but maybe could you offer a couple of pointers/snippits - the dots from which I could attempt to create the whole picture.

Would something like the following;
On Workbooks.Open GoSub

...provide the start?

I obviously wouldnt want to the workbook to open, just to pull the sheets into the existing.

Any pointers would be gratefully received. It is a shame the question wasn't fully understood over the last two days - we had so much unused time I am sure we could have worked out the beginnings of a solution.

Kind regards

Ziggy