VBA EXCEL MERGING WORKBOOKS INTO EXISTING WORKBOOK

I am trying to merge a workbook with an existing workbook, how would i go about doing this with Visual Basic 2010 Express edition?

What i am trying to accomplish is that i have a main file called "MASTER SCHEDULE". On the VB form, i will have a button which will copy this file onto the users desktop and open the copied version in some sort of picturebox/viewingbox which will still allow me to edit the copied excel sheet. Once i am done adding information (No Deleting, just adding information) to the copied excel file, there will be a button called "SAVE". Upon clicking the save button, i want the copied excel sheet to merge/overwrite with the MAIN "MASTER SCHEDULE" file.

I will try to explain this more clearly with an example:

On the main master schedule in cell A1, i have a value of 3. i open this VB 2010 form (this automatically creates a copy of the master schedule onto my desktop and opens it in some sort of picture/viewing box), i add a value of 4 into cell B1. now when i click save, the copied workbook and main workbook witll "MERGE" together (the changes on the copied file will be added onto the main file). So now if i open the main "MASTER SCHEDULE" file, it will have a value of 3 in cell A1 AND a value of 4 in cell B1.

Is this possible? if so, how would i go about merging the two workbooks like this? I have got everything down EXCEPT for this merging ability.

PLEASE HELP ME

Answers


There is a saveas function. code Example:

ActiveWorkbook.SaveAs "File\Name\With\Path" & ".Extension", xlCSV ' the last thing is the      file type. yours would probably be an excel type so: XlExcel7

if you look in the help files for "Workbook.SaveAs Method (Excel)" it will have the help file for this saveas function.

So basically, you said the copied one is exactly the same as the actual one, and the copied one is the one you're actually adding the data to. So basically, this function will just take the copied one, and overwrite the main one. so any changes made to the copied one, will then be reflected in the main one.

Note: I belive this will pop the overwrite message "do you want to overwrite" if you add

Application.DisplayAlerts = False before and Application.DisplayAlerts = true after it wont pop that message it'll just do it.

Edit

when hitting the botton to save, have a sub that compares the workbook they are working in, with the master one. loop through all the data and test to see if the data in master sheet is different from their data, and highlight the changes, popping a messagebox to review changes before saving, have a way to select yes to overwrite or something.

ORRR

use dropbox. it's free. basically you save your files to a drop box account, and then you share your dropbox with others, and there are permissions and things, and any time a user makes changes to the files, they upload their changes (a simple button click) and everyone else gets a little pop up saying that changes have been made, and they click a little button which updates their files with the changes. It has also things like revision control, you can rollback, it has tracking, users can see exactly what was changed and what it used to be, and there's conflict resolution for exactly what you want, if jimmy saves before bob, when bob goes to save it will simply merge the changes. and also for example, if jimmy changed data, made a1 = "test" and bob made a change a1 = "testING" then it will pop up a message saying that there are conflicts, and it will display conflicting changes to bob, so he can either cancel, overwrite, or selectively decide which changes to overwrite, and which to rollback.

google dropbox, you'll find lots of details, its perfect for multiple users editting 1 file. why reinvent the wheel :)


Need Your Help

Is there a simple way to test if a Moose attribute is read-only?

perl testing tdd attributes moose

I currently use a block eval to test that I've set an attribute as read-only. Is there a simpler way to do this?

protractor clickanddrug on canvas

canvas drag-and-drop protractor pageobjects

I need to automate a signature on a canvas. When I used Cucumber I used canvas.click_and_drop_by and now when I try to perform it using protractor I read that I have to use the action sequence.