Solved

Excel Macro to copy data from one spreadsheet into another

Posted on 2012-03-12
90
603 Views
Last Modified: 2012-06-05
Hi Experts,

I have a new problem and need your help. I need help developing a macro that will move data from one workbook format into a different one. I have attached a mini-specification that hopefully gives good enough instructions. The data is being copied from each line of the "EE-Move-From-File" (if data is present) to multiple tabs and lines in the "EE-Move-To-File".

Thank you!
MCU-EE-Spec-03.12.2012.docx
EE-Move-From-File.xlsx
EE-Move-To-File.xlsx
0
Comment
Question by:DMKetcher
  • 46
  • 44
90 Comments
 
LVL 13

Expert Comment

by:Chris Raisin
ID: 37714025
Stand By...working on it now.

Cheers
Chris
(Melbourne-Australia)
0
 
LVL 13

Expert Comment

by:Chris Raisin
ID: 37714185
In the EE Move To File" you have on the second row what appears to be "Column" identifiers.

Are these the columns you wish to move the data to?

Also, I gather that it is only the 8 columns mentioned which you wish to move?


I do not understand the last sentence on your specification file?

I also do not understand why "Alexander" starts on Row 2 in the "Move- To" file for period
2006-2007, but starts on row 3 in the subsequent year (Tab "2007-2008")

Am I right in assuming you want to move the data in the "from" sheet to the data in the "To" Sheet if the for only the columns showing in the specification of the "To-Sheet"
(leaving the other columns in the destination "as-is" (that is, present, but empty)

Why the different colors on certain columns in both the "Move-From" and "Move To" files?

Please clarify.....

Cheers
Chris
0
 

Author Comment

by:DMKetcher
ID: 37714589
To address your questions --- I really mucked up the spec. The original data actually is in one workbook tab with all of the years of data in one line per student. I think I must have decided it would be easier to break all of these into separate tabs which requires some copying and pasting that will take about 30-40 minutes (or less) each time I run it. Ideally I wanted to be able to go directly from this one tab workbook to the "Move To" workbook which is a federal report. There are two reports required out of this data and each year has to be separated. I did not design the originating input file so am having to work with what was expected from management. The color coding helps me organize the data since there are actually about 900 columns in the original workbook (remember one line per student there). It doesn't make sense in my examples because I have copied and pasted pieces of the data. This data is just for loans. There will be a similar process for creating the grants report. I am just using loans for this example so ignore the color coding.  

In the EE Move To File" you have on the second row what appears to be "Column" identifiers. Are these the columns you wish to move the data to?

dmketcher response: yes -- In the first tab I included the column from the "Move From" file in line 1 and the column headings in line 2. In the second tab I just put the column headings. I am attaching a corrected template that has two rows.

Also, I gather that it is only the 8 columns mentioned which you wish to move? Yes - the eight columns on the output example.

I do not understand the last sentence on your specification file?

I also do not understand why "Alexander" starts on Row 2 in the "Move- To" file for period
2006-2007, but starts on row 3 in the subsequent year (Tab "2007-2008")
dmketcher response: I cleaned this up. Sorry bout that. New attachment

Am I right in assuming you want to move the data in the "from" sheet to the data in the "To" Sheet if the for only the columns showing in the specification of the "To-Sheet"
(leaving the other columns in the destination "as-is" (that is, present, but empty)

dmketcher response: Yes
EE-Move-From-File.xlsx
EE-Move-To-File.xlsx
0
 

Author Comment

by:DMKetcher
ID: 37714632
This is what the original workbook looks like before I move the data into tabs for each aid-year period. As you can see there isn't any data for the earlier years for this group of students so I copied and pasted the data for 2007-2008 and 2008-2009 for the examples I included earlier. The students in this analysis for the most part have attended only one to three of the periods being analyzed.
EE-Original-Workbook-Example.xlsx
0
 
LVL 13

Expert Comment

by:Chris Raisin
ID: 37714794
OK - I think I now have a handle on it....Stnad By....
It is 1.10am here so I cannot spend too long on it now, but will press ahead tomorrow (oops...today) morning.....(you know what I mean :-)

Stand BY...

Cheers
Chris
0
 

Author Comment

by:DMKetcher
ID: 37714986
Sounds great -- will look for your reply when you get it done. I appreciate your help. J
0
 

Author Comment

by:DMKetcher
ID: 37725062
Hi -- any progress? Looking forward to your response. Thanks!
0
 
LVL 13

Expert Comment

by:Chris Raisin
ID: 37725486
Sorry about the delay....had a death in the family (Uncle) two days ago and I am tied up with activity at the moment....Have to go to church this morning, but will get onto the job of solving your problemas soon as I get back home.  It is now 3.05am and Mass is at 10.00am and I am very tired.....Please bear with me.

I have made some progress and things look promising.

Stand By....

Chris
0
 

Author Comment

by:DMKetcher
ID: 37726076
So sorry about your uncle. Thank you for the update.
0
 
LVL 13

Expert Comment

by:Chris Raisin
ID: 37734780
I am sorry but I still cannot grasp your mapping between the worksheets:

Can you set out the mapping in the following format for me?


From      To
  A3        A3
  B3        B3
  C3        C3
  M3       E3

etc.

You have "Origination Date" showing in the specs but it does not appear in the "To" worksheet anywhere!

If you can give me the exact mapping (just for one line) then we can probably get this thing moving.

Cheers
Chris
0
 

Author Comment

by:DMKetcher
ID: 37737912
I am working on this right now. Will get it to you today.

Janis
0
 

Author Comment

by:DMKetcher
ID: 37739418
Chris,

I redid this and hope it is easier to follow. Read the specification and study the input (From...)and output (To...) files to see if it now makes sense. If not let me know and I will do what I can to clarify things further.
EE-Move-From-P01-03.19.2012.xlsm
EE-Move-To-File-Prototype.xlsx
EE-Macro-Spec-P01-03.19.2012.docx
0
 
LVL 13

Expert Comment

by:Chris Raisin
ID: 37740160
Thanks for that - Working on it now.

Stand by....

Chris
0
 
LVL 13

Expert Comment

by:Chris Raisin
ID: 37742373
A couple of things for  final clarification before i start the nitty gritty.

I notice in your mapping (e.g. L7 to E2) that there is No E2 showing in the "To" File.

Do you still want the macro to copy data to that destination even though the column is not visible?

Is the code to be a Macro stored in the "To" workbook" or the "From" workbook, or will it be held in a separate workbook all by itself.?

Will the INPUT worksheet always have the name showing for the FROM workbook, or will there be multiple workbooks to be processed each with a different name?

(P.S. I printed out the spreadsheets...I am glad they were not too much bigger since it used lots of paper and ink! LOL)

Cheers
Chris
0
 

Author Comment

by:DMKetcher
ID: 37742854
I meant to take out the first line before sending to you. The data in the "to" file starts on row 3. I can adjust in the macro as things develop. I do see cell E3 - am I missing something?

What do you recommend about where the macro should be stored? I was thinking the "To" workbook.

I prefer it be set up to process multiple workbooks (find the workbook in a folder). This allows for future flexibility.

Any other questions?
0
 
LVL 13

Expert Comment

by:Chris Raisin
ID: 37745498
I was looking at the second row where (for example) "Enrollment date" has "J" showing above it as a column identifier.

I gather now that I can ignore those column indicators and the mapping goes from the "real" column names on the spreadsheets?

To answer your question, I think that storing of the macro in the "To" workbook would be OK (better than the from), although if you start a new "To" workbook (because it is getting too big for example) then you would need to copy the macro into the newly created "To" workbook.

Another option is to have a workbook that only contains the macro (this is probably the best idea really). This "macro" worksheet would store the name of the current "To" Workbook therein (which you could change via the macro) and would ask you for the name(s) of the "from workbooks" when you execute the macro. Is that OK with you?

One thing of importance. Is this to be a "once only" exercise? That is, once you have performed the moving "from" and "to", will all future data be directly stored in the "To"
Workbook (so the need for the macro would no longer exist) or is this going to be an
on-going thing, where (for ease of recording)  a simple "From" worksheet will exist into which you post the current data, then run the macro to merge the new data into the "To" workbook. (The latter would probably be the best way to go).

Please also clarify on what date a the decision is made to place the copied data to a new tab in the "To" workbook. Is it on the Enrollment date, Date of Cert, Corr Period Start or Orig Period Start?

Lastly (and I am sorry if I missed it earlier) what version of Excel are you using?

Cheers
Chris
0
 

Author Comment

by:DMKetcher
ID: 37747042
Chris,

I am using Excel 2007. This will be an ongoing project and will be reused for other clients down the road. We are a consulting group that helps college financial aid offices. I am the only person in my little company who does this kind of work so I really appreciate your help as I have no where else to go other than the internet. We do file reconstructions for colleges that are in difficulty with the department of education. Assess liability etc.

It is ok with me to use a separate workbook to store the macro. I am new at macros so will fully understand when I see it working.

Your last question is difficult and I forgot to address it. None of the fields you mentioned are reliable to accurately determine which tab the data goes into. The data for 2006-2007 are in cells J - HJ. Data for 2007-2008 is in HG - LE. Does this help or do I need to add something to the "From" workbook to make this easier?
0
 
LVL 13

Expert Comment

by:Chris Raisin
ID: 37747394
So I gather it is the Enrollment start date.

From what you say, I think you meant FH-LE for 2007-2008 did you not? ( rather than HG-LE)

Provided there is always something howing in cells J1 and FH1 in the format "yyyy-yyyy"
then that is OK and we can go with that data "break" to determine which tab the data is sent to in the "To" Workbook.

Will there be any FURTHER columns after "LE", for example years 2008-2009, or will those years be in a separate "From" workbook?

Once I know that final answer, I think we are set to get this baby off the ground  :-)

Cheers
Chris
0
 

Author Comment

by:DMKetcher
ID: 37747671
There will be data for years 2006-2007, 2007-2008, 2008-2009, 2009-2010, 2010-2011, 2011-2012 for now.

The enrollment start date doesn't work because that is the date the student started overall enrollment. For example a student enrolls in college in 2008 and attends through 2011. The year being reconstructed is 2009-2010. If you use the 2008 enrollment date the data would go in that aid year section rather than 2009-2010. It would be best to rely on the cell range.

The cell ranges in the example are J-FH for 2006-2007 and FH-LE for 2007-2008
0
 
LVL 13

Expert Comment

by:Chris Raisin
ID: 37747848
Yes, but it appears from the spreadsheets that the years showing in those cells J1 (and J7 to j9999) are the Enrollment start date and also in cells FH1 and cells FH7 to FH9999.

I suppose I could say, is that data showing in these two cells the one I can go on to decide what Tab the data is destined for in the "To" Workbook:

                 
                     Cell       Cell Data  
                       J1       2006-2007
                      FH1    2007-2008

Will 2008-2009 appear in cell J1 of another workbook or in a cell in the existing "From" workbook   (some cell later than LE?)

I guess what I am basically asking is, is ALL the data for all those years in this one FROM Workbook (I hope not!)   :-)

Cheers
Chris


Cheers
Chris
0
 

Author Comment

by:DMKetcher
ID: 37748004
2006-07 begins in J
2007-08 begins in FH
2008-09 begins in LF
2009-10 begins in RD
2010-11 begins in XB
2011-12 begins in ACZ

Each year has 155 cells involved and all are in the same format. (I did not design this)
0
 
LVL 13

Expert Comment

by:Chris Raisin
ID: 37750676
Yes...I can see why you said you did not design it!  :-)

OK...leave it with me...it might be a day or two since I have a lot of testing to do...

I have the code "sort of" written, but I need to test and ensure code is correctly documented (to help you).

I gather that in the future you will simply have an "In" workbook taking up just columns enough columns for a single entry. After all the single entries are added for a period, the macro will then run to copy (move?) that data from the "From" book into the "To book in the appropriate place.

If that is the case, could you please tell me EXACTLY the column names and headings of each of the columns in the future "input" Spreadsheet (Do not have any columns missing, even if they are not to contain data.(Probably best not to have "blank" columns which are never to receive data (i.e. blank columns) - we want to keep the input spreadsheet as small as possible.

I look forward to your planned input spreadsheet.

(If you want to make it "Multi-Line" rather than all on one line for an entry that is fine (just like filling in a proper A4 form on a screen (if you know what I mean). With that method after entry of a single lot of input, the macro could be run to move that data into the "TO" spreadsheet and clear the input form. (Just an idea).

Otherwise, we can leave it in the same "one line" format if you wish. I just then need to know what columns will be in use and their headings. (probably A7 to FG7 for first entry and then A* to FG8 for the next??)

Cheers
Chris)


and you will enter data
0
 

Author Comment

by:DMKetcher
ID: 37752171
Chris,

I really don't need all the detail coding for this. I can do it - I am at least that proficient. I just need the base code for the pattern and then I can follow it with moving each field. If you have to have it let me know but I want to go to a smaller sample to save time.

Thanks, Janis
0
 
LVL 13

Expert Comment

by:Chris Raisin
ID: 37755593
Yes, I think I do need it, but of course go to a smaller sample by all means.

Cheers
Chris
0
 

Author Comment

by:DMKetcher
ID: 37758219
I am working on this. Will get it to you today.
0
 
LVL 13

Expert Comment

by:Chris Raisin
ID: 37760715
OK thanks - No hurry (unless you are in a hurry) :-)
0
 

Author Comment

by:DMKetcher
ID: 37760722
I will get the spec to you early next week. Still working on it. Thanks! Getting it back the middle of next week would be great.
0
 
LVL 13

Expert Comment

by:Chris Raisin
ID: 37760773
OK. Thx
0
 

Author Comment

by:DMKetcher
ID: 37763055
I started over - changed a few things to make it easier. Mainly, I abandoned the large spreadsheet in the data_pull tab and copied and pasted the relevant data into separate tabs for each aid year. We are working with just two aid years for this example but the others will use the same pattern. The instructions are in the EE Worksheet mapping P01 03.23..2012.xlsm. I know you will let me know if you have questions :-).

Janis
EE-Worksheet-mapping-P01-03.23.2.xlsx
EE-Move-From-P02-03.25.2012.xlsm
EE-Move-To-File-Prototype.xlsx
0
 
LVL 13

Expert Comment

by:Chris Raisin
ID: 37763997
OK Thanks. On to it, but again it may takes a day or two.

Cheers
Chris
0
 

Author Comment

by:DMKetcher
ID: 37765708
ok -- it should be easier now. Let me know if you have further questions.

Janis
0
 

Author Comment

by:DMKetcher
ID: 37796139
Any progress on my project? I need this very soon. I do appreciate your help.
0
 
LVL 13

Expert Comment

by:Chris Raisin
ID: 37796212
i'm sorry about the delay.

I was at a Microsoft Conference all day Saturday looking at development under Windows 8, and was quite ill both Sunday and most of today.- no... not from seeing Windows 8!  :-)

Have just returned from running my Bridge Club tonight (through which I suffered) and after about 7 hours sleep (it is now midnight) I will return to work on your problem as soon as I arise and spend all day on it to get a working solution to you by midnight tomorrow (Tuesday)
i.e. in the next 24 hours. I hope that is OK.

Please stand by....


Cheers
Chris
0
 

Author Comment

by:DMKetcher
ID: 37796521
Thanks for letting me know. I am ok so far but will need something as soon as you can get it to me.

Thanks, Janis
0
 
LVL 13

Expert Comment

by:Chris Raisin
ID: 37804574
Q1. Will this be a "once" off conversion for each sheet as far as the "To" Workbook goes,
      or will data be added to the "To" Workbook via a "From" workbook in the future?

      What I am trying to say is, once the conversion is done, will any future data be added by    
      simply opening the "To" Workbook and on the appropriate Tab, manually inserting a new
      row at the appropriate place to manually insert new data, or will future data come from
      a "From" workbook even after this conversion is performed?

Q2. Will the "To"Workbook already have Headings in it, or do you wish the "To" workbook to
       be created from "Scratch" with the headings added as well?

This is turning into a very complex operation, but I have it "sort of" working at the moment provided the "To" workbook already has the headings in it for the columns.

A lot more testing needs to be done in the next 24 hours to ensure it all runs smoothly and then you should be OK to use it without recking data (I would of course test it yourself thoroughly on test data and back-up your originals first).

Anyway, I need a response to the two questions first.



Cheers
Chris
0
 
LVL 13

Expert Comment

by:Chris Raisin
ID: 37804963
Some of the lines in the mapping have comments such as:
"Line 3 (if data is present - test if Loan Type 2 has a value (is not null)"

I have no idea what that means and it is causing my mapping to crash.
Do I simply ignore those lines and continue on with the mapping of the cells beneath?

Cheers
Chris
0
 

Author Comment

by:DMKetcher
ID: 37806154
Q1. Will this be a "once" off conversion for each sheet as far as the "To" Workbook goes,
      or will data be added to the "To" Workbook via a "From" workbook in the future?

      What I am trying to say is, once the conversion is done, will any future data be added by    
      simply opening the "To" Workbook and on the appropriate Tab, manually inserting a new
      row at the appropriate place to manually insert new data, or will future data come from
      a "From" workbook even after this conversion is performed?

A. Yes -- I would like it to work that way.  Would like to be able to add data as development occurs.

Q2. Will the "To"Workbook already have Headings in it, or do you wish the "To" workbook to
       be created from "Scratch" with the headings added as well?

A2. Yes the headings will be there --  no need to do from scratch. Begin inserting data on line 2.

Your last question: Just put in all of the data requested for the eight possible loan types. I will clean it up after the import is done.
0
 
LVL 13

Expert Comment

by:Chris Raisin
ID: 37806473
Sorry - But your answer of Yes" to the first question is not clear.

Which way are you handling it after conversion?

Cheers
Chris
0
 

Author Comment

by:DMKetcher
ID: 37806633
There will be some manual entry as we have workbook data that will not work with the macros. You could set it up so that the line where I place the cursor is the next line to be added by the macro. Attached is an example of the macros developed by Dave (dlmille) to create the "From" data that was originally in that huge spreadsheet format I gave you. This adds new data at the line where I place the cursor so that I don't overlay the old data.
MCU-File-Review-Summary-Detail-N.xlsm
0
 
