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

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

--
Regards,
Tom Ogilvy

"StephanieH" wrote in message
...
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

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" wrote:

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

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 wrote:

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" wrote:

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



--

Dave Peterson

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

Thanks!



"Dave Peterson" wrote:

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 wrote:

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" wrote:

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



--

Dave Peterson