Hi,

I have a collection of documents for overview in an excel sheet and I
would like to be able to click a button to trigger Windows Explorer to
open and for the selected file to be highlighted. I have found a way
to do this for earlier Windows versions but it will not fly with my 64
bit Win7. The articles I find on Microsoft does not seem to help me
much. Any ideas?

Thanks!

thomas häger wrote:

I have a collection of documents for overview in an excel sheet and I
would like to be able to click a button to trigger Windows Explorer to
open and for the selected file to be highlighted. I have found a way
to do this for earlier Windows versions but it will not fly with my 64
bit Win7. The articles I find on Microsoft does not seem to help me
much. Any ideas?

Let's see the code you already have.

--
Usenet is a philosophy that only dogs can hear.

After serious thinking thomas häger wrote :
Hi,

I have a collection of documents for overview in an excel sheet and I
would like to be able to click a button to trigger Windows Explorer to
open and for the selected file to be highlighted. I have found a way
to do this for earlier Windows versions but it will not fly with my 64
bit Win7. The articles I find on Microsoft does not seem to help me
much. Any ideas?

Thanks!

Are you saying the x64 version of Excel VBA (VBA7) does not have a
GetOpenFilename function?

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

Are you saying the x64 version of Excel VBA (VBA7) does not have a
GetOpenFilename function?

Actually, that's an Application function that's normally exposed to VBA
as follows...

sFilename = Application.GetOpenFilename

...or in the case of specifying a filename...

sFilename = Application.GetSaveAsFilename

...where you can specify the path and filename.

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

On 10 Jan, 16:05, GS wrote:
Are you saying the x64 version of Excel VBA (VBA7) does not have a
GetOpenFilename function?

Actually, that's an Application function that's normally exposed to VBA
as follows...

* sFilename = Application.GetOpenFilename

..or in the case of specifying a filename...

* sFilename = Application.GetSaveAsFilename

..where you can specify the path and filename.

--
Garry

Free usenet access athttp://www.eternal-september.org
Classic VB Users Regroup!
* comp.lang.basic.visual.misc
* microsoft.public.vb.general.discussion

Thanks for the support!

My code so far is:

Sub OpenExplorer()
'Open the file with the name specified in the active cell
Dim strThisFile As String

strThisFile = ActiveCell.Value
Dim MyPathFile As String
MyPathFile = Range("F1").Value & strThisFile '"c:\Users\thomas.hager
\Documents\DocLibrary\"

-----
This defines the path and file to set focus to. What I want to do next
is to open an explorer window with the related file highlighted
(selected). I have not really been able to get there base don your
input above.

I read the following in a Microsoft article;

Examples
•To open a Windows Explorer view to explore only objects on \\<server
name, use the following syntax:
explorer /e,/root,\\<server name
•To view the C:\WINDOWS folder and select CALC.EXE, use the following
syntax:
explorer /select,c:\windows\calc.exe

But it seems not to work with Windows 7. What is explained in the
second bullet is exactly what I like to do.

Any ideas how to do this in Win7 environment - or am I doing something
wrong not getting it going?

Thanks!

You need to use a file explorer dialog, NOT open the Windows Explorer
app. Look up the functions I suggested in online help to see how they
will do exactly what you want.

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

An alternative to using the GetOpenFilename/GetSaveAsFilename functions
is the *Application.FileDialog(<msoFileDialogType)* function wherein
you can specify an *InitialFilename* and *FileFilters*. This is a bit
more complex approach but gives you much finer control over what the
others give you. This is my preference in all cases beyond simple
basics. Given your attempt to use the WE app (and related complexity),
this might be more to your liking.

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

Hi Thomas,

Am Mon, 14 Jan 2013 06:43:28 -0800 (PST) schrieb thomas häger:

Sub OpenExplorer()
'Open the file with the name specified in the active cell
Dim strThisFile As String

strThisFile = ActiveCell.Value
Dim MyPathFile As String
MyPathFile = Range("F1").Value & strThisFile '"c:\Users\thomas.hager
\Documents\DocLibrary\"

"C:\Users\thomas.hager" you can also express as:
Environ("USERPROFILE")
If your file exists and you want to open it, try:

Application.Dialogs(xlDialogOpen).Show _
Environ("USERPROFILE") & "\" & strThisFile