LVL 13

Expert Comment

by:Chris Raisin
ID: 37818138
OK - I'll try to incorporate that.

I am sorry for the delay, but it is Easter break here and I am very busy with my work with the Church for the next day or so. (I am a Special Minister).

I will resume work on this on Monday and hopefully have a working answer by Wednesday at the latest after testing is complete.

Cheers
Chris.
0
 

Author Comment

by:DMKetcher
ID: 37819630
OK thanks. I really need it soon.
0
 

Author Comment

by:DMKetcher
ID: 37834001
When will this be ready?
0
 
LVL 13

Expert Comment

by:Chris Raisin
ID: 37836238
I'm sorry about the delay...I have been caught up in another urgent question which uis very involved...please bear with me...I will try to get something to you today.

Just to let you know how it is going to work,
The macro will be placed in the workbook which contains the "Map" information.

It will step through each column of "From" addresses found in a certyain column in the "From" workbook, and copy the value found at the cell addressin the "From" workbook into the cell address mapped for the "To" Workbook.

Once it senses (say) 25 blank cells in a row in the "Map" workbook (i.e. we have reached the end of mapping) it will stop. That way you can define mapping at any time (and make changes if necessary) without changing the code.

Does this sound a reasonable way to go?

Cheers
Chris
0
 

Author Comment

by:DMKetcher
ID: 37843339
Hey Chris - how is it coming? Let m eknow if you have further questions.

Janis
0
 
LVL 13

Expert Comment

by:Chris Raisin
ID: 37843390
Janis,

I just asked you one!

Can you please answer the question in the last comment I made? (37836238)

Cheers
Chris
0
Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

 

Author Comment

by:DMKetcher
ID: 37843683
Sorry -- thought I answered it. Yes, that sounds fine. I appreciate your helping me with this.
0
 
LVL 13

Expert Comment

by:Chris Raisin
ID: 37845080
OK...today is Saturday and I will spend all day today and tomorrow (if needed) to get it to you.
( I have a few duties at Church tonight, but that only takes up about 5 hours)

Stand by....


Chris
0
 

Author Comment

by:DMKetcher
ID: 37846356
Chris -- You can take another week or so. I just learned you guys do not get paid for this. I am in shock over that one. Please do not stress yourself and enjoy solving the problem. I will definitely need this totally ready within a month and I think you and I will be going back and forth a bit after you deliver it. Have a good weekend! I do appreciate your efforts to help me out.

Janis
0
 
LVL 13

Expert Comment

by:Chris Raisin
ID: 37846507
Thanks for that, but I will try to get it out of the way as soon as possible for you.

Yes, it would be nice to be paid (I have been unemployed for 4 years now), but at 62 I am getting close to receiving a pension (only 3 years to go) and this keeps my mind active.

I guess I will still be solving problems when I reach 100!  :-)

Cheers
Chris
0
 

Author Comment

by:DMKetcher
ID: 37871363
Hi Chris,

All of a sudden I have another assignment which is high priority that requires similar logic to what you are developing. Could you send me what you have so far so that I might use it for this new report? It is similar to what you are developing for me but much simpler. Since you are almost finished I think it might help me get started.

Thanks, Janis
0
 
LVL 13

Expert Comment

by:Chris Raisin
ID: 37874618
OK - I will just finish my testing and will send you what I have tomorrow.

Cheers
Chris
0
 

Author Comment

by:DMKetcher
ID: 37876089
Thank you!
0
 
LVL 13

Expert Comment

by:Chris Raisin
ID: 37877471
A couple of questions:

1. Will the destination workbook always exist or do you want the macro to CREATE a new "TO"
    Workbook if it does not exist.

2. Following on from Q1, will the destination Tab in the "TO" workbook always have the
    same name as the Tab for the sourced data in the "FROM" Workbook, or will the Tabs be
    incrementally created from the date in the data?

This is very tricky stuff and I am growing older by the minute!  :-)

Cheers
Chris
0
 
LVL 13

Expert Comment

by:Chris Raisin
ID: 37877886
I think I will go off and commit suicide.....after five hours of testing and changes this evenings, I accidentally closed the worksheet and did not have autosave on, so I lost 5 hours work!   AAAAH!    It was working great too (except for a few minor cosmetic glitches)

I will have to go to bed now (it is 10 mins. to 1 in the morning)....

It is a good thing I am semi-retired!

I will get back it back working when I get up in the morning.

It just goes to show that you MUST have autosave  of versions on while you are working on these things

I feel sick!  :-(  


Cheers
Chris
0
 

Author Comment

by:DMKetcher
ID: 37877957
OK -- Just do your best. Sorry you lost your work. Five hours - oh no. Thanks again, Janis
0
 
LVL 13

Expert Comment

by:Chris Raisin
ID: 37878359
Ok...I could not sleep, so I came back to the PC and carried on regardless.

It is now 3.23 am and I think I have it back to what it was like 7 1/2 hours ago.

All the code (except the calling macro) is contained in a UserForm.

Are you familiar with UserForms?

Everything is in the attached file, but so others can view the code and see the form, I will also show them below.

All I know is that the code runs and data is copied, but I have not checked that the data is copied to the correct locations as defined in the mapping.

Basically, the macro and the form are contained in the VBA code within the "mapping" workbook.

This macro will run and the user selects the "FROM" Workbook (the macro will list all the
worksheets in that workbook as "Tabs")

Then the user also select the TO workbook.

Please note that both workbooks must exists otherwise the button "COPY DATA" will not activate.

When that button is clicked, the macro makes sure the TO and FROM workbooks are not open (if they are, it saves and closes them), then it goes through the Mapping in the Mapping workbook (The one showing on the screen) and for each tab in the FROM Workbook, it copies data (as defined in the mapping) from the FROM workbook to the TO workbook (on the same Tab). If the Tab does not exist, it creates it and names it (I think...that still needs to be tested).

There are button to load the FROM and TO workbooks onto the screen (when the button are activated) (They are designated as "Open" buttons)

Hopefully this is along the lines of what you want to do.
Everything centers around the mapping (which you can manually change as desired at any time).
The code should run with any two workbooks, provided the mapping is correct.

I am a liltle vague still about the "blank" notations in the mapping.

Do you want the code to "blank out" cells in the TO worksheet if the word "blank" appears in the mapping, or what?

At the moment it is ignoring anything that has "blank" designated.

I suppose it still has more to be done with it, but at least you can get an idea of where we are at and whether it is getting closer to your needs.

Please advise.

Cheers
Chris



This is the macro.......Wow! - a one line macro!  :-)

It is best to set it up in Excel with a "hotkey" to call it. (If you do not know how to
do this let me know and I will advise).

Public Sub CopyData()
  frmCopyData.Show
End Sub

Open in new window


Next comes the code that is already contained in the form (which you can obtain from the attached file)

Option Explicit

Private Sub DisplayBook(cfile As String)
  Dim nWS As Integer
  Dim ofs As Object
  Dim f   As Object
  Set ofs = CreateObject("scripting.filesystemobject")

  For nWS = 1 To Application.Workbooks.Count
    Set f = ofs.getfile(cfile)

    If Application.Workbooks(nWS).Name = f.Name Then
      Application.Workbooks(nWS).Windows(1).Visible = True
      Exit For

    End If

  Next

End Sub

Private Sub LoadTabs()
  Dim nTab  As Integer
  Dim oFrom As Workbook
  Set oFrom = Application.Workbooks.Open(Me.txtFrom.Text)
  Me.lstTabs.Clear

  For nTab = 1 To oFrom.Worksheets.Count
    Me.lstTabs.AddItem Trim(oFrom.Worksheets(nTab).Name)
  Next
  oFrom.Close
  Set oFrom = Nothing

End Sub

Private Sub PerformMove()
  Dim nTab  As Integer
  Dim oMap  As Object
  Dim oTo   As Object
  Dim oFrom As Object
  Set oMap = Application.ActiveSheet
  Set oFrom = Application.Workbooks.Open(Me.txtFrom.Text)
  Set oTo = Application.Workbooks.Open(Me.txtTo.Text)

  For nTab = 1 To Me.lstTabs.ListCount
    TransferData oFrom, oTo, nTab, oMap
  Next
  oTo.Save
  oTo.Close
  oFrom.Close
  Set oMap = Nothing
  Set oTo = Nothing
  Set oFrom = Nothing
  Me.cmdCopyData.Enabled = True
  Me.cmdOpenFrom.Enabled = True
  Me.cmdOpenTo.Enabled = True

End Sub

Private Sub RestoreTabs(cTagged As String)
  Dim nTab As Integer

  For nTab = 0 To Len(cTagged) - 1
    lstTabs.Selected(nTab) = (Mid(cTagged, nTab + 1, 1) = "1")
  Next

End Sub

Private Sub SaveAndClose(cfile As String)
  Dim nWS As Integer
  Dim ofs As Object
  Dim f   As Object
  Set ofs = CreateObject("scripting.filesystemobject")

  For nWS = 1 To Application.Workbooks.Count
    Set f = ofs.getfile(cfile)

    If Application.Workbooks(nWS).Name = f.Name Then
      Application.Workbooks(nWS).Windows(1).Visible = False
      Application.Workbooks(nWS).Save
      Application.Workbooks(nWS).Close
      Exit For

    End If

  Next

