ChDir - Specify Folder

S

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
 
R

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
 
T

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
 
S

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
 
S

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...
 
D

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.
 
S

StephanieH

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

Thanks!
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads


Top