Name a new sheet after a filename RRS feed

  • Question

  • I would like to import data into a new sheet and name the sheet after the filename: 2013-2014 - data set_clientname.csv, Since I only want to keep the "clientname" part, what should I do?


    Tuesday, October 8, 2013 7:08 PM

All replies

  • The following macro should work to import a selected CSV file into a new sheet in the current workbook, named as requested, though the import parameters may need tweaking:

    Sub ImportCSV()
    Dim fDialog As FileDialog
    Dim xlBook As Workbook
    Dim xlSheet As Worksheet
    Dim strFilename As String
    Dim vFilename As Variant
    Dim strSheetname As String
        Set xlBook = ActiveWorkbook
        Set fDialog = Application.FileDialog(msoFileDialogFilePicker)
        With fDialog
            .Title = "Select the CSV file to be inserted and click OK"
            .AllowMultiSelect = False
            .Filters.Add "CSV Files", "*.csv"
            .InitialView = msoFileDialogViewList
            If .Show <> -1 Then Exit Sub
            strFilename = fDialog.SelectedItems.Item(1)
            Set xlSheet = xlBook.Worksheets.Add
            vFilename = Split(strFilename, "_")
            strSheetname = vFilename(UBound(vFilename))
            xlSheet.Name = Replace(LCase(strSheetname), ".csv", "")

            With xlSheet.QueryTables.Add(Connection:= _
                                         "TEXT;" & strFilename, _
                .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 = False
                .TextFileCommaDelimiter = True
                .TextFileTrailingMinusNumbers = True
                .Refresh BackgroundQuery:=False
            End With
        End With
    End Sub

    Graham Mayor - Word MVP

    Wednesday, October 9, 2013 11:38 AM