End Sub

Private Function SaveTabs() As String
  Dim nTab As Integer
  Dim cRet As String

  For nTab = 0 To lstTabs.ListCount - 1
    cRet = cRet + IIf(lstTabs.Selected(nTab), "1", "0")
  Next
  SaveTabs = cRet
End Function

Private Sub TransferData(oFrom As Workbook, _
                         oTo As Workbook, _
                         nTab As Integer, _
                         oMap As Worksheet)
  Dim cMapCell    As String
  Dim cFromCell   As String
  Dim cToCell     As String
  Dim nRow        As Long
  Dim cValue      As String
  Dim nBlanks     As Integer
  Dim bSkip       As Boolean
  Dim nProcessed  As Integer
  Dim bProcessing As Boolean
  bProcessing = True

  For nRow = 6 To Application.Rows.Count

    If bProcessing Then
      bSkip = False
      cMapCell = "$B$" + CStr(nRow)
      cFromCell = Range(cMapCell).Value

      If Len(Trim(cFromCell)) = 0 Then
        nBlanks = nBlanks + 1
        bSkip = True

        If nBlanks = 100 Then
  'we can safely assume we have reached end of data input
          bProcessing = False

        End If

      Else
        nBlanks = 0

      End If

      If Not bSkip Then
        If cFromCell <> "blank" Then
          cMapCell = "$D$" + CStr(nRow)
          cToCell = Range(cMapCell).Value

          If cToCell <> "blank" Then
            cValue = oFrom.Worksheets(nTab).Range(cFromCell)
            oTo.Worksheets(nTab).Range(cToCell) = cValue
            nProcessed = nProcessed + 1
            Me.lblProcessed.Caption = "Processed: " + CStr(nProcessed)

          End If

        End If

      End If

  'let other processes run too, to be fair
      DoEvents
    Else
      Exit For

    End If

  Next

End Sub

Private Function WorkbookIsOpen(wbname As String) As Boolean
  Dim wb As Workbook

  On Error Resume Next

  Set wb = Workbooks(wbname)
  WorkbookIsOpen = (Err <> 0)

End Function

Private Sub chkAllTabs_Click()
  Dim nTab As Integer

  If chkAllTabs.Value = True Then

    For nTab = 0 To Me.lstTabs.ListCount - 1
      Me.lstTabs.Selected(nTab) = True
    Next
  Else

    For nTab = 0 To Me.lstTabs.ListCount - 1
      Me.lstTabs.Selected(nTab) = False
    Next

  End If

End Sub

Private Sub cmdClose_Click()
  Unload frmCopyData
  End

End Sub

Private Sub cmdCopyData_Click()
  Dim nTab    As Integer
  Dim nTagged As Integer

  For nTab = 0 To Me.lstTabs.ListCount - 1

    If Me.lstTabs.Selected(nTab) Then
      nTagged = nTagged + 1

    End If

  Next

  If nTagged > 0 Then
    lblProcessed.Caption = "Processed: 0"
    lblProcessed.Visible = True
    cmdCopyData.Enabled = False
    cmdOpenFrom.Enabled = False
    cmdOpenTo.Enabled = False

    If WorkbookIsOpen(Me.txtFrom.Text) Then
      SaveAndClose Me.txtFrom.Text

    End If

    If WorkbookIsOpen(Me.txtTo.Text) Then
      SaveAndClose Me.txtTo.Text

    End If

    PerformMove
  Else
    MsgBox "Please select some Tabs first!"

  End If

End Sub

Private Sub cmdFrom_Click()
  Dim cSource As String
  Dim ofs     As Object
  Set ofs = CreateObject("scripting.filesystemobject")
  cdFileMgr.DialogTitle = "Select Source File"
  cdFileMgr.Filter = "Excel Workbooks(*.xl*)|*.xl*"
  cSource = GetSetting(Application.Name, "Setup", "SourceDir", Dir(CurDir()))
  cdFileMgr.InitDir = cSource
  cdFileMgr.flags = cdlOFNFileMustExist
  cdFileMgr.ShowOpen
  Me.txtFrom.Text = cdFileMgr.FileName
  Set ofs = Nothing

End Sub

Private Sub cmdOpenFrom_Click()
  Dim oFrom As Object
  Set oFrom = Application.Workbooks.Open(Me.txtFrom.Text)
  DisplayBook Me.txtFrom.Text

  'set ofrom = nothing
End Sub

Private Sub cmdOpenTo_Click()
  Dim oTo As Object
  Set oTo = Application.Workbooks.Open(Me.txtTo.Text)
  DisplayBook Me.txtTo.Text

  'set oTo = nothing
End Sub

Private Sub cmdTo_Click()
  Dim cDest As String
  Dim ofs   As Object
  Dim cTabs As String
  'Save the selection of tabs in case when the user
  'is selecting the TO workbook he/she accidentally
  'selects tabs as wellwhen clicking qickly
  cTabs = SaveTabs()
  Set ofs = CreateObject("scripting.filesystemobject")
  cdFileMgr.DialogTitle = "Select Destination File"
  cdFileMgr.Filter = "Excel Workbooks(*.xl*)|*.xl*"
  cDest = GetSetting(Application.Name, "Setup", "DestDir", Dir(Me.txtFrom.Text))
  cdFileMgr.InitDir = cDest
  cdFileMgr.flags = cdlOFNFileMustExist
  cdFileMgr.ShowOpen
  Me.txtTo.Text = cdFileMgr.FileName
  'Restore the tabs to how they were
  RestoreTabs cTabs
  Set ofs = Nothing

End Sub

Private Sub txtFrom_Change()
  Dim ofs As Object
  Set ofs = CreateObject("scripting.filesystemobject")

  If ofs.fileexists(Me.txtFrom.Text) Then
    LoadTabs
    Me.cmdOpenFrom.Enabled = True

    If ofs.fileexists(Me.txtTo.Text) Then
      Me.cmdCopyData.Enabled = True

    End If

  Else
    Me.cmdCopyData.Enabled = False
    Me.cmdOpenFrom.Enabled = False

  End If

  Set ofs = Nothing

End Sub

Private Sub txtTo_Change()
  Dim ofs As Object
  Set ofs = CreateObject("scripting.filesystemobject")

  If ofs.fileexists(Me.txtTo.Text) Then
    Me.cmdOpenTo.Enabled = True

    If ofs.fileexists(Me.txtFrom.Text) Then
      Me.cmdCopyData.Enabled = True
    Else
      Me.cmdCopyData.Enabled = False

    End If

  Else
    Me.cmdCopyData.Enabled = False
    Me.cmdOpenTo.Enabled = False

  End If

  Set ofs = Nothing

End Sub

Open in new window





This is what the form looks like.

frmCopyData - The form used in the macro

The references you need in the VBA environment (just use the menu in VBA under Tools/References, and make sure you have these included).

References

 
And last but not least, I have attached a file that contains the form with its internal code.
I hope this works so yu do not have to copy and paste all the above code or create the form (creation of form would be the hardest bit).

You need to sabve the form to a folder somewhere and then IMPORT it as a file via the VBA editor using "File/Import". Import it into the place where you have written the one line macro.

Whew!  It is now 4.45am and I am off to bed!  :-)

Cheers
Chris
FRMCOPYDATA.ZIP
0
 

Author Comment

by:DMKetcher
ID: 37878919
Chris -- thank you so much. I expect to have time tomorrow to try it out and work with it. I know how to set up the buttons to execute the macro so am good there. I will let you know if I get stuck on anything. Janis
0
 

Author Comment

by:DMKetcher
ID: 37881360
Hi Chris,

I am already running into problems... I am attaching the setup in the VBA developer on my laptop. This is set up by my corporate IT. I do not find any of the following:
Microsoft Excel 14.0 Object Library
Microsoft Forms 2.0 Library
Microsoft Common Dialog Control 6.0(SP6)

Can we do this without a form?
1. Open from the "To" form (already open)
2. run the macro using a control button
3. have a popup ask for the "From File"
4. User finds the From File following a path
5. Then the magic happens

I have used this logic before and it works without activating any other references. I am getting heavy pressure. Have to get this done. Thanks again for your expert assistance.

Janis
0
 
LVL 13

Expert Comment

by:Chris Raisin
ID: 37884273
Thats OK, you do not need those particular versions (I am running under Excel 2010).

Do you have versions erlier than that (e.g. Excel ??.? Object Library, Microsoft Forms ?.??
Library, Micrtosoft Common Dialog Control ?.?).

If not, we COULD go without a form ( but it is not very nice).. Much better with the form.

I am sure you have those earlier versions, but if not I can supply them to you. (There is no problems with copyright).

Cheers
Chris
0
 

Author Comment

by:DMKetcher
ID: 37884347
I got screen shots of the setup on my laptop. I like the form idea. I just don't know how to set it up in Excel. I have worked with forms in MS Access but not Excel.
Tools-Reference-for-Excel-2007.docx
0
 
LVL 13

Expert Comment

by:Chris Raisin
ID: 37884516
Works the same way.although the form is created solely within the VBA editor, not the Access IDE.

If you are in the VBA Editor you just click on "Insert" and then  "From" and it will take you to the Form Designed. You need to view the Toolbox to grab Textboxes, Labels, buttons etc.

To view the Toolbox you click on "View/Toolbox". The toolbox will appear, provided you have first selected the form or one of the controls you have already placed on the form.

Scroll down in the References to see if you can see "Microsoft Common Dialog (any version) and Microsoft Forms (any version) and click on them to select them

If they do not appear there, click on the Browse button and look in :
"C:\Windows\System32" for files with names like:
                        "C:\Windows\System32\ComDlg32.ocx"
                        "C:\Windows\System32\FM20.dll"       (might be earlier version than  2.0)
 
You already have the Excel 14.0 Object Library.

By the way, we must run from the "Mapping" workbook, not the "To" Workbook.
The macro must be in the Mapping Spreadsheet. Do not put it in the "To" or "From" workbooks. The only constant workbook which will always be present in the operation is the Mapping workbook. (If you do it in the "To" workbook, you will have to copy the macro across every time into a new "To" workbook when you change "To" workbooks - very messy.

Let me know how you get on.

Once you have these, simply Import the form into the editor via "File/Import File" in the menu.

Cheers
Chris
0
 
LVL 13

Expert Comment

by:Chris Raisin
ID: 37884543
A couple of changes to two subroutines (simply teplace the current ones with the new ones). I forgot to include the "SaveSetting" after the buttons are clicked - without the "SaveSettings" the "GetSettings" will not work next time :-)

Private Sub cmdFrom_Click()
  Dim cSource As String
  Dim ofs     As Object
  Set ofs = CreateObject("scripting.filesystemobject")
  cdFileMgr.DialogTitle = "Select Source File"
  cdFileMgr.Filter = "Excel Workbooks(*.xl*)|*.xl*"
  cSource = GetSetting(Application.Name, "Setup", "SourceDir", Dir(CurDir()))
  cdFileMgr.InitDir = cSource
  cdFileMgr.flags = cdlOFNFileMustExist
  cdFileMgr.ShowOpen
  If ofs.fileexists(cdFileMgr.Filename) Then
    SaveSetting Application.Name, "Setup", "SourceDir", Dir(cdFileMgr.Filename)
  End If
  Me.txtFrom.Text = cdFileMgr.Filename
  Set ofs = Nothing

End Sub

Open in new window





Private Sub cmdTo_Click()
  Dim cDest As String
  Dim ofs   As Object
  Dim cTabs As String
  'Save the selection of tabs in case when the user
  'is selecting the TO workbook he/she accidentally
  'selects tabs as wellwhen clicking qickly
  cTabs = SaveTabs()
  Set ofs = CreateObject("scripting.filesystemobject")
  cdFileMgr.DialogTitle = "Select Destination File"
  cdFileMgr.Filter = "Excel Workbooks(*.xl*)|*.xl*"
  cDest = GetSetting(Application.Name, "Setup", "DestDir", Dir(Me.txtFrom.Text))
  cdFileMgr.InitDir = cDest
  cdFileMgr.flags = cdlOFNFileMustExist
  cdFileMgr.ShowOpen
  If ofs.folderexists(cdFileMgr.Filename) Then
    SaveSetting Application.Name, "Setup", "DestDir", Dir(cdFileMgr.Filename)
  End If
  Me.txtTo.Text = cdFileMgr.Filename
  'Restore the tabs to how they were
  RestoreTabs cTabs
  Set ofs = Nothing

End Sub

Open in new window

0
 
LVL 13

Expert Comment

by:Chris Raisin
ID: 37884935
Well, after some more testing I ran across a few problems which I have now fixed, but it involves changing the form slightly and the code (quite a bit).
The problems arose with the sheets being opened and closed all over the place in "read/write" mode and "read only" mode.

This new code fixes all that.

Sorry about that.

Uses the same references.

Below is the code, a new picture of the form and the form (in zipped format) as a file for importing into the VBA editor in Excel (File/Import File).

Note: The "Select All"  check box for the Tabs has gone, replaced now by simply clicking the RIGHT Mousebutton when the mouse is inside the listbox of tabs. This will REVERSE the current selections, so if all the Tabs are unchecked then right-mouse clicking will select them all (and vice-versa). By default ALL Tabs are selected when the FROM file is loaded.

Sorry for the changes, but they are needed to ensure it all runs smoothly.

I have tested by sending to a BLANK worksheet and it all seems to copy OK, but the headings are not copied. A really good macro wiould also copy the headings, but they would of course have to be in the mapping. Do you plan to do that, or will you live with the fact that the TO worksheet will always have to be set up first with the headings (created manuallly or copied from another TO worksheet)?

Cheers
Chris

Macro that kicks it off

Option Explicit
'Macro requested in
'http://www.experts-exchange.com/Programming/Languages/Visual_Basic/Q_27628659.html
Public Sub CopyData()
  'In place in case interrupts are present in the buffer
  'and we do not want interrupts to mess up the exection of this macro
  Application.EnableCancelKey = xlDisabled
  'the form must be modal else the interrupts will cut out immediately
  frmCopyData.Show vbModal
  Application.EnableCancelKey = xlInterrupt
End Sub

Open in new window


The New Form:

The design of the new form

And the code within that form:

Option Explicit

Private Sub DisplayBook(cFile As String)
  Dim nWS As Integer
  Dim ofs As Object
  Dim nWnd As Integer
  Dim f   As Object
  Set ofs = CreateObject("scripting.filesystemobject")
  
  For nWS = 1 To Application.Workbooks.Count
    Set f = ofs.getFile(cFile)
    If Application.Workbooks(nWS).Name = f.Name Then
      For nWnd = 1 To Application.Workbooks(nWS).Windows.Count
        If Application.Workbooks(nWS).Windows(nWnd).Caption = ofs.getfilename(cFile) Then
          Application.Workbooks(nWS).Windows(nWnd).Visible = True
          Application.Workbooks(nWS).Windows(nWnd).Activate
        End If
      Next
      Exit For
    End If
  Next

End Sub

Private Sub LoadTabs()
  Dim nTab  As Integer
  Dim oFrom As Workbook
  Dim X As Integer
  frmCopyData.lstTabs.Clear
  Set oFrom = Application.Workbooks.Open(Me.txtFrom.Text, False, False) 'open read only
  For nTab = 1 To oFrom.Worksheets.Count
     Me.lstTabs.AddItem Trim(oFrom.Worksheets(nTab).Name)
  Next
  oFrom.Close
  For nTab = 0 To lstTabs.ListCount
     lstTabs.Selected(nTab) = True
  Next

  Set oFrom = Nothing
End Sub


Private Sub PerformMove()
  Dim nTab  As Integer
  Dim oMap  As Object
  Dim oTo   As Object
  Dim oFrom As Object
  Set oMap = Application.ActiveSheet
  If WorkbookIsOpen(Me.txtFrom.Text) Then
    SaveAndClose Me.txtFrom.Text
  End If
  Set oFrom = Application.Workbooks.Open(Me.txtFrom.Text, , False, True) 'open read only
  If WorkbookIsOpen(Me.txtTo.Text) Then
    SaveAndClose Me.txtTo.Text
  End If
  Set oTo = Application.Workbooks.Open(Me.txtTo.Text, False, False)  'open for Read/write

  For nTab = 1 To Me.lstTabs.ListCount
    TransferData oFrom, oTo, nTab, oMap
  Next
  oTo.Save
  oTo.Close
  oFrom.Close
  Set oMap = Nothing
  Set oTo = Nothing
  Set oFrom = Nothing
  Me.cmdCopyData.Enabled = True
  Me.cmdViewFrom.Enabled = True
  Me.cmdViewTo.Enabled = True

End Sub

Private Sub SaveAndClose(cFile As String)
  Dim nWS As Integer
  Dim ofs As Object
  Dim f   As Object
  Set ofs = CreateObject("scripting.filesystemobject")

  For nWS = 1 To Application.Workbooks.Count
    Set f = ofs.getFile(cFile)

    If Application.Workbooks(nWS).Name = f.Name Then
      Application.Workbooks(nWS).Windows(1).Visible = False
      If Application.Workbooks(nWS).ReadOnly = False Then
        Application.Workbooks(nWS).Save
      End If
      Application.Workbooks(nWS).Close
      Exit For

    End If

  Next

End Sub