if you want to save as, try:
Application.Dialogs(xlDialogSaveAs).Show _
Environ("USERPROFILE") & "\" & strThisFile


Regards
Claus Busch
--
Win XP PRof SP2 / Vista Ultimate SP2
Office 2003 SP2 /2007 Ultimate SP2

On 14 Jan, 17:33, Claus Busch wrote:
Hi Thomas,

Am Mon, 14 Jan 2013 06:43:28 -0800 (PST) schrieb thomas häger:

Sub OpenExplorer()
'Open the file with the name specified in the active cell
Dim strThisFile As String

strThisFile = ActiveCell.Value
Dim MyPathFile As String
MyPathFile = Range("F1").Value & strThisFile '"c:\Users\thomas.hager
\Documents\DocLibrary\"

"C:\Users\thomas.hager" you can also express as:
Environ("USERPROFILE")
If your file exists and you want to open it, try:

Application.Dialogs(xlDialogOpen).Show _
* *Environ("USERPROFILE") & "\" & strThisFile

if you want to save as, try:
Application.Dialogs(xlDialogSaveAs).Show _
* *Environ("USERPROFILE") & "\" & strThisFile

Regards
Claus Busch
--
Win XP PRof SP2 / Vista Ultimate SP2
Office 2003 SP2 /2007 Ultimate SP2


Thanks for your support!
I must be doing something wrong here.

My code so far is:
----
Sub OpenExplorer()
'Open the file with the name specified in the active cell
Dim strThisFile As String

strThisFile = ActiveCell.Value
Dim MyPathFile As String
MyPathFile = Range("F1").Value & strThisFile '"c:\Users\thomas.hager
\Documents\DocLibrary\"

'Application.Dialogs(xlDialogOpen).Show MyPathFile

sFilename = Application.GetOpenFilename

-----
As you can see I tried both suggested versions (commenting one out at
the time). Both alternatives opens up the explorer window but when I
try to actually open a file from this window it does not work. The
explorer window closes but the file does not open. Any ideas here?

hi Thomas,

another way to do it,


Sub OpenExplorer()
Dim fd As FileDialog
Dim oFile As String
Dim wb1 As Workbook, wb2 As Workbook
Dim sPathName As String

sPathName = "c:\..." 'adapt

ChDir sPathName

Set fd = Application.FileDialog(msoFileDialogFilePicker)

With fd
.AllowMultiSelect = False
.Show
On Error Resume Next
If Err.Number < 0 Then Err.Clear: Exit Sub
oFile = .SelectedItems(1)
End With


Set wb1 = ActiveWorkbook
Set wb2 = Workbooks.Open(oFile)

Windows(wb2.Name).Visible = True

'action

wb2.Close SaveChanges:=True

Set wb1 = Nothing
Set wb2 = Nothing

End Sub



isabelle

Le 2013-01-10 04:50, thomas häger a écrit :
Hi,

I have a collection of documents for overview in an excel sheet and I
would like to be able to click a button to trigger Windows Explorer to
open and for the selected file to be highlighted. I have found a way
to do this for earlier Windows versions but it will not fly with my 64
bit Win7. The articles I find on Microsoft does not seem to help me
much. Any ideas?

Thanks!

thomas häger wrote on 16/01/2013 :
I must be doing something wrong here.

My code so far is:
----
Sub OpenExplorer()
'Open the file with the name specified in the active cell
Dim strThisFile As String

strThisFile = ActiveCell.Value
Dim MyPathFile As String
MyPathFile = Range("F1").Value & strThisFile '"c:\Users\thomas.hager
\Documents\DocLibrary\"

'Application.Dialogs(xlDialogOpen).Show MyPathFile

sFilename = Application.GetOpenFilename

-----
As you can see I tried both suggested versions (commenting one out at
the time). Both alternatives opens up the explorer window but when I
try to actually open a file from this window it does not work. The
explorer window closes but the file does not open. Any ideas here?

Both methods return a filename (including its path), and so you need to
use this as follows...

Workbooks.Open sFilename

OR

Dim WkbSource As Workbook
Set WkbSource = Workbooks.Open(sFilename)
'//do stuff
Set WkbSource = Nothing `//release its memory

**See Isabelle`s solution for an example...

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion