use vba to put address from open win explorer instance into the clipboard

[SamSpadePD]

I am working on Windows 7 and Office 2013.

I have an open instance of windows explorer that has been navigated to a folder. From excel vba I would like to load that win explorer address into the clipboard. I do not have a clue how to do this. Can anyone assist me?

 

[Kenneth Hobson]

The only way is by SendKeys() or other similar key methods which are all problematic.

It is best to use the tools that Excel provides.

Sub test_GetFolder()
  MsgBox Get_Folder(ThisWorkbook.path, "Folder Picker")
End Sub
 
Function Get_Folder(Optional FolderPath As String, _
  Optional HeaderMsg As String) As String
    With Application.FileDialog(msoFileDialogFolderPicker)
        If FolderPath = "" Then
          .initialFilename = Application.DefaultFilePath
          Else
          .initialFilename = FolderPath
        End If
        .Title = HeaderMsg
        If .show = -1 Then
            Get_Folder = .SelectedItems(1)
        Else
            Get_Folder = ""
        End If
    End With
End Function

 

[John_w]

You can get the folder by looking in the Shell.Windows collection and write to the clipboard using MSForms.DataObject.

 

[tonyyy]

Not exactly what you're looking for - this approach launches the Open File dialogue box, then the user needs to navigate to and select a file. The path/address is then copied into the clipboard.

Sub AddPathToClipboard()

'To work with Windows Clipboard you need a DataObject, the object in MSForms Library. It provides support for textstrings.
'For that you need to add the reference “Microsoft Forms 2.0 Object Library”
'How to add Reference Libaries:  http://excel-macro.tutorialhorizon.com/vba-excel-reference-libraries-in-excel-workbook/

    Dim Finfo As String
    Dim FilterIndex As Integer
    Dim Title As String
    Dim FileName As String
    Dim objData As New MSForms.DataObject
    
    Finfo = "Text Files (*.txt),*.txt," & _
            "Lotus Files (*.prn),*.prn," & _
            "Comma Separated Files (*.csv),*.csv," & _
            "ASCII Files (*.asc),*.asc," & _
            "Excel Files (*.xl*),*.xl*," & _
            "All Files (*.*),*.*"
    FilterIndex = 5
    Title = "Select a File to Open"

    FileName = Application.GetOpenFilename(Finfo, FilterIndex, Title)
    If FileName = "" Then
        MsgBox "No file was selected."
    Else
        objData.SetText FileName
        objData.PutInClipboard
        MsgBox "The following Address will be put into the Clipboard: " & vbCrLf & FileName
    End If
End Sub

Cheers,

tonyyy

 

[Kenneth Hobson]

Using John's idea, you can do it like this below in a Module. Run "Ken" and then "Ken2". This method will put each Windows Explorer's Instance for the folder value into the object Clipboard. The last is the most current.

I could show you an API method to deal with the clipboard but it is more involved than MSForms.

Option Explicit

Sub ken()
  Dim sf As Object, saw As Object
  
  Set saw = CreateObject("Shell.Application").Windows
  
  If saw.Count = 0 Then
    'Clipboard.Clear
    Clipboard.SetText ""
    Exit Sub
  End If
  
  For Each sf In saw
    'Debug.Print sf.locationname, sf.locationurl
    'Debug.Print sf.Document.Folder.Self.Path
    Clipboard.SetText sf.Document.Folder.Self.Path
    Clipboard.PutInClipboard
  Next sf
End Sub

Sub ken2()
  MsgBox Clipboard.GetText()
End Sub

 

[SamSpadePD]

Thanks for your response!! As given it would not execute until I added after the Option Explicit statement the following statement

Dim Clipboard As New MSForms.DataObject

Your solution hit the nail right on the head.

SamSpadePD

Using John's idea, you can do it like this below in a Module. Run "Ken" and then "Ken2". This method will put each Windows Explorer's Instance for the folder value into the object Clipboard. The last is the most current.

I could show you an API method to deal with the clipboard but it is more involved than MSForms.

Option Explicit

Sub ken()
  Dim sf As Object, saw As Object
  
  Set saw = CreateObject("Shell.Application").Windows
  
  If saw.Count = 0 Then
    'Clipboard.Clear
    Clipboard.SetText ""
    Exit Sub
  End If
  
  For Each sf In saw
    'Debug.Print sf.locationname, sf.locationurl
    'Debug.Print sf.Document.Folder.Self.Path
    Clipboard.SetText sf.Document.Folder.Self.Path
    Clipboard.PutInClipboard
  Next sf
End Sub

Sub ken2()
  MsgBox Clipboard.GetText()
End Sub

 

[Kenneth Hobson]

It was one way to keep the object in memory. I must have deleted it when I deleted some other code before pasting. You can put it into a Sub or pass the value to another routine as needed. Or, I could show you the more involved API method for clipboard operations. There are several ways, just depends on what you goals are.