Private Sub TransferData(oFrom As Workbook, _
                         oTo As Workbook, _
                         nTab As Integer, _
                         oMap As Worksheet)
  Dim cMapCell    As String
  Dim cFromCell   As String
  Dim cToCell     As String
  Dim nRow        As Long
  Dim cValue      As String
  Dim nBlanks     As Integer
  Dim bSkip       As Boolean
  Dim nProcessed  As Integer
  Dim bProcessing As Boolean
  bProcessing = True

  For nRow = 6 To Application.Rows.Count

    If bProcessing Then
      bSkip = False
      cMapCell = "$B$" + CStr(nRow)
      cFromCell = Range(cMapCell).Value

      If Len(Trim(cFromCell)) = 0 Then
        nBlanks = nBlanks + 1
        bSkip = True

        If nBlanks = 100 Then
  'we can safely assume we have reached end of data input
          bProcessing = False

        End If

      Else
        nBlanks = 0

      End If

      If Not bSkip Then
        If cFromCell <> "blank" Then
          cMapCell = "$D$" + CStr(nRow)
          cToCell = Range(cMapCell).Value

          If cToCell <> "blank" Then
            cValue = oFrom.Worksheets(nTab).Range(cFromCell)
            oTo.Worksheets(nTab).Range(cToCell) = cValue
            nProcessed = nProcessed + 1
            Me.lblCellsMapped.Caption = "Cells Mapped: " + CStr(nProcessed)

          End If

        End If

      End If

  'let other processes run too, to be fair
      DoEvents
    Else
      Exit For

    End If

  Next

End Sub

Private Function WorkbookIsOpen(cFile As String) As Boolean
  Dim nWS As Integer
  Dim ofs As Object
  Dim f   As Object
  Set ofs = CreateObject("scripting.filesystemobject")
  For nWS = 1 To Application.Workbooks.Count
    Set f = ofs.getFile(cFile)
    If Application.Workbooks(nWS).Name = f.Name Then
      WorkbookIsOpen = True
      Exit For
    End If
  Next
  Set ofs = Nothing
End Function


Private Sub cmdClose_Click()
  Unload frmCopyData
  End

End Sub

Private Sub cmdCopyData_Click()
  Dim nTab    As Integer
  Dim nTagged As Integer
  
  For nTab = 0 To Me.lstTabs.ListCount - 1

    If Me.lstTabs.Selected(nTab) Then
      nTagged = nTagged + 1

    End If

  Next

  If nTagged > 0 Then
    lblCellsMapped.Caption = "Processed: 0"
    lblCellsMapped.Visible = True
    cmdCopyData.Enabled = False
    cmdViewFrom.Enabled = False
    cmdViewTo.Enabled = False

    If WorkbookIsOpen(Me.txtFrom.Text) Then
      SaveAndClose Me.txtFrom.Text

    End If

    If WorkbookIsOpen(Me.txtTo.Text) Then
      SaveAndClose Me.txtTo.Text

    End If

    PerformMove
  Else
    MsgBox "Please select some Tabs first!"

  End If

End Sub

Private Sub cmdFrom_Click()
  Dim cSource As String
  Dim ofs     As Object
  Dim oMap    As Object
  Set oMap = Application.ActiveWorkbook
  Set ofs = CreateObject("scripting.filesystemobject")
  cdFileMgr.DialogTitle = "Select Source File"
  cdFileMgr.Filter = "Excel Workbooks(*.xl*)|*.xl*"
  cSource = GetSetting(Application.Name, "Setup", "SourceDir", Dir(CurDir()))
  cdFileMgr.InitDir = cSource
  cdFileMgr.flags = cdlOFNFileMustExist
  cdFileMgr.ShowOpen
  If ofs.getfilename(cdFileMgr.Filename) = oMap.Name Then
    MsgBox "Cannot select file: " + oMap.Name
  Else
    Me.txtFrom.Text = cdFileMgr.Filename
  End If
  Set ofs = Nothing
  Set oMap = Nothing
End Sub

Private Sub cmdViewFrom_Click()
  Dim oFrom As Object
  If Not WorkbookIsOpen(Me.txtFrom.Text) Then
    Set oFrom = Application.Workbooks.Open(Me.txtFrom.Text, False, True)
  End If
  DisplayBook Me.txtFrom.Text
  Set oFrom = Nothing
End Sub

Private Sub cmdViewTo_Click()
  Dim oTo As Object
  If Not WorkbookIsOpen(Me.txtTo.Text) Then
    Set oTo = Application.Workbooks.Open(Me.txtTo.Text, False, True)
  End If
  DisplayBook Me.txtTo.Text
  Set oTo = Nothing
End Sub

Private Sub cmdTo_Click()
  Dim cDest As String
  Dim ofs   As Object
  Dim oMap As Object
  Set oMap = Application.ActiveWorkbook
  
  'Save the selection of tabs in case when the user
  'is selecting the TO workbook he/she accidentally
  'selects tabs as well when clicking qickly
  Set ofs = CreateObject("scripting.filesystemobject")
  cdFileMgr.DialogTitle = "Select Destination File"
  cdFileMgr.Filter = "Excel Workbooks(*.xl*)|*.xl*"
  cDest = GetSetting(Application.Name, "Setup", "DestDir", Dir(Me.txtFrom.Text))
  cdFileMgr.InitDir = cDest
  cdFileMgr.flags = cdlOFNFileMustExist
  cdFileMgr.ShowOpen
  If ofs.getfilename(cdFileMgr.Filename) = oMap.Name Then
    MsgBox "Cannot select file: " + oMap.Name
  Else
    Me.txtTo.Text = cdFileMgr.Filename
  End If
  'Restore the tabs to how they were
  Set ofs = Nothing
End Sub

Public Sub lstTabs_MouseDown(ByVal Button As Integer, ByVal Shift As Integer, ByVal X As Single, ByVal Y As Single)
  Dim nTab As Integer
  If Button = 2 Then    'the right button
    For nTab = 0 To lstTabs.ListCount - 1
      lstTabs.Selected(nTab) = Not (lstTabs.Selected(nTab))
    Next
  End If
End Sub


Private Sub txtFrom_Change()
  Dim ofs As Object
  Set ofs = CreateObject("scripting.filesystemobject")

  If ofs.fileexists(Me.txtFrom.Text) Then
    LoadTabs
    Me.cmdViewFrom.Enabled = True

    If ofs.fileexists(Me.txtTo.Text) Then
      Me.cmdCopyData.Enabled = True

    End If

  Else
    Me.cmdCopyData.Enabled = False
    Me.cmdViewFrom.Enabled = False

  End If

  Set ofs = Nothing

End Sub

Private Sub txtTo_Change()
  Dim ofs As Object
  Set ofs = CreateObject("scripting.filesystemobject")

  If ofs.fileexists(Me.txtTo.Text) Then
    Me.cmdViewTo.Enabled = True

    If ofs.fileexists(Me.txtFrom.Text) Then
      Me.cmdCopyData.Enabled = True
    Else
      Me.cmdCopyData.Enabled = False

    End If

  Else
    Me.cmdCopyData.Enabled = False
    Me.cmdViewTo.Enabled = False

  End If

  Set ofs = Nothing

End Sub

Open in new window


Now finally the attached form with the code in it:
FRMCOPYDATA.ZIP
0
 

Author Comment

by:DMKetcher
ID: 37886291
Chris -- I am fine with the headings being set in the output workbook. Or I can add them if I get time. I hope to be able to work on testing this in the afternoon. Will get back to you with my next questions. Thanks!!
0
 
LVL 13

Expert Comment

by:Chris Raisin
ID: 37913454
How are things progressing?

Cheers
Chris
0
 

Author Comment

by:DMKetcher
ID: 37914664
Chris -- I am so sorry but I have not had time to test it and may not get to it until next weekend. I hope that is ok. Things have changed a little with my work. I will get back to you as soon as I can get at it. Maybe an evening this week...

Janis
0
 
LVL 13

Expert Comment

by:Chris Raisin
ID: 37915756
No worries - take your time!  :-)

Cheers
Chris
0
 

Author Comment

by:DMKetcher
ID: 37917040
Chris,

I was able to begin testing this afternoon but I am stuck at the beginning. I unzipped the form in a folder and opened a blank excel macro enabled workbook. Had trouble with the instructions... I thought you developed the full form. Do I need to create it? Your reference to grabbing text boxes, labels etc. Or was that just for my education in case I need to do this myself at some point? So I can't get any movement on this. When I click on Insert it gives me a dropdown with Userform, Module and Class module as options that are available. There are two others in greybar. So I select Userform and it just brings up the toolbox. It does not give me an option to locate the form you developed and import it. So in short, I don't know what I am doing. I think I need more explicit directions. I am getting all mixed up. Can you send me a word document with steps and screen shots? I knkow it is a lot to ask but I think we are close on this. I wish I could give you a thousand points.

Janis


If you are in the VBA Editor you just click on "Insert" and then  "From" and it will take you to the Form Designed. You need to view the Toolbox to grab Textboxes, Labels, buttons etc.

To view the Toolbox you click on "View/Toolbox". The toolbox will appear, provided you have first selected the form or one of the controls you have already placed on the form.

Scroll down in the References to see if you can see "Microsoft Common Dialog (any version) and Microsoft Forms (any version) and click on them to select them

If they do not appear there, click on the Browse button and look in :
"C:\Windows\System32" for files with names like:
                        "C:\Windows\System32\ComDlg32.ocx"
                        "C:\Windows\System32\FM20.dll"       (might be earlier version than  2.0)
 
You already have the Excel 14.0 Object Library.
0
 
LVL 13

Expert Comment

by:Chris Raisin
ID: 37922248
Yes my instructions to you about creating a form were educational only.

I have created the form for you.

I will put together a small "movie" on the procedure in about 3 hours since I am teaching at the moment. That should clear everything up.

Stand by.

Cheers
Chris
0
 

