I have a XLSM in a folder, and part of a macro is to create a new folder, then SaveAs in to the new folder, then delete the original folder (with the original XLSM in it).
I'd like to put an IF statement in the macro where it looks to see if the original folder is currently open. If it is, close it before the SaveAs, and if not then do nothing.
The only thing I can find using Google pertains to Internet Explorer windows, nothing about folder windows.
Please help?
Why would it be open?
Doesnt that mean you are trying to OPEN a file?
Are you talking about a windows explorer folder is open or a folder is open in the Excel open file prompt thing?
Please ensure you mark your thread as Solved once it is. Click here to see how.
If a post helps, please don't forget to add to our reputation by clicking the star icon in the bottom left-hand corner of a post.
A user would browse through their computer (or network), and locate the folder where the spreadsheet is located. Is that "Windows Explorer"? Then they open the spreadsheet and start using it. In their taskbar, the folder is probably still open. I say probably because some folks in the office close those types of windows the moment they're done with them.
After entering data in to the spreadsheet, the user clicks a macro which does SaveAs in a different folder location, deletes the original folder.
Because the original folder is deleted, there is no reason to have a windows explorer window open displaying an irrelevant folder, so I would like to A) see if the folder is open (somehow), and B) if it is, close it.
Does that make more sense?
I understand what you WANT to do I just dont follow WHY you want to do it. I wouldnt want to automate or handle EVERY possible snonsensical thing a user could do OUTSIDE of excel using VBA IN excel, that just sounds like a futile path...
Simple; convenience. Isn't that the whole point of macros in the first place; to automate lengthy, repetitive, or other processes/ procedures?
I'm not really here to debate the aspect of "why", I'm here for the aspect of "how"...
Yeah, absolutely but my point is you might be trying to squeeeeze lemon out of a brick. You are asking for help on how you can control windows explorer with VBA, then how you can validate if a particular folder is open IN windows explorer, which is outside the typical Excel objects, properties and methods. That gets exponentially more complicated but have a look through google, it is outside my little VBA experience.
http://vbadud.blogspot.com/2009/01/h...using-vba.html
http://stackoverflow.com/questions/1...-select-a-file
Seems to be the most closely related to what I could find quickly:
http://stackoverflow.com/questions/1...er-is-open-vba
Good Luck.
Thanks for the links. I've been googling like crazy as well, but hadn't come across that last link for some reason; odd. At any rate, the last code on that link will work for what I need it to do. Here is a copy of the code. I just need to change the "OpenFold" and "strFolder" to reference cell values, have it call a function, and I should be all set.
Thanks for the assistance!
Please Login or Register to view this content.
Last edited by Rerock; 09-10-2014 at 01:01 PM.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks