ChDir - Specify Folder

[StephanieH]

I've tried two variations trying to specify the folder for GetOpenFilename.
The first defaults to the directory last used, the second gives me an error
message of "Invalid procedure call or argument". I can't use ChDrive "I"
because the letter is different for each of my users. Any suggestions?

Sub OpenPlacements()

ChDir "\\Fl-aejf-fs1\Data\Data\RECOVERY\EXLDATA\Loan Recovery MIS\Placements"

Filename = Application.GetOpenFilename("Excel Files (*.xls),*.xls)", , _
"Please choose a Placement file to open", True)

End Sub

Sub Openplacements2()

Dim FileToOpen As Variant
Dim MyPath As String
Dim Wb As Workbook

MyPath = "\\Fl-aejf-fs1\Data\Data\EXLDATA\Loan Recovery MIS\Placements"
ChDrive "\\Fl-aejf-fs1\Data\Data"
ChDir MyPath

FileToOpen = Application.GetOpenFilename("Excel Files (*.xls), *.xls", , _
"Please choose a Placement file to open", , True)

If FileToOpen <> False Then
Set Wb = Workbooks.Open(FileToOpen)
Else
Exit Sub
End If

End Sub

 

[Ron de Bruin]

Hi StephanieH

Try this
Copy the code in a normal module
*****************************

'Previously posted by Rob Bovey:

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

' sample usage

Sub FindFile()
Dim FName As Variant

ChDirNet "\\DELL\testing"
FName = Application.GetOpenFilename
End Sub

 

[Tom Ogilvy]

Previously posted by Rob Bovey:
' Placed at the top of a general module outside any procedures:
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

' sample usage

Sub FindFile()
ChDirNet "\\LOGD0FILES\OGILVTW\Docs\Temp"
fName = Application.GetOpenFileName
End Sub

 

[StephanieH]

I took Ron's entry and adjusted to inlude our file path. It highlights
"SetCurrentDirectoryA" and gives me an error reading "Sub or Function not
defined"..
Here's what I have
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 FindFile()
Dim FName As Variant

ChDirNet "\\Fl-aejf-fs1\Data\Data\RECOVERY\EXLDATA\Loan Recovery
MIS\Placements"
FName = Application.GetOpenFilename
End Su

 

[StephanieH]

Sorry, I forgot to include the Private Declare Function SetCurrentDirectoryA
Lib _
"\\Fl-aejf-fs1\Data\Data\RECOVERY\EXLDATA\Loan Recovery MIS\Placements"
(ByVal lpPathName As String) As Long

When this is included, I get a message that says "File not found:
\\Fl-aejf-fs1\Data\Data\RECOVERY\EXLDATA\Loan Recovery MIS\Placements

as if this is the file, not the begining of the path name...

 

[Dave Peterson]

This line:

Private Declare Function SetCurrentDirectoryA Lib _
"kernel32" (ByVal lpPathName As String) As Long

shouldn't be changed and has to be kept at the top of your module. It's the API
Function that makes it work. Just leave it as-is.

 

[StephanieH]

Works perfectly. Sorry, I thought the Kernel32 was was where I put my
pathname..

Thanks!