Dialogue box to select folder >files for import?

[firebladejr]

Hi,

The below code imports seperate csv's from "C:\Temp\OTC_Files\", then populates the workbook with each csv becoming a worksheet. Is it possible to amend the code to present the user with a dialogue box where they can browse to the folder containing the files, then select the necessary files? I've tried a few different methods but seem to be making a mess of the code rather than progress... any help would be great.

Private Sub CommandButton1_Click()

    'All files must be  *.csv format
      
    Dim strPath As String
    Dim strFile As String
     '
    strPath = "C:\Temp\OTC_Files\"
    strFile = Dir(strPath & "*.csv")
    Do While strFile <> ""
        With ActiveWorkbook.Worksheets.Add
            With .QueryTables.Add(Connection:="TEXT;" & strPath & strFile, _
                Destination:=.Range("A1"))
                .FieldNames = True
                .RowNumbers = False
                .FillAdjacentFormulas = False
                .PreserveFormatting = True
                .RefreshOnFileOpen = False
                .RefreshStyle = xlInsertDeleteCells
                .SavePassword = False
                .SaveData = True
                .AdjustColumnWidth = True
                .RefreshPeriod = 0
                .TextFilePromptOnRefresh = False
                .TextFilePlatform = 850
                .TextFileStartRow = 1
                .TextFileParseType = xlDelimited
                .TextFileTextQualifier = xlTextQualifierDoubleQuote
                .TextFileConsecutiveDelimiter = True
                .TextFileTabDelimiter = False
                .TextFileSemicolonDelimiter = False
                .TextFileCommaDelimiter = True
                .TextFileSpaceDelimiter = False
                .TextFileOtherDelimiter = "="
                .TextFileColumnDataTypes = Array(9, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1)
                .TextFileTrailingMinusNumbers = True
                .Refresh BackgroundQuery:=False
            End With
        End With
        strFile = Dir
     Loop
End Sub

[dominicb]

Good morning firebladejr

Is it possible to amend the code to present the user with a dialogue box where they can browse to the folder containing the files, then select the necessary files?

You need to have a look at the GetOpenFilename function in VBA help :

Sub test()
f = Application.GetOpenFilename("CSV Files (*.csv), *.csv")
MsgBox "You have selected : " & f
End Sub

The code above will ask you to choose a file, and dump the path / filename into the variable f, for you to do with as you please.

HTH

DominicB

[chrismann85]

This is the code I use, I thought it may be of use to you:

'Browse to database using prompt
   Dim strPath As String
    Dim vntFile As Variant
    
    strPath = "C:\Documents and Settings\cmann29\My Documents\2007 Test Converter Tool\"

    ChDrive strPath
    
    vntFile = Application.GetOpenFilename("Database File *.mdb,*.mdb,All Files *.*,*.*")
    If vntFile = False Then Exit Sub


...insert the rest of you code here:)