Default File path

[Steph]

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))

 

[Gustavo Strabeli]

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" <[email protected]> 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))

 

[Jim Rech]

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
| 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))
|
|
|

 

[Corey]

http://www.vba-programmer.com/Snippets/Code_Excel/Saving_Workbook_no_Dialogs.html

 

[Steph]

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!

 

[Dave Peterson]

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

 

[NickHK]

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