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

What is =ROMAN(40) in Excel?
The Roman numeral for 40 is XL. Bill "MrExcel" Jelen's 40th book was called MrExcel XL.

Cbrine

Well-known Member
Joined
Dec 2, 2003
Messages
3,196
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
 

Joe Was

MrExcel MVP
Joined
Feb 19, 2002
Messages
7,539
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
 

Watch MrExcel Video

Forum statistics

Threads
1,130,336
Messages
5,641,565
Members
417,222
Latest member
Broflovski

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
Top