Open a workbook from within a specified folder

[Peter Rooney]

Good morning, all,

My workbook contains a cell called "Foldername" which currently contains the
following value:

I have a macro,

Sub WorkbookOpen()
On Error Resume Next
ChDir (Sheets("Database").Range("FolderName").Value)
WorkbookToOpen = Application.GetOpenFilename()
Workbooks.Open Filename:=WorkbookToOpen
End Sub

When the FileOpen dialog is displayed, I want the default directory to be
whatever is contained in Foldername. ChDir Doesn't seem to work. Van anyone
advise me how to get around this?

Regards & thanks in advance

Pete

 

[Ron de Bruin]

Hi Peter

This is working for me

Sub test()
Dim FName As Variant
Dim wb As Workbook
Dim MyPath As String
Dim SaveDriveDir As String

SaveDriveDir = CurDir

MyPath = Sheets("Database").Range("FolderName").Value

ChDrive MyPath
ChDir MyPath

FName = Application.GetOpenFilename(filefilter:="Excel Files (*.xls), *.xls")
If FName <> False Then
Set wb = Workbooks.Open(FName)
MsgBox "your code"
wb.Close
End If

ChDrive SaveDriveDir
ChDir SaveDriveDir

End Sub

 

[Peter Rooney]

Ron,

"ChDrive Mypath" drops out - could this be because "FolderName" begins with
\\ and not a drive letter? I have to do it this way, as the system is used by
multiple users, not all of whom map to the drive using the same letter.

Cheers

Pete

 

[Ron de Bruin]

Yes

That's your problem

See Example 6
http://www.rondebruin.nl/copy3.htm#select

I use a function there
If you need help post back

 

[Peter Rooney]

Ron,

Splendid!

Thanks very much.

Pete

Yes

That's your problem

See Example 6
http://www.rondebruin.nl/copy3.htm#select

I use a function there
If you need help post back