| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Change current directory to the directory that the workbook loads from! | Excel Programming | |||
| current directory | Excel Discussion (Misc queries) | |||
| Current Directory | Excel Discussion (Misc queries) | |||
| get current directory | Excel Programming | |||
| changing current directory to that of the current open file | Excel Programming | |||
Hybrid Mode
