Open Directory for user to choose a file

[andymalan]

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)

 

[AlphaFrog]

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...

[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)

 

[andymalan]

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" ?

 

[AlphaFrog]

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)

 

[andymalan]

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

 

[AlphaFrog]

    [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]

 

[andymalan]

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

 

[andymalan]

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

 

[AlphaFrog]

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 ???????"

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.

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?

 

[andymalan]

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.