PLEASE HELP ME!!!

Amichelecotton

New Member
Joined
Jan 21, 2005
Messages
1
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!
 

Excel Facts

When they said...
When they said you are going to "Excel at life", they meant you "will be doing Excel your whole life".
Code:
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
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,217,756
Messages
6,138,425
Members
450,136
Latest member
Tabako1960

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top