ExcelBanter

ExcelBanter (https://www.excelbanter.com/)
-   Excel Programming (https://www.excelbanter.com/excel-programming/)
-   -   Defining Current Directory (https://www.excelbanter.com/excel-programming/390160-defining-current-directory.html)

ll
Defining Current Directory
 
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

Ben McBen
Defining Current Directory
 
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

ll
Defining Current Directory
 
On May 25, 11:15 am, Ben McBen
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

Tom Ogilvy
Defining Current Directory
 
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
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

ll
Defining Current Directory
 
On May 25, 2:20 pm, Tom Ogilvy
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
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


All times are GMT +1. The time now is 11:58 AM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com