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

SamSpadePD

New Member
Joined
Nov 29, 2014
Messages
6
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?
 

Excel Facts

Quick Sum
Select a range of cells. The total appears in bottom right of Excel screen. Right-click total to add Max, Min, Count, Average.
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.
Code:
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
 
Upvote 0
You can get the folder by looking in the Shell.Windows collection and write to the clipboard using MSForms.DataObject.
 
Upvote 0
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.

Code:
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
 
Upvote 0
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.

Code:
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
 
Upvote 0
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.

Code:
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
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,224,559
Messages
6,179,513
Members
452,921
Latest member
BBQKING

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top