Hi everyone. Below is a piece of code that allows the user to browse to a
location on the network and select a file to open. Is there a way to
default to a specific path? For example, default to C:\Documents and
Settings\All Users. Thanks!


Sub GetDataFile()
Dim v As Variant, i As Long, bk As Workbook

Application.ScreenUpdating = False
Application.DisplayAlerts = False

v = Application.GetOpenFilename(MultiSelect:=False)
If Not IsArray(v) Then Exit Sub
For i = LBound(v) To UBound(v)
Set bk = Workbooks.Open(v(i))

Hi, Steph!
Not sure if I got you and if you indeed need a VBA. However, you can set it as follows.

On main menu select: Tools Options General
Then you set the standard files' source on the bottom part.

Hope that helps.

Gustavo.


"Steph" escreveu na mensagem ...
Hi everyone. Below is a piece of code that allows the user to browse to a
location on the network and select a file to open. Is there a way to
default to a specific path? For example, default to C:\Documents and
Settings\All Users. Thanks!


Sub GetDataFile()
Dim v As Variant, i As Long, bk As Workbook

Application.ScreenUpdating = False
Application.DisplayAlerts = False

v = Application.GetOpenFilename(MultiSelect:=False)
If Not IsArray(v) Then Exit Sub
For i = LBound(v) To UBound(v)
Set bk = Workbooks.Open(v(i))

Since GetOpenFilename uses the current path that is what you have to change:

ChDrive "c:"
ChDir "c:\a"

Btw, your code is set to check for a returned array but you'll never get it
unless you change multiselect to True.


--
Jim
"Steph" wrote in message
...
| Hi everyone. Below is a piece of code that allows the user to browse to a
| location on the network and select a file to open. Is there a way to
| default to a specific path? For example, default to C:\Documents and
| Settings\All Users. Thanks!
|
|
| Sub GetDataFile()
| Dim v As Variant, i As Long, bk As Workbook
|
| Application.ScreenUpdating = False
| Application.DisplayAlerts = False
|
| v = Application.GetOpenFilename(MultiSelect:=False)
| If Not IsArray(v) Then Exit Sub
| For i = LBound(v) To UBound(v)
| Set bk = Workbooks.Open(v(i))
|
|
|

Thanks Jim. One wrinkle - I realize our drives are not mapped to letters.
So for instance, the actual path looks something like this:
\\Hqfile01\acctmgt\Account_Review\CURRENT_MONTH

So the ChDrive command errors unless I specify a drive letter. I can map
the drive to a letter, but then I would have to make sure everyone else who
runs the code also has the same mapping. any way around this?

PS - nice catch on the multiselect!


"Jim Rech" wrote in message
...
Since GetOpenFilename uses the current path that is what you have to
change:

ChDrive "c:"
ChDir "c:\a"

Btw, your code is set to check for a returned array but you'll never get
it
unless you change multiselect to True.


--
Jim
"Steph" wrote in message
...
| Hi everyone. Below is a piece of code that allows the user to browse to
a
| location on the network and select a file to open. Is there a way to
| default to a specific path? For example, default to C:\Documents and
| Settings\All Users. Thanks!
|
|
| Sub GetDataFile()
| Dim v As Variant, i As Long, bk As Workbook
|
| Application.ScreenUpdating = False
| Application.DisplayAlerts = False
|
| v = Application.GetOpenFilename(MultiSelect:=False)
| If Not IsArray(v) Then Exit Sub
| For i = LBound(v) To UBound(v)
| Set bk = Workbooks.Open(v(i))
|
|
|

You can use a windows api function that changes the drive. In fact, this works
with mapped drives, too.

Here's a sample, but with getsaveasfilename.

Option Explicit
Private Declare Function SetCurrentDirectoryA Lib _
"kernel32" (ByVal lpPathName As String) As Long
Sub ChDirNet(szPath As String)
Dim lReturn As Long
lReturn = SetCurrentDirectoryA(szPath)
If lReturn = 0 Then Err.Raise vbObjectError + 1, "Error setting path."
End Sub
Sub testme01()

Dim myFileName As Variant
Dim myCurFolder As String
Dim myNewFolder As String

myCurFolder = CurDir
myNewFolder = "\\share\folder1\folder2"

On Error Resume Next
ChDirNet myNewFolder
If Err.Number < 0 Then
'what should happen
MsgBox "Please change to your own folder"
Err.Clear
End If
On Error GoTo 0

myFileName = Application.GetSaveAsFilename(filefilter:="Excel Files, *.xls")

ChDirNet myCurFolder

If myFileName = False Then
Exit Sub 'user hit cancel
End If

'do your stuff...
End Sub


Steph wrote:

Thanks Jim. One wrinkle - I realize our drives are not mapped to letters.
So for instance, the actual path looks something like this:
\\Hqfile01\acctmgt\Account_Review\CURRENT_MONTH

So the ChDrive command errors unless I specify a drive letter. I can map
the drive to a letter, but then I would have to make sure everyone else who
runs the code also has the same mapping. any way around this?

PS - nice catch on the multiselect!

"Jim Rech" wrote in message
...
Since GetOpenFilename uses the current path that is what you have to
change:

ChDrive "c:"
ChDir "c:\a"

Btw, your code is set to check for a returned array but you'll never get
it
unless you change multiselect to True.


--
Jim
"Steph" wrote in message
...
| Hi everyone. Below is a piece of code that allows the user to browse to
a
| location on the network and select a file to open. Is there a way to
| default to a specific path? For example, default to C:\Documents and
| Settings\All Users. Thanks!
|
|
| Sub GetDataFile()
| Dim v As Variant, i As Long, bk As Workbook
|
| Application.ScreenUpdating = False
| Application.DisplayAlerts = False
|
| v = Application.GetOpenFilename(MultiSelect:=False)
| If Not IsArray(v) Then Exit Sub
| For i = LBound(v) To UBound(v)
| Set bk = Workbooks.Open(v(i))
|
|
|



--

Dave Peterson

http://www.vba-programmer.com/Snippe...o_Dialogs.html


"Steph" wrote in message
...
Hi everyone. Below is a piece of code that allows the user to browse to a
location on the network and select a file to open. Is there a way to
default to a specific path? For example, default to C:\Documents and
Settings\All Users. Thanks!


Sub GetDataFile()
Dim v As Variant, i As Long, bk As Workbook

Application.ScreenUpdating = False
Application.DisplayAlerts = False

v = Application.GetOpenFilename(MultiSelect:=False)
If Not IsArray(v) Then Exit Sub
For i = LBound(v) To UBound(v)
Set bk = Workbooks.Open(v(i))

Steph,
You can use ChDir before your call to GetOpenFilename.

Also, you have MultiSelect=false, so a string will be return and your code
will always exit as you will never get an array.
Looks like you want to set MultiSelect=True instead.
You should also check for a return value False, in case the user cancelled.

NickHK

"Steph" wrote in message
...
Hi everyone. Below is a piece of code that allows the user to browse to a
location on the network and select a file to open. Is there a way to
default to a specific path? For example, default to C:\Documents and
Settings\All Users. Thanks!


Sub GetDataFile()
Dim v As Variant, i As Long, bk As Workbook

Application.ScreenUpdating = False
Application.DisplayAlerts = False

v = Application.GetOpenFilename(MultiSelect:=False)
If Not IsArray(v) Then Exit Sub
For i = LBound(v) To UBound(v)
Set bk = Workbooks.Open(v(i))