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" <(E-Mail Removed)> escreveu na mensagem news:exIZnv%(E-Mail Removed)...
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" <(E-Mail Removed)> wrote in message
news:exIZnv%(E-Mail Removed)...
| 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))
|
|
|

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


"Steph" <(E-Mail Removed)> wrote in message
news:exIZnv%(E-Mail Removed)...
> 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" <(E-Mail Removed)> wrote in message
news:uymtyY$(E-Mail Removed)...
> 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" <(E-Mail Removed)> wrote in message
> news:exIZnv%(E-Mail Removed)...
> | 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" <(E-Mail Removed)> wrote in message
> news:uymtyY$(E-Mail Removed)...
> > 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" <(E-Mail Removed)> wrote in message
> > news:exIZnv%(E-Mail Removed)...
> > | 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

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" <(E-Mail Removed)> wrote in message
news:exIZnv%(E-Mail Removed)...
> 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))
>
>
>