Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 05-29-2019, 02:26 PM
DaveP DaveP is offline Open Folder/File by Keyword Windows 10 Open Folder/File by Keyword Office 2010
Novice
Open Folder/File by Keyword
 
Join Date: May 2019
Posts: 11
DaveP is on a distinguished road
Default Open Folder/File by Keyword

Hello,



I'm trying to write a macro that will open a document from a separate folder (I know how to do that) but that is specific to the current document and current folder.

For example, we are working on a case using NewReport, which is inside of MainFolder, and we need to open OldReport that is in SubFolder (of the main folder, which contains NewReport). The cases and folders have an extremely consistent file naming system; they are always named "253762954 OldReport 5/29/2018", or "253762954 NewReport 5/29/2019", with the numbers and dates changing for each specific case.

Is there a way to open a file that contains a keyword? ie., could VBA understand "go to the file path of this active document, go to this sub folder, and open the file in the subfolder that contains, "OldReport", in the file name"?

I just kinda threw together 2 lines that do similar things but not the right thing. I put 'Foldernames' in place of the actual directory and folder names, lol.

Private Sub CheckBox1_Click()
'OldReport
Shell Environ("windir") & "\Explorer.exe " & ActiveDocument.path, vbNormalFocus
Shell "Explorer.exe \\'Foldernames'\OldReport", vbNormalFocus
Me.Hide
End Sub
Reply With Quote
  #2  
Old 05-29-2019, 03:00 PM
DaveP DaveP is offline Open Folder/File by Keyword Windows 10 Open Folder/File by Keyword Office 2010
Novice
Open Folder/File by Keyword
 
Join Date: May 2019
Posts: 11
DaveP is on a distinguished road
Default

Also, I just discovered that a piece of code I wrote, that opens the active document's containing folder, doesn't work if the file is saved to the network, as opposed to saved to your PC. Here's the code to open the file path of this active document:


Sub FolderTest()
Shell Environ("windir") & "\Explorer.exe " & ActiveDocument.path, vbNormalFocus
End Sub
Reply With Quote
  #3  
Old 05-29-2019, 05:23 PM
eduzs eduzs is offline Open Folder/File by Keyword Windows 10 Open Folder/File by Keyword Office 2010 32bit
Expert
 
Join Date: May 2017
Posts: 260
eduzs is on a distinguished road
Default

Those names are invalid file/folder names:
"253762954 OldReport 5/29/2018", or "253762954 NewReport 5/29/2019"
Reply With Quote
  #4  
Old 05-30-2019, 01:04 PM
DaveP DaveP is offline Open Folder/File by Keyword Windows 10 Open Folder/File by Keyword Office 2010
Novice
Open Folder/File by Keyword
 
Join Date: May 2019
Posts: 11
DaveP is on a distinguished road
Default

Lol, dashes not slashes. 23452345 OldReport 5-29-19
Reply With Quote
  #5  
Old 05-30-2019, 04:12 PM
Guessed's Avatar
Guessed Guessed is offline Open Folder/File by Keyword Windows 10 Open Folder/File by Keyword Office 2016
Expert
 
Join Date: Mar 2010
Location: Canberra/Melbourne Australia
Posts: 3,932
Guessed has a brilliant futureGuessed has a brilliant futureGuessed has a brilliant futureGuessed has a brilliant futureGuessed has a brilliant futureGuessed has a brilliant futureGuessed has a brilliant futureGuessed has a brilliant futureGuessed has a brilliant futureGuessed has a brilliant futureGuessed has a brilliant future
Default

Dave, I initially thought your code wouldn't work on paths which include spaces and this might be why it didn't work on the network path you were using.

If this is the case, you would need to enclose the pathname in double quotes
Shell Environ("windir") & "\Explorer.exe """ & ActiveDocument.Path & """", vbNormalFocus

However, on my machine both your code and my variation worked on both local and network paths. Perhaps you have an older version of Windows and it might be worth exploring on your machine.

BUT, If you want to actually open a second file with exactly the same name as the current document but in a different folder path then you could use a string replace function to get the fullname along the lines of the following aircode

Dim sNewFile as string, sOldFile as string
sNewFile = ActiveDocument.Fullname
sOldFile = Replace(sNewFile,"NewReport","OldReport")
Documents.Open sOldFile
__________________
Andrew Lockton
Chrysalis Design, Melbourne Australia
Reply With Quote
  #6  
Old 06-04-2019, 10:27 AM
DaveP DaveP is offline Open Folder/File by Keyword Windows 10 Open Folder/File by Keyword Office 2010
Novice
Open Folder/File by Keyword
 
Join Date: May 2019
Posts: 11
DaveP is on a distinguished road
Default

Your code worked flawlessly for me!! Thanks!!

If I may ask, why do the spaces matter? In my research on VBA, I didn't see (or maybe didn't notice..) anything about the spacing mattering like that. Clearly it does matter, ha.
Reply With Quote
  #7  
Old 06-04-2019, 04:06 PM
Guessed's Avatar
Guessed Guessed is offline Open Folder/File by Keyword Windows 10 Open Folder/File by Keyword Office 2016
Expert
 
Join Date: Mar 2010
Location: Canberra/Melbourne Australia
Posts: 3,932
Guessed has a brilliant futureGuessed has a brilliant futureGuessed has a brilliant futureGuessed has a brilliant futureGuessed has a brilliant futureGuessed has a brilliant futureGuessed has a brilliant futureGuessed has a brilliant futureGuessed has a brilliant futureGuessed has a brilliant futureGuessed has a brilliant future
Default

Dave

Spaces often matter in VBA. In the case of the Shell Environ line, we are essentially running the equivalent of a dos command via a command prompt. In that context a space would separate a new parameter rather than a part of a single parameter.
__________________
Andrew Lockton
Chrysalis Design, Melbourne Australia
Reply With Quote
Reply

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
When i open excel one always the same excel file open(empty XLstart folder) marcin4991 Excel 2 08-29-2016 02:36 AM
Open Folder/File by Keyword How to open Documents folder directly from CTRL+O of Open folder on QAT scvjudy Word 2 08-11-2014 10:58 PM
Open Folder/File by Keyword Formula to open external file with specific program (like open with) pemartins Excel 16 02-24-2014 11:39 PM
Open Folder/File by Keyword excel cannot open the file .xlsx because the file format or file extension is not val teddysika Excel 1 11-22-2012 06:06 AM
Open Folder/File by Keyword Setting file open folder options KevinJ Word 1 10-15-2011 05:51 AM

Other Forums: Access Forums

All times are GMT -7. The time now is 09:24 AM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2024, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2024 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft