Hi
I have a folder that contain a list of files in shortcut type. So that i can just click it without going to different drives and paths to open those files and it really save my time.
After updating those files, i need to zip it up. There are many files to zip up and it takes time by going to its path one by one. Do you guys know is there any method to zip up those files without going to its relevant drives and paths?
Thanks
Last edited by mingali; 09-02-2009 at 06:33 AM.
Thanks for your prompt answer.
as you are aware, my goal is to take less time to zip up those files.
Does the first method requires me to go to all the drives and paths to copy those files to the common folder after i update the files everytime?
Unfortunately, the second method is out of question as my company does not allow us to instal any software.
Rin de bruin has some solutions at his site
i just tried an experiment. when the files in the folder are shortcut type, the zip up version is also shortcut type. So it doesnt work in this way.
you mentioned double click the files, does it mean i need to open the file from the folder and zip it one by one? Some files are so large that it take two or three minutes to open. So in fact, it is slower than going to different path. If it is not what you meant, can you please give me a clearer explanation as i have misunderstood?
FYI, after updating those files, i need to close it for my boss to review. After he reviewed only then i zip those files up.
Thanks
Last edited by mingali; 08-27-2009 at 07:35 AM. Reason: to add FYI line
Maybe this will help you collect a copy of the orginal files, based on the shortcut information, into the folder of short cuts.
Then you can select all .xls files and zip. Once you have a zip you can delete the copied .xls files
So I have a folder in C:\temp called MyShortcuts. This folder contains shortcuts to xls files in other locations.
Please Login or Register to view this content.
The code currently only displays information in the immediate window about which files and where they are stored.
Hi Arthurbr & Andy,
I dont know how to use Marco. So i am not sure how to apply it. Sorry about it.
Open new workbook.
ALT+F11 to go to vbe
Insert Module
Paste code
CTRL+G to display immediate window, where the output will be displayed
Change folder locations names to suit your setup
With the cursor in the code F5 to execute code
The immediate window should contain source and destination file details
If all looks correct remove '' to un comment FileCopy command and re run.
Andy,
Thanks for your help. I think now i am really testing your patient. Hope you can bear with me.
In your code:
1) strPath = "C:\temp\MyShortcuts\" ............ i will need to change to my folder's location, right? eg strPath - "C:\user\mingali\desktop\new folder\"
2) strFile = Dir(strPath & "*.lnk")............ i will need to change to "strFile = Dir(strPath & "P&L July09.xls") and keep repeat all the files name i want by rewrite (2)?
In vbe,
1) i pasted the code to module, then open immediate window. However, it is blank. Shall it show the output?
I did not proceed further.
Thanks again for your help
1) strPath = "C:\temp\MyShortcuts\" ............ i will need to change to my folder's location, right? eg strPath - "C:\user\mingali\desktop\new folder\"
Correct, where 'new folder' contains all your shortcut, .lnk, files.
Do not change any other codes.
You need to press F5 to execute the code before output appears in the immediate window.
thanks.
However, when i pressed F5. a error message pop up stating ''Compile Error User-defined type not defined" with "objWSH As WshShell'' being highlighted.
As I put in the code.
"requires reference to Windows Scripting Host object model"
To do this you need to use Tools > References in VBE and then browse for windows scripting host
Hi Andy,
I hope you are not running thin of your patient. I went to tools but reference cannot be selected. Any idea why?
Thanks
Perhaps you IT have set it so you can not access them.
You could try late binding code.
Please Login or Register to view this content.
Hi Andy,
It works perfectly....well, at least at home. i will try again at work tomorrow.
Thanks for your help ;-)
Hi Andy,
It did not work at workplace. When i click F5, nothing show up in the immediate window. I tried both version you provided. At my workplace, i can access to tools -> reference and i did tick 'windows scripting host object model'.
What else may have gone wrong?
Thanks
Add a line to the code to check it is running.
Does the word running appear in the immediate window?Please Login or Register to view this content.
If yes then check the path name you are using is correct.
if not then check macros are enabled and that you can run them
Hi Andy,
Thanks for your reply
Where shall i add this additional line?Add a line to the code to check it is running.
NoDoes the word running appear in the immediate window?
How to check marcos are enabled?if not then check macros are enabled and that you can run them
You can literally add it any where within the routine.
Try this, do you see the message boxes?
Please Login or Register to view this content.
Last edited by Andy Pope; 08-28-2009 at 05:28 AM.
Thank Andy.
I will try it on Monday and let you know.
Hi Andy,
[QUOTE=
Try this, do you see the message boxes?
[/QUOTE]
Yes, there are two message boxes. However, immediate window still remains blank.
What is your suggestion for next step?
Thanks
What did the second of the message boxes say?
If it says 0, zero, then your file/folder is incorrect.
If it has a value then it would suggest that the folder has not *.lnk files in it.
If the folder is correct and has files then it could be you do not have permission to access that area.
Hi Andy,
There are numbers in the second box.
If it has a value then it would suggest that the folder has not *.lnk files in it.
How can i rectify this issue?
the folders are in share drive. i have shortcut type in my desktop. So 'that area' mean which area?If the folder is correct and has files then it could be you do not have permission to access that area.
Thanks
This will report files attempting to process.
Do you see the message boxes?
Please Login or Register to view this content.
Thanks Andy.
I will try it tommorrow and let you know.
No. Nothing at all.This will report files attempting to process.
Do you see the message boxes?
Then there are no *.lnk files in the folder you specified in the code.
Assuming the folder is correct try changing the files to search for and see if anything is returned.
Please Login or Register to view this content.
Hi Andy,
I will try it tomorrow and see how it goes.
FYI, i did create another new folder in my desktop with different filenames and to see whether the macro (the one you provided yesterday) can be executed or not. Unfortunately, no message box also.
Today, i just zipped up those files one by one and it took me about one hour. I really hope your macro can run at my workplace PC so that i can have an easier life.
How come it works in my home PC but not office PC?
Thanks
If I knew I would tell you.How come it works in my home PC but not office PC?
You may have to get you IT guys involved to hunt down the reason for it not working on your work pc, as the code itself acutally works as you have demonstrated on your home pc.
Hi Andy,
It works now!!!! Yahoo!!!
I asked an IT guys to look into it. He did something on my PC and then your code works!!!! How happy I am now ;-)
Thank you very much. Your code save me about an hour time a month. Have seen how powerful marco is, now i am learning it. Hopefully, i can learn and understand it faster so that i can apply it to my job and save me more time for more relevant work.
Glad you got it working.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks