VBA to pop up WINDOW to choose the file...

[pedie]

Hi, I need help with VBA to pop up WINDOW to choose the file...Suppose my files are in C:\GroupsFiles\ i want the default window to how this folder...but still allow to navigate to other folders too if user wants...


Thanks for helping!

 

[diddi]

this lists all files whose type are selected in combobox1, in listbox1

Sub GetFolder()
    ListBox1.Clear
    With Application.FileDialog(msoFileDialogFolderPicker)
        .Title = "Select Boxplot Location Directory"
        .ButtonName = "Open"
        .Show
        SelFolder = .SelectedItems.Item(1) & "\"
    End With
    
    sFiles = AllFiles(SelFolder)

    For row = 0 To UBound(sFiles)
        If Right(sFiles(row), 4) = Right(ComboBox1.List(ComboBox1.ListIndex, 1), 4) Then
            ListBox1.AddItem sFiles(row)
        End If
    Next row
    Select Case ListBox1.ListCount
        Case 0
            Label1.Caption = "Didnt find any files - choose another folder"
            CommandButton2.Visible = False
        Case Is > 1
            Label1.Caption = "Found these " & ListBox1.ListCount & " files..."
            CommandButton2.Visible = True
        Case 1
            Label1.Caption = "Found just this 1 file..."
            CommandButton2.Visible = True
    End Select
End Sub

Private Function AllFiles(ByVal FullPath As String) As String()
    Dim oFs As New FileSystemObject
    Dim sAns() As String
    Dim oFolder As Folder
    Dim oFile As File
    Dim lElement As Long
    
    Label1.Caption = "Searching for files..."
    DoEvents
    Application.Cursor = xlWait
    ReDim sAns(0) As String
    If oFs.FolderExists(FullPath) Then
        Set oFolder = oFs.GetFolder(FullPath)
     
        For Each oFile In oFolder.Files
          lElement = IIf(sAns(0) = "", 0, lElement + 1)
          ReDim Preserve sAns(lElement) As String
          sAns(lElement) = oFile.Name
        Next
    End If

    AllFiles = sAns
    Application.Cursor = xlDefault
    
ErrHandler:
    Set oFs = Nothing
    Set oFolder = Nothing
    Set oFile = Nothing
End Function

 

[pedie]

Hi Diddi, thanks for helping. Sorry i think my explaination was not correct. I want a Dialog bOx to popup so that i can select the workbook to open and then [run my code...] then close back the file...

Thanks again

 

[LydiaF]

Hi, I need help with VBA to pop up WINDOW to choose the file...Suppose my files are in C:\GroupsFiles\ i want the default window to how this folder...but still allow to navigate to other folders too if user wants...


Thanks for helping!

Try researching the application.getopenfilename method.

 

[diddi]

oh, i thought you want to list files. you can still modify this and pinch the bits you need.

    With Application.FileDialog(msoFileDialogFolderPicker)
        .Title = "Select your excel file"
        .ButtonName = "Open"
        .Show
        SelFolder = .SelectedItems.Item(1) & "\"
    End With

opens the file dialog

 

[pedie]

Thanks that was it. If you please have some more time to spare then..
it error when i cancel it...

 

[diddi]

    Dim oFs As New FileSystemObject
    Dim oFolder As Folder
    Dim oFile As File
    
    'Label1.Caption = "Searching for files..."
    DoEvents
    If oFs.FolderExists(FullPath) Then
        Set oFolder = oFs.GetFolder(FullPath)
    else
        msgbox "No file selected"
    end if

   ... etc

 

[pedie]

Thanks again Diddi!!!

 

[rjwebgraphix]

Here's an alternate solution that I use in many different things...

complete with Comments

Sub FileOpen()
 
On Error GoTo Err_Import
 
Dim FileToOpen As Variant
Dim Answer As Variant
Dim ImportFile As String
Dim FileOpened As String

' If you want a hard coded Starting folder, then just change this to...
' ChDir "C:\PathtoFile"
If Sheets("IMPORTER").Cells(19, "C") = "" Then
    ChDir "C:\"
Else
    ChDir Sheets("IMPORTER").Cells(19, "C")
End If
 
'Used for switching back and forth between the Current workbook
'and the workbook that was opened.
ImportFile = ThisWorkbook.Name
Start_Beg:

' If it's not a text file, but excel file, comment out the .txt and uncomment the .xls
'FileToOpen = Application.GetOpenFilename("Excel Files (*.xls), *.xls")
FileToOpen = Application.GetOpenFilename("Text Files (*.txt), *.txt")
If FileToOpen = False Then
    GoTo Exit_Import
End If

' ************* OPEN FILE ************
' Replace this section with one you record yourself.
' Just run the macro recorder, then open the file as you want it to open.
' For my purposes, I'm importing a delimited text file, so this is set up for that.
    Workbooks.OpenText Filename:=FileToOpen, _
        Origin:=437, StartRow:=1, DataType:=xlDelimited, TextQualifier:= _
        xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=False, Semicolon:=False _
        , Comma:=False, Space:=False, Other:=True, OtherChar:="|", FieldInfo _
        :=Array(1, 1), TrailingMinusNumbers:=True
' Replace above with your own
' ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
 
FileOpened = ActiveWorkbook.Name

Finish:
' *************************
' Add Code here to do what you need with the open file
'**************************
    
Exit_Import:
Exit Sub
Err_Import:
If Err.Number = 91 Then Resume Next
Msgbox Err.Number & " " & Err.Description
Resume Exit_Import
    
End Sub

 

[pedie]

RJweb. thanks for sharing....