PDA

View Full Version : Solved: Version control woes



jwise
10-19-2007, 12:28 PM
I have two workbooks, call them wb1 and wb2. wb2 was initially a copy of wb1, and it was created so I could fix code problems in the macros. Since I had to do a lot of data testing, the worksheets in wb2 are now bad, i.e. the data is incorrect, missing, or victim to bugs. The code in wb2 is now fixed. So what I want is a new workbook, wb3 with wb1's data and wb2's code. Is there any easy way to do this?

I tried "import/export" from ASAP and that tool does not do what I want. I know I can copy all my code to Notepad and then copy from Notepad files back to the good data workbook, but there are lots of places (seven) to copy and many potential errors. Surely someone else has faced this problem.

Ideas?

lucas
10-19-2007, 12:34 PM
Have you tried opening workbook 1 and select the sheet tabs...right click and select move or copy...drop the box that says to book: (both workbooks have to be open) and select workbook 2 . You might have to delete all of the sheets from workbook 1 before you start except one and rename it to something that you know won't conflict with the other sheets so you can delete it when done....you can't delete all of the sheets out of a workbook.

grichey
10-19-2007, 01:23 PM
I agree w/ Lucas. Open both Work Books and copy your tabs over which should preserve their formulas and values. If you have it in a module, you can simply drag the module to the next workbook in the MS VB editor.

jwise
10-19-2007, 01:43 PM
I don't know much about VBA, but even less about Excel. Thanks, it worked great! I think the world needs a utility to handle such.

I'm curious, as it seems to me that copying worksheets is copying objects, thus I would get the code associated with the worksheet as well. Is there some sub-object level, such that I can copy the worksheet separate from the code that lives in the object with the worksheet? Am I making sense?

When I use ASAP to export files, you can read the code in the exported files. Unfortunately, after exporting, the code was still left. My intention was to delete (I eventually manually deleted all the code in every object) the code and import from the good code workbook. I encountered another roadblock on this trail unfortunately.

I keep struggling with this "object model" stuff because I can't figure out how to get the information out of the HELP system. Is there anyplace else? I have Shepherd's book which is supposed to be the best VBA object book, but it doesn't explain this kind of stuff. It seems that there are people who do understand it (witness the ASAP author who must) because that code is so close to what I want.

Thanks again.

Bob Phillips
10-19-2007, 03:55 PM
That is odd, I think I know a fair bit about Excel and about VBA, but I have never heard of that book, so I doubt it is the best.

Loads of people know about Excel, VBA and the object model, and many are willing to give their time freely to help others, like many here. You have two choices, do it yourself (take a course, buy a GOOD book, ask smart questions) and improve to a level that you can cope, or buy the skill.

Aussiebear
10-20-2007, 03:55 AM
Well I'm only a dumb Aussie, but I'd look at opening both books and copying the good code (book 2) to the good data (Code 1). If the users are putting in good data, then its not too hard to simply fix your cose by copying and replacing the existing poor code in book 1.

jwise
10-22-2007, 07:23 AM
Thanks for all the responses. It's amazing how fast I can dig myself into a hole. Reminds me of the early 1970's learning JCL!

For xld, the book is: Excel VBA Macro Programming, by Richard Shepherd. ISBN 0-07-223144-0 (Copyright 2004).

I bought this book because several Amazon reviewers said that it had the "best Excel object model description". I have not read the entire book, but it does have 3 chapters devoted to object models (12-14), about 40 pages worth. This did help me. I have a collection of several books, and I am willing to buy more.

I am not satisfied with my understanding of the object model. I have been working on "With" loops and "For each" loops, trying to understand when you use those.

I am still very frustrated with "HOW" you learn this stuff. I had the same gripe with JCL in my previous life, but I did eventually figure it out. I haven't made sufficient progress with the object model yet where I can honestly say I understand anything. I just look around until I find something that works or ask a question here and someone graciously answers (thanks again for all your replies!), but I have to believe there is some pattern to this. I went to a seminar with Bill Jelen, and he mentioned a problem with putting names together from two columns, saying that most users wouldn't know to search on "concatenate". Being an old programmer, that is no problem to me. But I have no clue from looking at the object model how to string "A.B.C" together to accomplish a certain task. This is very frustrating... but I am seeking, searching, and trying to put this together.