Author Comment

by:DMKetcher
ID: 37923966
No worries -- I still appreciate all you are doing for me. Thanks!
0
 
LVL 13

Expert Comment

by:Chris Raisin
ID: 37926210
OK, the tutorial is attached, along with a slightly changed (and renamed) file called "CopyData.zip".

The file has changed because I have included the code for the macro itself this time.

The tutorial is in flv format since this is the only format for video files allowed by EE within this part of the EE website. To view it you need an flv player.
The one I use is called "Any flv Player" available from http://www.any-flv-player.com/
You should be able to download the "Trial" version to simply view flv files.
There are probably other free flv players around.

If you cannot get an flv player, let me know and I can upload the tutorial in mp4 or wmv format via  EE-Stuff (another way I can get files to you).

By the way, you will notice in the tutorial I happened upon a "bug" in the code.This has now been fixed and so you should not encounter it when you run the macro.

I have re-zipped the FRMCOPYDATA.ZIP file and renamed it to "CopyData.zip".
It has code slightly amended within the form and also includes the code for the macro in a separate file (as per the tutorial).

(Please ignore the earlier file supplied.)

For documentation purposes I also include the amended code below for both the macro and for the code within the form. No need to copy/paste this code since it is all supplied in the zip file.

I suggest you unzip the zip file into a folder such as "C:\Temp" and then play the tutorial to see how to deal with the code.

Let me know how you get on.

Cheers
Chris

Macro Code:
Option Explicit

'Macro requested in
'http://www.experts-exchange.com/Programming/Languages/Visual_Basic/Q_27628659.html
Public Sub CopyData()
  'In place in case interrupts are present in the buffer
  'and we do not want interrupts to mess up the exection of this macro
  Application.EnableCancelKey = xlDisabled
  'the form must be modal else the interrupts will cut out immediately
  frmCopyData.Show vbModal
  Application.EnableCancelKey = xlInterrupt
End Sub

Open in new window


And the code within the form:
Option Explicit

Private Sub DisplayBook(cFile As String)
  Dim nWS As Integer
  Dim ofs As Object
  Dim nWnd As Integer
  Dim f   As Object
  Set ofs = CreateObject("scripting.filesystemobject")
  
  For nWS = 1 To Application.Workbooks.Count
    Set f = ofs.getFile(cFile)
    If Application.Workbooks(nWS).Name = f.Name Then
      For nWnd = 1 To Application.Workbooks(nWS).Windows.Count
        If Application.Workbooks(nWS).Windows(nWnd).Caption = ofs.getfilename(cFile) Then
          Application.Workbooks(nWS).Windows(nWnd).Visible = True
          Application.Workbooks(nWS).Windows(nWnd).Activate
        End If
      Next
      Exit For
    End If
  Next

End Sub

Private Sub LoadTabs()
  Dim nTab  As Integer
  Dim oFrom As Workbook
  Dim X As Integer
  frmCopyData.lstTabs.Clear
  Set oFrom = Application.Workbooks.Open(Me.txtFrom.Text, False, False) 'open read only
  For nTab = 1 To oFrom.Worksheets.Count
    Me.lstTabs.AddItem Trim(oFrom.Worksheets(nTab).Name)
  Next
  oFrom.Close
  For nTab = 0 To lstTabs.ListCount - 1
    lstTabs.Selected(nTab) = True
  Next

  Set oFrom = Nothing
End Sub

Private Sub PerformMove()
  Dim nTab  As Integer
  Dim oMap  As Object
  Dim oTo   As Object
  Dim oFrom As Object
  Set oMap = Application.ActiveSheet
  If WorkbookIsOpen(Me.txtFrom.Text) Then
    SaveAndClose Me.txtFrom.Text
  End If
  Set oFrom = Application.Workbooks.Open(Me.txtFrom.Text, , False, True) 'open read only
  If WorkbookIsOpen(Me.txtTo.Text) Then
    SaveAndClose Me.txtTo.Text
  End If
  Set oTo = Application.Workbooks.Open(Me.txtTo.Text, False, False)  'open for Read/write

  For nTab = 1 To Me.lstTabs.ListCount
    TransferData oFrom, oTo, nTab, oMap
  Next
  oTo.Save
  oTo.Close
  oFrom.Close
  Set oMap = Nothing
  Set oTo = Nothing
  Set oFrom = Nothing
  Me.cmdCopyData.Enabled = True
  Me.cmdViewFrom.Enabled = True
  Me.cmdViewTo.Enabled = True

End Sub

Private Sub SaveAndClose(cFile As String)
  Dim nWS As Integer
  Dim ofs As Object
  Dim f   As Object
  Set ofs = CreateObject("scripting.filesystemobject")

  For nWS = 1 To Application.Workbooks.Count
    Set f = ofs.getFile(cFile)

    If Application.Workbooks(nWS).Name = f.Name Then
      Application.Workbooks(nWS).Windows(1).Visible = False
      If Application.Workbooks(nWS).ReadOnly = False Then
        Application.Workbooks(nWS).Save
      End If
      Application.Workbooks(nWS).Close
      Exit For

    End If

  Next

End Sub


Private Sub TransferData(oFrom As Workbook, _
                         oTo As Workbook, _
                         nTab As Integer, _
                         oMap As Worksheet)
  Dim cMapCell    As String
  Dim cFromCell   As String
  Dim cToCell     As String
  Dim nRow        As Long
  Dim cValue      As String
  Dim nBlanks     As Integer
  Dim bSkip       As Boolean
  Dim nProcessed  As Integer
  Dim bProcessing As Boolean
  bProcessing = True

  For nRow = 6 To Application.Rows.Count

    If bProcessing Then
      bSkip = False
      cMapCell = "$B$" + CStr(nRow)
      cFromCell = Range(cMapCell).Text

      If Len(Trim(cFromCell)) = 0 Then
        nBlanks = nBlanks + 1
        bSkip = True

        If nBlanks = 100 Then
  'we can safely assume we have reached end of data input
          bProcessing = False

        End If

      Else
        nBlanks = 0

      End If

      If Not bSkip Then
        If cFromCell <> "blank" Then
          cMapCell = "$D$" + CStr(nRow)
          cToCell = Range(cMapCell).Value

          If cToCell <> "blank" Then
            cValue = oFrom.Worksheets(nTab).Range(cFromCell)
            oTo.Worksheets(nTab).Range(cToCell) = cValue
            nProcessed = nProcessed + 1
            Me.lblCellsMapped.Caption = "Cells Mapped: " + CStr(nProcessed)

          End If

        End If

      End If

  'let other processes run too, to be fair
      DoEvents
    Else
      Exit For

    End If

  Next

End Sub

Private Function WorkbookIsOpen(cFile As String) As Boolean
  Dim nWS As Integer
  Dim ofs As Object
  Dim f   As Object
  Set ofs = CreateObject("scripting.filesystemobject")
  For nWS = 1 To Application.Workbooks.Count
    Set f = ofs.getFile(cFile)
    If Application.Workbooks(nWS).Name = f.Name Then
      WorkbookIsOpen = True
      Exit For
    End If
  Next
  Set ofs = Nothing
End Function


Private Sub cmdClose_Click()
  Unload frmCopyData
  End

End Sub

Private Sub cmdCopyData_Click()
  Dim nTab    As Integer
  Dim nTagged As Integer
  
  For nTab = 0 To Me.lstTabs.ListCount - 1

    If Me.lstTabs.Selected(nTab) Then
      nTagged = nTagged + 1

    End If

  Next

  If nTagged > 0 Then
    lblCellsMapped.Caption = "Processed: 0"
    lblCellsMapped.Visible = True
    cmdCopyData.Enabled = False
    cmdViewFrom.Enabled = False
    cmdViewTo.Enabled = False

    If WorkbookIsOpen(Me.txtFrom.Text) Then
      SaveAndClose Me.txtFrom.Text

    End If

    If WorkbookIsOpen(Me.txtTo.Text) Then
      SaveAndClose Me.txtTo.Text

    End If

    PerformMove
  Else
    MsgBox "Please select some Tabs first!"

  End If

End Sub

Private Sub cmdFrom_Click()
  Dim cSource As String
  Dim ofs     As Object
  Dim oMap    As Object
  Set oMap = Application.ActiveWorkbook
  Set ofs = CreateObject("scripting.filesystemobject")
  cdFileMgr.DialogTitle = "Select Source File"
  cdFileMgr.Filter = "Excel Workbooks(*.xl*)|*.xl*"
  cSource = GetSetting(Application.Name, "Setup", "SourceDir", Dir(CurDir()))
  cdFileMgr.InitDir = cSource
  cdFileMgr.flags = cdlOFNFileMustExist
  cdFileMgr.ShowOpen
  If ofs.getfilename(cdFileMgr.Filename) = oMap.Name Then
    MsgBox "Cannot select file: " + oMap.Name
  Else
    Me.txtFrom.Text = ""
    Me.txtFrom.Text = cdFileMgr.Filename
  End If
  Set ofs = Nothing
  Set oMap = Nothing
End Sub

Private Sub cmdViewFrom_Click()
  Dim oFrom As Object
  If Not WorkbookIsOpen(Me.txtFrom.Text) Then
    Set oFrom = Application.Workbooks.Open(Me.txtFrom.Text, False, True)
  End If
  DisplayBook Me.txtFrom.Text
  Set oFrom = Nothing
End Sub

Private Sub cmdViewTo_Click()
  Dim oTo As Object
  If Not WorkbookIsOpen(Me.txtTo.Text) Then
    Set oTo = Application.Workbooks.Open(Me.txtTo.Text, False, True)
  End If
  DisplayBook Me.txtTo.Text
  Set oTo = Nothing
End Sub

Private Sub cmdTo_Click()
  Dim cDest As String
  Dim ofs   As Object
  Dim oMap As Object
  Set oMap = Application.ActiveWorkbook
  
  'Save the selection of tabs in case when the user
  'is selecting the TO workbook he/she accidentally
  'selects tabs as well when clicking qickly
  Set ofs = CreateObject("scripting.filesystemobject")
  cdFileMgr.DialogTitle = "Select Destination File"
  cdFileMgr.Filter = "Excel Workbooks(*.xl*)|*.xl*"
  cDest = GetSetting(Application.Name, "Setup", "DestDir", Dir(Me.txtFrom.Text))
  cdFileMgr.InitDir = cDest
  cdFileMgr.flags = cdlOFNFileMustExist
  cdFileMgr.ShowOpen
  If ofs.getfilename(cdFileMgr.Filename) = oMap.Name Then
    MsgBox "Cannot select file: " + oMap.Name
  Else
    Me.txtTo.Text = cdFileMgr.Filename
  End If
  'Restore the tabs to how they were
  Set ofs = Nothing
End Sub

Public Sub lstTabs_MouseDown(ByVal Button As Integer, ByVal Shift As Integer, ByVal X As Single, ByVal Y As Single)
  Dim nTab As Integer
  If Button = 2 Then    'the right button
    For nTab = 0 To lstTabs.ListCount - 1
      lstTabs.Selected(nTab) = Not (lstTabs.Selected(nTab))
    Next
  End If
End Sub


Private Sub txtFrom_Change()
  Dim ofs As Object
  If Len(Me.txtFrom.Text) > 0 Then
    Set ofs = CreateObject("scripting.filesystemobject")
    If ofs.fileexists(Me.txtFrom.Text) Then
      LoadTabs
      Me.cmdViewFrom.Enabled = True

      If ofs.fileexists(Me.txtTo.Text) Then
        Me.cmdCopyData.Enabled = True

      End If

    Else
      Me.cmdCopyData.Enabled = False
      Me.cmdViewFrom.Enabled = False

    End If

    Set ofs = Nothing
  End If

End Sub

Private Sub txtTo_Change()
  Dim ofs As Object
  Set ofs = CreateObject("scripting.filesystemobject")

  If ofs.fileexists(Me.txtTo.Text) Then
    Me.cmdViewTo.Enabled = True

    If ofs.fileexists(Me.txtFrom.Text) Then
      Me.cmdCopyData.Enabled = True
    Else
      Me.cmdCopyData.Enabled = False

    End If

  Else
    Me.cmdCopyData.Enabled = False
    Me.cmdViewTo.Enabled = False

  End If

  Set ofs = Nothing

End Sub

Open in new window

CopyData.zip
CopyData.flv
0
 
LVL 13

Expert Comment

by:Chris Raisin
ID: 37944844
Did you succeed in viewing the tutorial?
0
 

Author Comment

by:DMKetcher
ID: 37946392
Goodness - I didn't even see it in my email. I will look at it either tonight or tomorrow night. I am now traveling with a client project (same one you are helping me with) so it is hard to get this type of work done during the day. I am sure it will enlighten me. Thank you so much for everything.
0
 

Author Comment

by:DMKetcher
ID: 37967495
Hi Chris, back at it.

I don't have the media player required and can't download anything on my corportate laptop without involving our IT dept. who will give me a hard time. Everyone in this company avoids contact with them if at all possible. I have windows media player which will not play this video. So I am back to square one with trying to make all your hard work happen on my laptop. Maybe if I set up a guide and you fill in the blanks.

1. The unzipped file is in a folder.
2. The input or "From" workbook is already created - in my example it is called
"EE-Move-from-P02-03.25.2012"
3. The output or "To" workbook will hold the macro(s).

In your macro a form pops up that requires entry of the "From" and "To" file names along with the tab names. The tab names never change so could this be made more "hard-coded"?

I do have Adobe Captivate installed and could play a demo on that tool. Can you create something to demo this for me in Captivate?

I will try to get this going and will let you know if I have success.

Thanks, Janis
0
 

Author Comment

by:DMKetcher
ID: 37967563
I worked a little more on it. I got a compile error but I consider that progress. See the attached word document with some screen shots. Am I missing one of the files? Thanks!

Janis
Chris-Response-05.14.2012.docx
0
 

Author Comment

by:DMKetcher
ID: 37967579
Chris -- one more comment that will help you debug this. The form did pop up. I got the error when I selected the box to enter or find the input or from file. We are getting there!
0
 
LVL 13

Expert Comment

by:Chris Raisin
ID: 37968377
The code is referencing an object which is "hidden" on ther form named "cdFileMgr"

You need to look at the form and make sure the object is there and is named correctly.

Select the form within the explorer on the left hand side and look for the object. Click ONCE on it to select it and then press F4 to see its properties. Check the name is correct.

Cheers
Chris

cdFileMgr object on the form
0
 

Author Comment

by:DMKetcher
ID: 37970189
I think it is not imported.

"Select the form within the explorer on the left hand side and look for the object. Click ONCE on it to select it and then press F4 to see its properties. Check the name is correct."

I don't think I have that object imported. I am attaching a word document with some screen shots of what I am seeing. Hope it helps. I know it is likely something simple.
Chris-Message-05.15.2012-1.docx
0
 
LVL 13

Accepted Solution

by:
Chris Raisin earned 500 total points
ID: 37970844
Please follow the instructions below.

Cheers
Chris



Step 1
Step 2 and 3
Step 4
References
Step 5
Step 6
Step 7
Step 8
0
 

Author Comment

by:DMKetcher
ID: 37985989
Chris -- I missed this message. Sorry. i will work on it again this weekend. I appreciate the documentation.

Have a nice weekend!

Janis
0
 

Author Comment

by:DMKetcher
ID: 38011760
Back at it. Please see my attachment
Craisen-Msg-1-05252012.docx
0
 
LVL 13

Expert Comment

by:Chris Raisin
ID: 38017827
I can't seem to download that file.

Can you change it to a PDF?

Cheers
Chris
0
 

Author Comment

by:DMKetcher
ID: 38033452
Chris -- I thought I had sent this to you.
Craisen-Msg-1-05252012.pdf
0
 
LVL 13

Expert Comment

by:Chris Raisin
ID: 38034851
No, you had supplied it as as ".docx" file (see above).

I will now look at the PDF file - Stand by.

Cheers
Chris
0
 
LVL 13

Expert Comment

by:Chris Raisin
ID: 38034869
Please re-read my instructions under 37970844 above (the digrams).

You have not put on the form the "CommonDialog" object as detailed in steps 3 and 4.
That is all that is missing.

Make sure you name the object: "cdFileMgr"

If you have the reference set up correctly as shown between steps 4 and 5 above, then when you import the Form I supplied earlier the "cdFileMgr" should import OK without error, but since you have not done that, you need to set it up exactly as I outlined in the Steps above.

Please confirm and let me see a snapshot of your "references" (just like I did in between steps 4 and 5 above)

Cheers
Chris
0
 

Author Comment

by:DMKetcher
ID: 38036319
Thanks Chris - I won't get to this until the weekend. Will be back with you Monday.

Janis
0
 
LVL 13

Expert Comment

by:Chris Raisin
ID: 38047100
Thanks Janis.

I have had a lot of trouble with Experts Exchange after they cancelled my membership because I had not earned enough points during the month.

(I was bogged down on your question and two others, all of which are of a more difficult nature to the norm).

It has taken them a while to realise the mistake, but now I need points ASAP, so I look forward to your success in getting this problem solved ASAP.

I think if you follow my tutorial "diagrams" exactly (especially in regard to the "references" and the drawing of the File Dialog in steps 4 to 7) then it should all work for you.

Please advise

Cheers
Chris
0
 

Author Comment

by:DMKetcher
ID: 38050357
I will go ahead and close out and award you your points. You have been great. Sorry this has been so complicated.
0
 

Author Closing Comment

by:DMKetcher
ID: 38050359
Chris did a great job staying with me on this difficult project.
0
 
LVL 13

Expert Comment

by:Chris Raisin
ID: 38051836
Thanks Janis - but did you finally get it to work OK?

Cheers
Chris
0

Featured Post

Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

Join & Write a Comment

The System Center Operations Manager 2012, known as SCOM, is a part of the Microsoft system center product that provides the user with infrastructure monitoring and application performance monitoring. SCOM monitors:   Windows or UNIX/LinuxNetwo…
User Beware!  This is a rather permanent solution to removing your email from an exchange server.  The only way to truly go back is to have your exchange administrator restore your mailbox from backups.  This is usually the option of last resort.  A…
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.

705 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now