Solved

Vba code to create folder ans archive WBk files

Posted on 2013-01-23
30
322 Views
Last Modified: 2013-02-01
Hi Experts

I need a macro that will archive all the workbooks in the following path:-        Dir("C:\Documents and Settings\Test\*.xls")
To a folder name the user specify and creates via pop up window...

Once the files have been archived and If the user acciendtially tries to copy across the source files and the folder is empty then magbox you have no files on current folder...

If updated file are saved in the above file path and the user which to save these to the created folder and they are of the same name then over write previous version and move..
0
Comment
Question by:route217
  • 15
  • 15
30 Comments
 
LVL 29

Expert Comment

by:gowflow
ID: 38809679

Once the files have been archived and If the user acciendtially tries to copy across the source files and the folder is empty then magbox you have no files on current folder...

What do you mean via a macro or from windows Explorer ?

and when you say


I need a macro that will archive all the workbooks in the following path:-        Dir("C:\Documents and Settings\Test\*.xls")
To a folder name the user specify and creates via pop up window...

You want the source files to be MOVED to the new location or COPIED ??

gowflow
0
 

Author Comment

by:route217
ID: 38810344
Moved...
Assume the following sequence:-

1. User clicks macro button
2. Pop up window or form appears asking user to create a folder where file are going to moved to... So if I create folder called Jan 13,

2a. Then click ok and move my file from c:\ to a specified file path in vba code..
3. So if I try to repeat set 2 and 2a and create a folder called Feb 13 and re try to move the file and the source path where the file where previously saved is empty then Msg...
4. If I mid week receive new workbooks and I want to move these from the c:\ to the Jan13 folder and the new file are the same name workbook as the one already there then replace with current files...
5. Close down form or macro after task is complete...
0
 

Author Comment

by:route217
ID: 38811949
Hi gowflow

Any feedback on this question??
0
 
LVL 29

Expert Comment

by:gowflow
ID: 38812549
I have been reading your steps several times around and seems you are missing steps.
From what I understood let me rephrase and tell you how I understood it or better how I envision the solution to be and if at any step I am mistaken please correct me then.

1) We have a workbook in which we have a sheet called Main.
2) On this sheet Main there are 3 command buttons like this


Folder to Move From                       Folder to Move to
|-----------------------------|                   |---------------------------|                    |----------------------|
|                                   |                   |                                |                    |   Move Files   |
|-----------------------------|                   |---------------------------|                    |----------------------|

So any time you can do the following:
a- press on Folder to move from and select any folder you want.
b- press on folder to Move to and select any folder you want
c- Activate Macro Move Files that will move content of folder From to Folder Move.
if any of the From or Move button are not selected with a folder then Macro Move Files    will advise you that it cannot proceed.

also, if files are move to folder Move and already exist they are overriten !!

Is that what you want ??
gowflow
0
 

Author Comment

by:route217
ID: 38813172
Agree...totally
0
 
LVL 29

Expert Comment

by:gowflow
ID: 38813364
One last question you have Excel 2003 or 2007 ? what is preferable to you ?
gowflow
0
 

Author Comment

by:route217
ID: 38813451
2007 - thanks gow flow
0
 
LVL 29

Expert Comment

by:gowflow
ID: 38813798
your welcome. Working on your solution
gowflow
0
 

Author Comment

by:route217
ID: 38813806
Gow flow....

Do u require any further info from me...or have u completed the development?
0
 
LVL 29

Expert Comment

by:gowflow
ID: 38813818
Will revert during the course of the day.
gowflow
0
 

Author Comment

by:route217
ID: 38815770
Hi gow flow

Any updates???
0
 
LVL 29

Expert Comment

by:gowflow
ID: 38816216
if you want something good u need to be patient.
gowflow
0
 

Author Comment

by:route217
ID: 38816469
Understood - over and out..
0
 
LVL 29

Accepted Solution

by:
gowflow earned 500 total points
ID: 38818380
Well here it is.

After you load this file run it without enabling macroes first time as you need to add a library. So after you load it goto VBA and on hte tools menu choose References and from the list select the following library:
Microsoft Scripting Runtime.

once selected press ok save the workbook then run it \enabling macroes.

Here is how it works.
This file when is run consist of 2 windows From and To you will need to select Red and Blue buttons to select folders From and To upon selection it will display the available files in the coresponding lists.

You have selveral buttons
> selected
>> All
< selected
<< All

you may select files and only move the selected files and tto select files either click and select files following eachothers or you can press on CTRL and click several files that do not follow each others.

Pressing on the button with All will move all the files from origin to destination.

If you want to clear selections press on Clear Selections.

I suggest you make up 2 folders and put several files in each and select them via the macro and try to move in between these folders the files and check the results.

Also if same file exist in origin and destination then when it is moved it is overriden.

Let me know your comments. Last the Excel application get hidden when the form is shown don't worry it is on purpose to have a real feel look.
gowlfow
MoveFolders.xlsm
0
 
LVL 29

Expert Comment

by:gowflow
ID: 38822591
Did you try the proposed solution ?
gowflow
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:route217
ID: 38824108
trying this weekend....no internet bad weather snow and more snow...
0
 
LVL 29

Expert Comment

by:gowflow
ID: 38824318
ok no problem
gowflow
0
 

Author Comment

by:route217
ID: 38825923
Hi gow flow

Firstly, excellent but if coding but..  Need a small change I need this to be workbook specific and open up with a certain workbook only...

Can we kindly amend - please...
0
 
LVL 29

Expert Comment

by:gowflow
ID: 38826067
yes for sure you have it open all out we can restrict. Usually common practice for most programers as it is much easier for 'them' is to restrict and put it in the coding I usually do the opposit let the user have all the flexibility.

But here I understand that you don't want to give that 'flexibility' to your users. So give me the specific details like what folder and which name it is need to be fixed is it in the From or to ? so I can modify.

gowflow
0
 

Author Comment

by:route217
ID: 38826216
Hi gow flow

Not sure I totally understand the question, but I would like the following:

Workbook final template is open

1. User click macro button
2. Gow flows user form pops up
3. Files current stored at c:\outlookattachments\cmu attachments\
4. Users selects files from here
5. Move file to c:\ what path they wish to do so


That's it...
0
 
LVL 29

Expert Comment

by:gowflow
ID: 38826305
Sorry it seems we speak diffrent languages I do not understand what you are saying:

1) What is workbook final template open ??? what is this ???
2) User click on macro button What macro In the file I attached there are several buttons I gave you a full solution what else more you want ??? You have a button to transfer from to all the files or the selected files and vice versa and a button to clear selections !!!

3) Gow flows user from pops up ????????? What the hell is this phrase it is chineese to me with all due respect to chineese people but it is a language I do not understand !!! What do you mean by item 2 ???

4) Your item number 3) files stored at: c:\outlookattachments\cmu attachments\
do you mean you want the From button folder to only accept setting of this folder ONLY ???

Please clarify
gowflow
0
 

Author Comment

by:route217
ID: 38826381
Hi gow flow...

Point 1 and 2...

The form is prefect...my question is how to I assign the form to work from a particular workbook...

And point 4. Yes..

And apologies if I was talking crap....
0
 
LVL 29

Expert Comment

by:gowflow
ID: 38826394
ok
lets deal reverse. Point 4 noted and can be fixed.

Point 1 and 2  I still do not understand what do you mean by:
I assign the form to work from a particular workbook...

Do you mean you only want to view certain workbook in the From Window for that specific folder that is in point 4) ???
If this is what you meant then yes as we are limiting access to a certain folder then we limit access to view whatever we want as long as I have a certain pattern of workbboks like workbooks starting with Acc.... or Daily ... or whatever....

Still if I did not understand well please clarify.
gowflow
0
 

Author Comment

by:route217
ID: 38826424
Point 1 and 2....
Current the move file form open from its own WBk...

Why can I not operate it from a workbook I choose???

Nothing else....everything else is more then excellent
0
 
LVL 29

Expert Comment

by:gowflow
ID: 38826468
Still not clear to me. But let me rephrase what I understood from


Point 1 and 2....
Current the move file form open from its own WBk...

Why can I not operate it from a workbook I choose???


I posted a file that have a macro. When the file is run it opens a Form and the Excel attached to that file disapear to only show you the tools to do a move. In that tool we can limit .. and do what ever we want but at the end it will remain in that specific workbook that all this will be done.

From your question above do I understand that you would like this 'tool' to be available in any workbook that you choose ? Is that what you want ? like you would have a workbook that contain data info that is called abc.xls and you want that workbook to have these functionalities ? if yes then what you are requesting is to have this functionality built in a toolbar of Excel like each time you open Excel ... then you would have this New Menu that would be available that say would be called Move Files and once clicked on ... then it will pop up this form to do the moving ... Is that what you want ???

If your answer is yes then please allow me a small comment here: don't you think that what was provided is already WAYYYYY more and above a normal question ??? for sure anything we can immagine can be done and executed provided we know how but I think it takes time and effort.

right ?
if I am totally of-course pls correct my understanding.
gowflow
0
 

Author Comment

by:route217
ID: 38826575
Hi gow flow

Apologies once again...I have looked at what has been provided and I am more then happy with what the macro does and once again sorry if I sound rude in any way...

Much appreciated for all you're assistance...
0
 
LVL 29

Expert Comment

by:gowflow
ID: 38826585
glad that settles it. If you need to implement into menu bar or whatever then you are welcome to post new questionsand I will be glad to assist. No offence but it seems you went at first very lightly on a nice project that people would pay money to get !!! not trying to be sarcastic but it is plain reality.
gowflow
0
 

Author Comment

by:route217
ID: 38826630
Agree...

Thanks once again...
0
 
LVL 29

Expert Comment

by:gowflow
ID: 38826673
your welcome. Pls feel free to put a link on a new question you may need help with I will be glad to assist.
gowflow
0
 

Author Comment

by:route217
ID: 38843576
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

What is a Form List Box? (skip if you know this) The forms List Box is the alternative to the ActiveX list box. If you are using excel 2007, you first make sure you have a developer tab (click the Orb)->"Excel Options"->Popular->"Show Developer tab…
Introduction This Article is a follow-up to my Mappit! Addin Article (http://www.experts-exchange.com/A_2613.html), it was inspired by an email posting I made to EUSPRIG (http://www.eusprig.org/index.htm), I will briefly cover: 1) An overvie…
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 a scrolling table in Microsoft Excel using the INDEX function.

707 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

12 Experts available now in Live!

Get 1:1 Help Now