Page 1 of 2 12 LastLast
Results 1 to 10 of 13

Thread: Open Directory for user to choose a file

  1. #1
    New Member
    Join Date
    Feb 2017
    Posts
    10
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Open Directory for user to choose a file

    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)

  2. #2
    MrExcel MVP AlphaFrog's Avatar
    Join Date
    Sep 2009
    Posts
    16,087
    Post Thanks / Like
    Mentioned
    16 Post(s)
    Tagged
    7 Thread(s)

    Default Re: Open Directory for user to choose a file

    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:
    ChDir ThisWorkbook.Path
    
    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)
    Paste your Excel data to the forum...
    MrExcel HTML Maker or Excel Jeanie

    How to post your vba code
    [CODE]your VBA code here[/CODE]
    The # button in the forum's editor will apply CODE tags around your selected text.

  3. #3
    New Member
    Join Date
    Feb 2017
    Posts
    10
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Open Directory for user to choose a file

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

  4. #4
    MrExcel MVP AlphaFrog's Avatar
    Join Date
    Sep 2009
    Posts
    16,087
    Post Thanks / Like
    Mentioned
    16 Post(s)
    Tagged
    7 Thread(s)

    Default Re: Open Directory for user to choose a file

    Code:
    ChDir ThisWorkbook.Path
    
    FileFilter = "Excel 2003 (*.xls),*.xls," & _
    "Excel 2007 > (*.xlsx),*.xlsx," & _
    "All Excel Files (*.xl*),*.xl*," & _
    "All Files (*.*),*.*"
    
    GetFileName = Application.GetOpenFilename(FileFilter, 4, "Select One File To Open")
    If VarType(GetFileName) = vbBoolean Then GetFileName = CVErr(10)
    Paste your Excel data to the forum...
    MrExcel HTML Maker or Excel Jeanie

    How to post your vba code
    [CODE]your VBA code here[/CODE]
    The # button in the forum's editor will apply CODE tags around your selected text.

  5. #5
    New Member
    Join Date
    Feb 2017
    Posts
    10
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Open Directory for user to choose a file

    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

  6. #6
    MrExcel MVP AlphaFrog's Avatar
    Join Date
    Sep 2009
    Posts
    16,087
    Post Thanks / Like
    Mentioned
    16 Post(s)
    Tagged
    7 Thread(s)

    Default Re: Open Directory for user to choose a file

    Code:
        Dim wkb2 As Workbook
        Dim strFileName As String
        Dim FileFilter As String
        
        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
    Paste your Excel data to the forum...
    MrExcel HTML Maker or Excel Jeanie

    How to post your vba code
    [CODE]your VBA code here[/CODE]
    The # button in the forum's editor will apply CODE tags around your selected text.

  7. #7
    New Member
    Join Date
    Feb 2017
    Posts
    10
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Open Directory for user to choose a file

    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


  8. #8
    New Member
    Join Date
    Feb 2017
    Posts
    10
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Open Directory for user to choose a file

    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

  9. #9
    MrExcel MVP AlphaFrog's Avatar
    Join Date
    Sep 2009
    Posts
    16,087
    Post Thanks / Like
    Mentioned
    16 Post(s)
    Tagged
    7 Thread(s)

    Default Re: Open Directory for user to choose a file

    Quote Originally Posted by AndyPegLeg View Post

    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

    Quote Originally Posted by AndyPegLeg View Post
    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?
    Paste your Excel data to the forum...
    MrExcel HTML Maker or Excel Jeanie

    How to post your vba code
    [CODE]your VBA code here[/CODE]
    The # button in the forum's editor will apply CODE tags around your selected text.

  10. #10
    New Member
    Join Date
    Feb 2017
    Posts
    10
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Open Directory for user to choose a file

    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.

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •