VBA to go to specified folder and open it

[dellzy]

Dear Excel Experts,

I found a great sets of macro that can split multiple tabs in one workbook into separate workbooks and save them in a folder and pop-up msgbox mentioning that they are saved in a specified path.
Upon clicking "OK" on the Msgbox, we can either make a macro line to point focus to either Activeworkbook or ThisWorkbooth or ActiveSheet.

However, what I want to achieve is, upon clicking "OK", user will be redirect to the specified directory. But I don't know what sort of command lines to allow this in VBA? I found something like "CurrentFolder" properties but so far the search results show how to move emails to specific folder. I just need to set focus. That's all.

Hopefully anyone can help me on this.

Thank you so much in advance.

DZ

 

[Maximus Tatius]

I don't know if this helps you but in one of my workbooks where I create a new folder my code displays a hyperlink to the new folder after the code has run. I then just click the hyperlink to open it in Windows Explorer, which works great for me...

' display hyperlink to the new folder
    Sheet1.Range("C11").Value = "=HYPERLINK(""" & ToPath & """,""" & ToPath & """)"

 

[dellzy]

Hi, Maximus Tatius,

I don't know if this helps you but in one of my workbooks where I create a new folder my code displays a hyperlink to the new folder after the code has run. I then just click the hyperlink to open it in Windows Explorer, which works great for me...

' display hyperlink to the new folder
    Sheet1.Range("C11").Value = "=HYPERLINK(""" & ToPath & """,""" & ToPath & """)"

Thank you for your swift response. I admit this is a workaround but if possible would like the cursor to go to the specified folder after clicking "OK".

I wish to do this is to minimize action from end-user as much as possible. Because after splitting them into separate workbooks, the original workbook would auto-close without saving and end user to work out directly those in the folder.

Thank you in advance.

 

[Maximus Tatius]

Do you mean that you are copying sheets to a new workbook and then you just want to close your master workbook without saving, and leave the new workbook open, or just open the folder you've saved the workbooks to in Windows Explorer?

For the first scenario I generally close my master workbook with the following, which leaves any underlying workbook open...

Private Sub Workbook_BeforeClose(Cancel As Boolean)

ThisWorkbook.Close savechanges:=False

End Sub

If you want to open the folder location itself then see if this is what you're looking for...

ms access - How to open a folder in Windows Explorer from VBA? - Stack Overflow

 

[dellzy]

Hi,
I think this one was way out from what I wish to do. Your first reply was near to my objective but I don't want it to stay in the excel wbook and show the hyperlink and need to wait for the user to click the Hyperlink to redirect them to the folder. I want to skip the manual click. I just want it to automatically open the folder after MsgBox pops-up "Your files are saved in this C:\Temp folder". Any ideas?

Do you mean that you are copying sheets to a new workbook and then you just want to close your master workbook without saving, and leave the new workbook open, or just open the folder you've saved the workbooks to in Windows Explorer?

For the first scenario I generally close my master workbook with the following, which leaves any underlying workbook open...

Private Sub Workbook_BeforeClose(Cancel As Boolean)

ThisWorkbook.Close savechanges:=False

End Sub

If you want to open the folder location itself then see if this is what you're looking for...

ms access - How to open a folder in Windows Explorer from VBA? - Stack Overflow

 

[dellzy]

Hello,


Please ignore my questions. I managed to find the code in the internet. It's as below:-

Example macro to open My Documents folder. Note you must have a "\" as end of path.
Sub OpenFolder()
Dim MyFolder As String
MyFolder = "C:\My Documents\"
ActiveWorkbook.FollowHyperlink MyFolder
End Sub

Found the above code at http://answers.microsoft.com/en-us/...e-folder/c72e8e66-f544-e011-90b6-1cc1de79d2e2

 

[Maximus Tatius]

Great! Glad to hear you worked it out. That's actually a very useful bit of code - I'm going to use that myself, thanks!