On May 25, 2:20 pm, Tom Ogilvy <TomOgi...@discussions.microsoft.com>
wrote:
> Private Declare Function SetCurrentDirectoryA Lib _
> "kernel32" (ByVal lpPathName As String) As Long
>
> Sub ChDirNet(szPath As String)
> Dim lReturn As Long
> lReturn = SetCurrentDirectoryA(szPath)
> Debug.Print lReturn
> If lReturn = 0 Then Err.Raise vbObjectError + 1, "Error setting path."
> End Sub
>
> sample usage:
>
> Sub FindFile()
> ChDirNet "\\LOGD0FILES\OGILVTW\Docs\Temp"
> Application.Dialogs(xlDialogFindFile).Show
> End Sub
>
> --
> Regards,
> Tom Ogilvy
>
> "ll" wrote:
> > On May 25, 11:15 am, Ben McBen <BenMc...@discussions.microsoft.com>
> > wrote:
> > > Is it not just a case of using ActiveWorkbook.Path ?
>
> > > "ll" wrote:
> > > > Hi,
> > > > I'm working with a form that has an 'open' box and a 'save as' box.
> > > > How can I specify that the 'current directory' is equal to the
> > > > directory in which the spreadsheet with the form resides?
> > > > Here is what I have so far: Thanks - Louis
> > > > ----------------------
> > > > Public Sub listfilesinfoldersandsub1()
>
> > > > Dim i As Long
> > > > Dim Path As String
> > > > Dim Prompt As String
> > > > Dim Title As String
> > > > Dim TempArr() As String
> > > > Dim PathName As String
> > > > Dim NewFN As String
>
> > > > With Application.FileSearch
>
> > > > Dim SaveDriveDir As String
> > > > SaveDriveDir = CurDir
>
> > > > ChDir (SaveDriveDir)
> > > > NewFN = Application.GetOpenFilename(FileFilter:="Excel Files
> > > > (*.xls), *.xls", Title:="Please select a file")
>
> > > > If NewFN = False Then
>
> > > > MsgBox "Stopping because you did not select a file"
> > > > Exit Sub
> > > > Else
>
> > > > .FileType = msoFileTypeAllFiles
> > > > .SearchSubFolders = False
> > > > .Execute
>
> > > > PathName = NewFN
>
> > > > UserForm1.TextBox1.Value = PathName
>
> > > > End If
>
> > > > End With
>
> > > > End Sub
>
> > thanks
> > Well, that works ok with local files, but when I try to put the file
> > on the network and run it from there, "My Documents" becomes my
> > directory, for some reason. The msgbox displays the correct path, but
> > the file directory that opens is still "My Documents."
> > Here's what I've got now:
> > ------------------
> > Dim i As Long
> > Dim Path As String
> > Dim Prompt As String
> > Dim Title As String
> > Dim TempArr() As String
> > Dim PathName As String
> > Dim NewFN As String
>
> > With Application.FileSearch
>
> > Dim SaveDriveDir As String
> > SaveDriveDir = CurDir
>
> > SaveDriveDir = ActiveWorkbook.Path
>
> > ChDir SaveDriveDir
> > MsgBox SaveDriveDir
> > NewFN = Application.GetOpenFilename(FileFilter:="Excel Files
> > (*.xls), *.xls", Title:="Please select a file")
>
> > If NewFN = False Then
>
> > MsgBox "Stopping because you did not select a file"
> > Exit Sub
> > Else
>
> > .FileType = msoFileTypeAllFiles
> > .SearchSubFolders = False
> > .Execute
>
> > PathName = NewFN
>
> > UserForm1.TextBox1.Value = PathName
>
> > End If
> > ChDrive SaveDriveDir
> > ChDir SaveDriveDir
>
> > End With
>
> > End Sub
Thanks Tom
That did the trick.
Regards,
Louis
|
Similar Threads