Example: I am looking for examples on how to use "VBComponents". I found some example code (via Google). Naturally I neded to modify this code. When I did HELP on "VBComponents", VBA says "Not Found". Where am I supposed to go from there? Further search on Google revealed more source code which was much closer to my goal, but still not exact. I had to resort to trying every option on the pop-up list of available objects. This is very time consuming and not much fun.

I apologize for the rant. I do appreciate all responses.

Bob Phillips
10-22-2007, 07:49 AM
For xld, the book is: Excel VBA Macro Programming, by Richard Shepherd. ISBN 0-07-223144-0 (Copyright 2004).

I know the book (NOW) as I looked it up myself, but IU had never heard of it previously, which also means I have never heard anyone recommend it.


I am not satisfied with my understanding of the object model. I have been working on "With" loops and "For each" loops, trying to understand when you use those.

That is nothing to do with the object model, it is basic VB.


I am still very frustrated with "HOW" you learn this stuff. I had the same gripe with JCL in my previous life, but I did eventually figure it out. I haven't made sufficient progress with the object model yet where I can honestly say I understand anything. I just look around until I find something that works or ask a question here and someone graciously answers (thanks again for all your replies!), but I have to believe there is some pattern to this. I went to a seminar with Bill Jelen, and he mentioned a problem with putting names together from two columns, saying that most users wouldn't know to search on "concatenate". Being an old programmer, that is no problem to me. But I have no clue from looking at the object model how to string "A.B.C" together to accomplish a certain task. This is very frustrating... but I am seeking, searching, and trying to put this together.

Again, neither of these are anything to do with the object model. Where you get the data from may well be, or it may be related to another object model, but in itself it is just VB again.


Example: I am looking for examples on how to use "VBComponents". I found some example code (via Google). Naturally I neded to modify this code. When I did HELP on "VBComponents", VBA says "Not Found". Where am I supposed to go from there? Further search on Google revealed more source code which was much closer to my goal, but still not exact. I had to resort to trying every option on the pop-up list of available objects. This is very time consuming and not much fun.

I am not meaning to rude or derogatory in any way, but it seems to me that you are not a natural developer. We all face this dilemma, there is no easy solution, some find it and pick it up quickly, some don't. For instance, I just looked up VBComponents in the help and I found nothing. It must have been the same when I learnt about it, and so I have no idea where and how I found out about it, but I did (I have written plenty of extension code for the VBIDE which uses all of that aspect of the model).

As I said earlier, I feel you either have to plough on, dealing with those frustrations but expecting that it will get better and easier, and keep asking specific questions, OR, if that is too much, buy the skill in (customised training or development).

jwise
10-22-2007, 09:36 AM
I do not take your responses as rude nor derogatory. I've climbed enough hills that when I get to the next one, I know what I'm in for. My attitude is based on the fact that in the mainframe world, anything I didn't know (and there was a lot!), I could look up. In fact, I was very good at looking stuff up. I find no rhyme or reason to "How you look things up" in VBA, VB, Excel, Outlook, Word, or Windows, so I keep looking for the keys that will allow me to find my own answers. A Ph.D. degree does not mean that you know everything about a particular subject; it does mean that you have a broad knowledge of that subject, a specialized knowledge in some particular area of the subject, and that you know how to do independent research in your subject which will stand the scrutiny of your peers.

I seek the knowledge of how to do this research on my own, and sometimes I digress as to how to solve particular issues. I am always looking at this end goal. Many of my mainframe contemporaries were better programmers than I, but I did very well in the conceptual area. My tendency has been to attack this the same way. Perhaps I should change my approach.

Bob Phillips
10-22-2007, 09:46 AM
Nobody is criticising you or you approach. I am just trying to point out that I believe that what you seek is not out there, so either accept this and get less stress <G>, or change tack. If you can live with your approach and any attendant frustrations, plough on, we will always be here to help.

The mainframe world is gone, there was much less in the way of products, competition, etc. then, the life-expectancy was much greater, and the the prices were much higher (I remember spending ?500K on a Tandem back in the 80s, madness by today's values), so they could afford to supply a great deal more technical documentation.

jwise
10-22-2007, 10:37 AM
As for stress, my doctor says I need less, and that is my problem. I wish I could handle this better. Perhaps I should feel better knowing that this stuff is not documented. That makes the puzzle more valuable when it is put together, since many others won't pay the price of admission.

As to your "mainframe is dead" idea, I disagree. This tends to be one of those opinion things. Time will tell.

VBAX has been a great help to me. I think I've come a long way with this help. Time will tell on this, too.