Vba code to create folder ans archive WBk files
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..
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..
ASKER
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...
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...
ASKER
Hi gowflow
Any feedback on this question??
Any feedback on this question??
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
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
ASKER
Agree...totally
One last question you have Excel 2003 or 2007 ? what is preferable to you ?
gowflow
gowflow
ASKER
2007 - thanks gow flow
your welcome. Working on your solution
gowflow
gowflow
ASKER
Gow flow....
Do u require any further info from me...or have u completed the development?
Do u require any further info from me...or have u completed the development?
Will revert during the course of the day.
gowflow
gowflow
ASKER
Hi gow flow
Any updates???
Any updates???
if you want something good u need to be patient.
gowflow
gowflow
ASKER
Understood - over and out..
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Did you try the proposed solution ?
gowflow
gowflow
ASKER
trying this weekend....no internet bad weather snow and more snow...
ok no problem
gowflow
gowflow
ASKER
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...
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...
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
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
ASKER
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...
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...
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
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
ASKER
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....
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....
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
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
ASKER
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
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
Still not clear to me. But let me rephrase what I understood from
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
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
ASKER
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...
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...
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
gowflow
ASKER
Agree...
Thanks once again...
Thanks once again...
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
gowflow
ASKER
Hi gow flow
Can u shed some light on https://www.experts-exchange.com/questions/28016785/Macro-to-populate-unique-values-into-table.html
Can u shed some light on https://www.experts-exchange.com/questions/28016785/Macro-to-populate-unique-values-into-table.html
What do you mean via a macro or from windows Explorer ?
and when you say
You want the source files to be MOVED to the new location or COPIED ??
gowflow