Excel 2010 VBA save, open location, select file

Question

I have made a VBA macro to save a file as xlsx and pdf, at a set location with a filename taken from a cell in the sheet, after which the save location opens.

Code as follows:

Sub SaveAs()

Dim FName           As String
Dim FPath           As String

FPath = "PATHNAME HERE"
FName = Sheets("SHEETNAME").Range("E1").Text
ActiveWorkbook.SaveAs Filename:=FPath & "\" & FName, FileFormat:=51

ActiveWorkbook.ExportAsFixedFormat Type:=xlTypePDF, Filename:=FPath & "\" & FName, Quality:=xlQualityStandard

Shell "explorer.exe" & " " & "PATHNAME HERE", vbNormalFocus    

End Sub

However the folder i am saving those files becomes quite filled, and for easy access i want to open the path in explorer with the just saved pdf selected (as in, I want to open folder with the file selected, not like opening the pdf). Any suggestions how to accomplish this?

You say you are successfully saving the files, and the issue is how to open explorer with the recently saved filepath in the address bar? — peege
– peege, Commented Jan 16, 2015 at 11:14
Basically all I have now works fine, but i would like to add functionality to have the latest PDF selected (the one created by the same macro) when opening the folder in explorer. — user1761646
– user1761646, Commented Jan 16, 2015 at 11:56
I would suggest you create a String variable called FileNPath where FileNPath = FPath & "\" & FName & ".pdf" Then include that string from your call to explorer. The solution provided below by @katz accomplishes this, when using the full path. — peege
– peege, Commented Jan 16, 2015 at 12:01
I have tried this ( where it becomes Shell "explorer.exe" & " " & FileNPath, vbNormalFocus ) but this actually opens the pdf file. I would like to just have it selected in the opened folder. — user1761646
– user1761646, Commented Jan 16, 2015 at 12:06

Kᴀτᴢ · Accepted Answer · 2015-01-16 12:14:02Z

2

Try this:

Use

Shell "explorer.exe /select," & FPath & "\" & FName, vbMaximizedFocus

instead of

Shell "explorer.exe" & " " & "PATHNAME HERE", vbNormalFocus

edited Jan 16, 2015 at 12:14

answered Jan 16, 2015 at 11:53

Kᴀτᴢ

2,1966 gold badges33 silver badges60 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

user1761646 Over a year ago

I could do that, but that won't add the functionality I would like, namely selecting the just created PDF file. Also it breaks the functionality (variable not defined).

user1761646 Over a year ago

Thanks, got it working with some minor adjustments: PathNName = FPath & "\" & FName & ".pdf" Shell "explorer.exe /select," & PathNName, vbMaximizedFocus

Collectives™ on Stack Overflow

Excel 2010 VBA save, open location, select file

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related