PLEASE HELP ME!!!

[Amichelecotton]

My boss has asked me to create a dialogue box (or something) that would change the directory where the information that we are importing from our unix computer is coming from everytime we run the marco. I don't have any experience with macros whatsoever and the little i do know, he (my boss) has taught me. Any information that you would be able to provide would be greatly appreciated!

 

[Cbrine]

Private Sub CommandButton1_Click()
Dim Test As String, FileName As String, CancelPressed As String

CancelPressed = Application.FileDialog(msoFileDialogFolderPicker).Show
Test = CurDir
FileName = "Test.xls"
If CancelPressed = -1 Then
    Workbooks.Open (Test & "\" & FileName)
End If
End Sub

Here's some code to help you out. Just set the FileName to your filename and it will open that Filename in the selected folder.

HTH
Cal

 

[Joe Was]

Option Explicit
Dim FileTitle As String

Sub BrowseDir()
'Run from standard module, like: Module1.
'Display Folder Shell.
Dim objFolder As Object, strFullPath As String, strFileName As String
Dim ws As Worksheet, wb As Workbook
Dim x As Double

'Selects the Current Folder!
Set objFolder = CreateObject("Shell.Application"). _
BrowseForFolder(0, "Please Select Folder", 0, Left(CurDir, 3))
'Folder?
If Not objFolder Is Nothing Then
'Root Dir?
If Len(objFolder.Items.Item.Path) > 3 Then
strFullPath = objFolder.Items.Item.Path & Application.PathSeparator
Else
strFullPath = objFolder.Items.Item.Path
End If
End If
'Hold Folder!
FileTitle = strFullPath

'Run your code here to set the default folder to the one selected!
'"FileTitle" will hold the newly selected path!
'Something like: ChDir FileTitle
'Or what ever your code needs.
'You can also run a new Sub from this location!

Reset:

Application.DisplayAlerts = True
Application.ScreenUpdating = True
Exit Sub

ErrH:

MsgBox Err.Number & vbCr & _
Err.Description & vbCr _
, vbMsgBoxHelpButton _
, "Error Accessing: " & strFullPath & strFileName _
, Err.HelpFile _
, Err.HelpContext

End Sub