Open Directory for user to choose a file

andymalan

Board Regular
Joined
Feb 22, 2017
Messages
128
Office Version
  1. 365
  2. 2007
Platform
  1. Windows
Hello, I hope i can get some help from people whom i regard as super smart.
The code below (that i have managed to get from others) finds the username and computer name so that other users would be able to use the same code on their computers.
I had hoped that the code would also open the filename "Element List (whatever the user had named the file).xlxs"
(The filename will ALWAYS begin with"Element List".) if that cannot be done, then if the directory could open for the user to choose the file manually.
the code below opens the directory to Documents, leaving the user to stumble along the path to find the file.

Your help will be greatly appreciated.
many thanks in advance
Andy

Dim wkb2 As Workbook
Dim UsersName As String, strFileName As String

UsersName = Environ("USERNAME")

strFileName = "C:\Users" & UsersName & _
"\Desktop\Excalibur Winner\ExcaliburProPlus\_ExcaliburDataConfig\Element list - Export.xlsx"

If Not Dir(strFileName, vbDirectory) = vbNullString Then

Set wkb2 = Workbooks.Open(strFileName, False, False)







Else
MsgBox strFileName & Chr(10) & Chr(10) & Space(Len(strFileName) / 2) & "File Not Found", 48, "Not Found"

End If


FilterIndx = IIf(Val(Application.Version) < 12, 1, 2)




FileFilter = "Excel 2003 (*.xls),*.xls," & _
"Excel 2007 > (*.xlsx),*.xlsx," & _
"All Excel Files (*.xl*),*.xl*," & _
"All Files (*.*),*.*"




GetFileName = Application.GetOpenFilename(FileFilter, FilterIndx, "Select One File To Open")
If VarType(GetFileName) = vbBoolean Then GetFileName = CVErr(10)
 

Excel Facts

Which lookup functions find a value equal or greater than the lookup value?
MATCH uses -1 to find larger value (lookup table must be sorted ZA). XLOOKUP uses 1 to find values greater and does not need to be sorted.
Is "Element List (whatever the user had named the file).xlxs" in the same folder as the workbook that has the macro code?

If yes...

Code:
[B]ChDir ThisWorkbook.Path[/B]

FileFilter = "Excel 2003 (*.xls),*.xls," & _
"Excel 2007 > (*.xlsx),*.xlsx," & _
"All Excel Files (*.xl*),*.xl*," & _
"All Files (*.*),*.*"

GetFileName = Application.GetOpenFilename(FileFilter, FilterIndx, "Select One File To Open")
[color=darkblue]If[/color] VarType(GetFileName) = vbBoolean [color=darkblue]Then[/color] GetFileName = [color=darkblue]CVErr[/color](10)
 
Upvote 0
AWESOME AlphaFrog, that is true.

just one thing though, the correct directory opens but "Excel 2003" is selected. how can i change the code to select"All Files" ?
 
Upvote 0
Code:
ChDir ThisWorkbook.Path

FileFilter = "Excel 2003 (*.xls),*.xls," & _
"Excel 2007 > (*.xlsx),*.xlsx," & _
"All Excel Files (*.xl*),*.xl*," & _
[COLOR="#FF0000"]"All Files (*.*),*.*"[/COLOR]

GetFileName = Application.GetOpenFilename(FileFilter, [COLOR="#FF0000"]4[/COLOR], "Select One File To Open")
If VarType(GetFileName) = vbBoolean Then GetFileName = CVErr(10)
 
Upvote 0
Magnifica! ok, so if you can sort out this next problem, then you truly will be my hero. :)

Notice line 5 of code
"\Desktop\Excalibur Winner\ExcaliburProPlus\_ExcaliburDataConfig\Element list - Export.xlsx"

when the user selects the file he/she wants e.g. Element List Nicholas, the file that actually opens is Element list - Export.xlsx
what can be done so that the file chosen by the user opens? the filename will always start with "Element List"
all my thanks in advance
PegLeg

e
 
Upvote 0
Code:
    [color=darkblue]Dim[/color] wkb2 [color=darkblue]As[/color] Workbook
    [color=darkblue]Dim[/color] strFileName [color=darkblue]As[/color] [color=darkblue]String[/color]
    [color=darkblue]Dim[/color] FileFilter [color=darkblue]As[/color] [color=darkblue]String[/color]
    
    ChDir ThisWorkbook.Path
    
    FileFilter = "Excel 2003 (*.xls),*.xls," & _
                 "Excel 2007 > (*.xlsx),*.xlsx," & _
                 "All Excel Files (*.xl*),*.xl*," & _
                 "All Files (*.*),*.*"
    
    strFileName = Application.GetOpenFilename(FileFilter, 4, "Select One File To Open")
    
    [color=darkblue]If[/color] strFileName <> "False" [color=darkblue]Then[/color]
        [color=darkblue]Set[/color] wkb2 = Workbooks.Open(strFileName, [color=darkblue]False[/color], False)
    [color=darkblue]Else[/color]
        [color=darkblue]Exit[/color] [color=darkblue]Sub[/color] [color=green]' User canceled[/color]
    [color=darkblue]End[/color] [color=darkblue]If[/color]
 
Upvote 0
Dear AlphaFrog
you are my hero, because, as i knew you would, you have brought the answer again.

so even if you don't help me with one final hurdle ( I hope its the final hurdle) you will still be my hero.

At a certain point in my code, I have to refer to the newly opened worksheet, as follows;

Windows("ConfigureData.xlsm").Activate


Sheets("Cabinets").Select
Range("I1").Select
ActiveSheet.Paste
Application.CutCopyMode = False

it is at this point I need to activate the newly opened workbook, but because the file name will be "Element List ???????"
i need your expertise to help me with the code to achieve this.

Your most ardent fan

AndyPegLeg

 
Upvote 0
Dear AlphaFrog, the code does indeed allow the user to select his file from the directory.

when I ran the code, I had selected a few breakpoints within the code. if I try to run the code without breakpoints present, the selected file opens, but the file that has the macro closes without a warning. If I try to run the application, the same thing happens i.e. the selected file opens but the application closes.

what can be done?

your greatest fan
PegLeg
 
Upvote 0

At a certain point in my code, I have to refer to the newly opened worksheet, as follows;

Windows("ConfigureData.xlsm").Activate


Sheets("Cabinets").Select
Range("I1").Select
ActiveSheet.Paste
Application.CutCopyMode = False

it is at this point I need to activate the newly opened workbook, but because the file name will be "Element List ???????"

Code:
Set wkb2 = Workbooks.Open(strFileName, False, False)

When the workbook is opened, the variable wkb2 is set to it. Just reference wkb2. It is the opened workbook regardless of the file name.

Code:
wkb2.Sheets("Cabinets").Range("I1").Paste
Application.CutCopyMode = False


Dear AlphaFrog, the code does indeed allow the user to select his file from the directory.

when I ran the code, I had selected a few breakpoints within the code. if I try to run the code without breakpoints present, the selected file opens, but the file that has the macro closes without a warning. If I try to run the application, the same thing happens i.e. the selected file opens but the application closes.

Is there more code in the macro than I gave you? I have no idea what you are doing other than the bit of code I gave. All that does is prompt the user to select a file and open it.

Excel is the application. Do you mean Excel closes?
 
Upvote 0
Dear AlphaFrog
bad choice of words on my part.
My macro is set in a work book named "ConfigureData.xlsm", and the objective is to get data from a work book named "Element List (whatever the user has named the workbook)e.g. Element List Jenny *******.xlsm and copy the data to "Sheet 1" in "ConfigureData.xlsm". there, the data is manipulated using macros and formulas and then saved to a workbook named "ImportFromExcel.xlsm".

Users gather data pertinent to their customers requirements, and save the data in the work book Element List *******, the work book will ALWAYS be named
Element List followed by a customer name etc. e.g.
Element List AlphaFrog *******.xlsm
Element List PegLeg Study.xlsm

The macro in "ConfigureData.xlsm" needs to access the work book "Element List ****.xlsm" to get to the data needed. Below is the code that brings me to the point where the user can select the file.

When the macro is run in VB with breakpoints set in various points, you can select the file from the directory.
If it is run in VB without break points, as you select the file "Element List ****.xlsm" from the directory, "ConfigureData.xlsm" closes immediately, leaving "Element List ****.xlsm" open.

'Getdata from Element list - Export.xlsx Dim wkb2 As Workbook
Dim UsersName As String, strFileName As String

UsersName = Environ("USERNAME")

strFileName = "C:\Users" & UsersName & _
"\Desktop\Excalibur Winner\ExcaliburProPlus\_ExcaliburDataConfig\Element list - Export.xlsx"

ChDir ThisWorkbook.Path

FileFilter = "Excel 2003 (*.xls),*.xls," & _
"Excel 2007 > (*.xlsx),*.xlsx," & _
"All Excel Files (*.xl*),*.xl*," & _
"All Files (*.*),*.*"

strFileName = Application.GetOpenFilename(FileFilter, 4, "Select One File To Open")

If strFileName <> "False" Then
Set wkb2 = Workbooks.Open(strFileName, False, False)
Else
Exit Sub ' User canceled
End If



On Error Resume Next

'copy the customer name and address from the "Element List WorkSheet"
Range("A9:A15").Select
Selection.Copy

Windows("ConfigureData.xlsm").Activate


Sheets("Cabinets").Select
Range("I3").Select
ActiveSheet.Paste
Application.CutCopyMode = False

Range("I1").Select
ActiveCell.FormulaR1C1 = "The Last Client that was configured was:"

Windows("ConfigureData.xlsm").Deactivate

'Activate workbook with unknown filename
This is where I am stuck.

much appreciation, and many thanks.
PegLeg.
 
Upvote 0

Forum statistics

Threads
1,213,501
Messages
6,114,010
Members
448,543
Latest member
MartinLarkin

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