Open to specific Directory

[daniels012]

What code can I enter to open A DIALOG WINDOW to a specific folder?

I need to open a SaveAs Dialog window to a specific directory(folder).

Is this possible, not knowing any of the beginning part, but only knowing the folder name itself? Mine is called: LeadFolder

Application.Dialog etc. etc. c:\something\something\something\LeadFolder

Please any help on this is greatly appreciated!


Michael D

I have this on thread on Mr. Excel as well!
http://www.mrexcel.com/forum/showthr...05#post3034405

[Bob Phillips]

Use

[vba]
ChDrive path
ChDir path[/vba]

[daniels012]

OK Great!
What if I don't know the path. That is the part I am having issues with.
The Path is where I need help. The "LeadFolder" file will be different on 50 different computers.

I need to wildcard something like this:
Application.Dialog c:\*\LeadFolder


Thank You,
Michael D

[Bob Phillips]

Then you can't possibly preset the path for the SaveAs if you don't know it!

You need to get the user to select it using GetSaveAsFilenam method.

[daniels012]

Ah! Ok

Can I do a find somehow? Something like find c:\*\LeadFolder then SaveAs this directory?

Michael

[Bob Phillips]

Yes, I gave you the way in the previous post.

[daniels012]

Ok
Tried this:
ChDir "c:\*\LeadFolder"

It did not work

[Bob Phillips]

Of course not, * is not a directory. As I said,if you don't know the path you cannot switch to it.

[Kenneth Hobs]

I guess that you could make a guess for them. If you know that it would be a 2nd level subfolder, iterating through all the subfolders will give you something to guess with. Using that concept, you must first collect all of the subfolder names. I would do it using DOS.

First, put this into a Module called mExecCmd or whatever you like:
[VBA]Option Explicit

'http://support.microsoft.com/kb/q129796/
Private Type STARTUPINFO
cb As Long
lpReserved As String
lpDesktop As String
lpTitle As String
dwX As Long
dwY As Long
dwXSize As Long
dwYSize As Long
dwXCountChars As Long
dwYCountChars As Long
dwFillAttribute As Long
dwFlags As Long
wShowWindow As Integer
cbReserved2 As Integer
lpReserved2 As Long
hStdInput As Long
hStdOutput As Long
hStdError As Long
End Type

Private Type PROCESS_INFORMATION
hProcess As Long
hThread As Long
dwProcessId As Long
dwThreadId As Long
End Type

'Enum enSW
' SW_Hide = 0
' SW_NORMAL = 1
' SW_MAXIMIZE = 3
' SW_MINIMIZE = 6
'End Enum
'Enum enPriority_Class
' NORMAL_PRIORITY_CLASS = &H20
' IDLE_PRIORITY_CLASS = &H40
' HIGH_PRIORITY_CLASS = &H80
'End Enum

Private Declare Function WaitForSingleObject Lib "kernel32" (ByVal _
hHandle As Long, ByVal dwMilliseconds As Long) As Long

Private Declare Function CreateProcessA Lib "kernel32" (ByVal _
lpApplicationName As String, ByVal lpCommandLine As String, ByVal _
lpProcessAttributes As Long, ByVal lpThreadAttributes As Long, _
ByVal bInheritHandles As Long, ByVal dwCreationFlags As Long, _
ByVal lpEnvironment As Long, ByVal lpCurrentDirectory As String, _
lpStartupInfo As STARTUPINFO, lpProcessInformation As _
PROCESS_INFORMATION) As Long

Private Declare Function CloseHandle Lib "kernel32" _
(ByVal hObject As Long) As Long

Private Declare Function GetExitCodeProcess Lib "kernel32" _
(ByVal hProcess As Long, lpExitCode As Long) As Long

Private Const INFINITE = -1&
Private Const STARTF_USESHOWWINDOW = &H1


Public Function ExecCmd(cmdLine$, Optional windowstyle As Integer = 0, _
Optional priorityclass As Integer = &H20)
Dim proc As PROCESS_INFORMATION
Dim start As STARTUPINFO
Dim ret As Long

' Initialize the STARTUPINFO structure:
start.cb = Len(start)
start.dwFlags = STARTF_USESHOWWINDOW
start.wShowWindow = windowstyle

' Start the shelled application:
ret& = CreateProcessA(vbNullString, cmdLine$, 0&, 0&, 0&, _
priorityclass, 0&, vbNullString, start, proc)

' Wait for the shelled application to finish:
ret& = WaitForSingleObject(proc.hProcess, INFINITE)
Call GetExitCodeProcess(proc.hProcess, ret&)
Call CloseHandle(proc.hThread)
Call CloseHandle(proc.hProcess)
ExecCmd = ret&
End Function
[/VBA]

In another module, use this to get the subfolder names and test.

[VBA]Sub Test1()
Dim a As Variant
Range("A1").EntireColumn.ClearContents
a = SubFolderList("x:")
If UBound(a) = -1 Then Exit Sub
Range("A1").Resize(UBound(a) + 1).value = WorksheetFunction.Transpose(a)
End Sub

'Requires Module mExecCmd
Function SubFolderList(Folder As String) As Variant
Dim tFile As String
Dim diff As Long
Dim hFile As Integer, Str As String, vArray As Variant, e As Variant
Dim I As Integer, FolderPart As String
Dim iHandle As Integer


'Delete temp file if it exists and create path
tFile = Environ$("temp") & "\FileList.txt"
'If Dir$(tFile) <> "" Then Kill tFile
'Write a 0 byte file
iHandle = FreeFile
Open tFile For Output Access Write As #iHandle
Close #iHandle

'Put files into tFile
'http://support.microsoft.com/kb/q129796/
' NORMAL_PRIORITY_CLASS = &H20, IDLE_PRIORITY_CLASS = &H40, HIGH_PRIORITY_CLASS = &H80
ExecCmd Environ$("comspec") & " /c Dir " & Folder & Application.PathSeparator & _
"*.* /ad/s/b > " & tFile, 0

'Show tFile in Notepad
'Shell "Notepad " & tFile

'Put tFile contents into an array
hFile = FreeFile
Open tFile For Binary Access Read As #hFile
Str = Input(LOF(hFile), hFile)
Close hFile
vArray = Split(Str, vbCrLf)

ReDim Preserve vArray(0 To UBound(vArray) - 1)
SubFolderList = vArray
End Function


[/VBA]

[Paul_Hossler]

You could search the tree for the folder you want

This returns the full path, or blank if not found

[VBA]
Option Explicit

Sub drv()
MsgBox SearchFolders("D:\data\phossler", "FindThisFolder")
MsgBox SearchFolders("D:\data\phossler", "NoHereFindThisFolder")
End Sub

'ref http://www.cpearson.com/Excel/QuickTree.aspx
' The code requires a reference to the Scripting library. In VBA, go to the Tools menu,
'choose References, and scroll down to Microsoft Scripting Runtime Library and check that item.

Function SearchFolders(StartHere As String, FindThis As String) As String
Dim FSO As Scripting.FileSystemObject
Dim StartFolder As Scripting.Folder
Dim SubFolder As Scripting.Folder
Dim sPath As String
Dim iDepthCounter As Long

SearchFolders = vbNullString

Set FSO = New Scripting.FileSystemObject

Set StartFolder = FSO.GetFolder(StartHere)

iDepthCounter = 1
For Each SubFolder In StartFolder.SubFolders

' Debug.Print iDepthCounter & " -- " & StartFolder & Application.PathSeparator & SubFolder.Name

If UCase(SubFolder.Name) = UCase(FindThis) Then
SearchFolders = StartFolder & Application.PathSeparator & SubFolder.Name
Exit Function

Else
iDepthCounter = iDepthCounter + 1
sPath = SearchFolders(StartFolder & Application.PathSeparator & SubFolder.Name, FindThis)
If Len(sPath) > 0 Then
SearchFolders = sPath
Exit Function
End If
End If
Next
End Function
[/VBA]

Paul