Excel 2010 VBA save, open location, select file

2
votes

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?

1
excelexcel-2010vba
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
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
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
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
added info, sorry for confusing you – user1761646

1 Answers

2
votes

Try this:

Use 

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

instead of 

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