CODEWITHSUNDEEP
excelvbamacos
AG10
AG10

Reputation: 259

Macro to prompt user to select CSV files for import into existing sheet in workbook

I am running a macro which automatically takes csv files and import them into specific worksheets in my workbook. However, I would want to add greater flexibility by having the user select the files for import rather than have the macro automatically grab the csv files because the naming could change as well as the directory. I am new to VBA and have been trying to better understand the MsoFileDialogType and GetOpenFilename but having difficulty trying to grasp the concept/implementation into my code.

What I ultimately want is for the user to click a button on the workbook front-end. Be prompted with a message to select the first csv file for import. This csv file will be imported into a pre-named worksheet in the workbook temp1. However since the data files come in pairs, I want the user to be able to select the next csv file after the first one into temp2.

What I have currently is:

Worksheets.Add
ActiveSheet.Name = "temp1"
With ActiveSheet.QueryTables.Add(Connection:= _
        "TEXT;MAC Directory path here" _
        , Destination:=Range("A1"))
        .Name = "temp 1 03.02.12"
        .FieldNames = True
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .RefreshOnFileOpen = False
        .BackgroundQuery = True
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .TextFilePromptOnRefresh = False
        .TextFilePlatform = xlMacintosh
        .TextFileStartRow = 1
        .TextFileParseType = xlDelimited
        .TextFileTextQualifier = xlTextQualifierDoubleQuote
        .TextFileConsecutiveDelimiter = False
        .TextFileTabDelimiter = False
        .TextFileSemicolonDelimiter = False
        .TextFileCommaDelimiter = True
        .TextFileSpaceDelimiter = False
        .TextFileColumnDataTypes = Array(1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, _
        1, 1, 1)
        .Refresh BackgroundQuery:=False
        .UseListObject = False
End With
ActiveSheet.Move after:=Worksheets(Worksheets.Count)

Thank you.

Upvotes: 0

Views: 22503

Answers (1)

Fionnuala
Fionnuala

Reputation: 91376

Perhaps something on these lines.

Sub GetCSVList()
Dim dlgOpen As FileDialog
Set dlgOpen = Application.FileDialog(msoFileDialogFilePicker)
With dlgOpen
    .AllowMultiSelect = True
    ''Start in
    .InitialFileName = "Z:\docs\"
    .Show
End With

For Each fname In dlgOpen.SelectedItems
    ImportCSV fname
Next
End Sub

Sub ImportCSV(fname)
Set ws = Worksheets.Add(after:=Worksheets(Worksheets.Count))
ws.Name = "temp" & Worksheets.Count + 1

With ws.QueryTables.Add( _
        Connection:="TEXT;" & fname, _
        Destination:=Range("A1"))
    .Name = "Temp" & Worksheets.Count + 1
    .FieldNames = True
    .RowNumbers = False
    .FillAdjacentFormulas = False
    .PreserveFormatting = True
    .RefreshOnFileOpen = False
    .BackgroundQuery = True
    .RefreshStyle = xlInsertDeleteCells
    .SavePassword = False
    .SaveData = True
    .AdjustColumnWidth = True
    .TextFilePromptOnRefresh = False
    .TextFilePlatform = xlMacintosh
    .TextFileStartRow = 1
    .TextFileParseType = xlDelimited
    .TextFileTextQualifier = xlTextQualifierDoubleQuote
    .TextFileConsecutiveDelimiter = False
    .TextFileTabDelimiter = False
    .TextFileSemicolonDelimiter = False
    .TextFileCommaDelimiter = True
    .TextFileSpaceDelimiter = False
    .Refresh BackgroundQuery:=False
    '.UseListObject = False
End With
End Sub

Upvotes: 4

Related Questions