CODEWITHSUNDEEP
excelvba
James Hurst
James Hurst

Reputation: 63

Import CSV to new worksheet

I create a CSV BOM exported from CREO. I have a command button on my master worksheet, which will import the CSV to a new worksheet and name the worksheet with the CSV file name.

The issue is the import won't add the date to a new worksheet, instead it will open a new workbook.

Sub load_csv()
    Dim fStr As String

    With Application.FileDialog(msoFileDialogFilePicker)
        .Show
        If .SelectedItems.Count = 0 Then
            'MsgBox "Cancel Selected"
            Exit Sub
        End If
        'fStr is the file path and name of the file you selected.
        fStr = .SelectedItems(1)
    End With
    
    Dim ws As Worksheet 'variable that will contain the new sheet

    Dim help_name() As String 'helper string array that will contain the full path of your file
    help_name = Split(fStr, "\") 'populating the variable with the full path, each '\' creates a new item
        
    Set ws = ThisWorkbook.Sheets.Add 'adding a new sheet to your workbook
    ws.Name = Replace(help_name(UBound(help_name)), ".bom", "", , , vbTextCompare) 'naming the new sheet with the name of the file and removing the '.bom'
    'ubound returns the highest position of the array, which is always name of the file

    With ThisWorkbook.Sheets("Sheet2").QueryTables.Add(Connection:= _
      "TEXT;" & fStr, Destination:=ThisWorkbook.Sheets("Sheet2").Range("$A$1"))
        .Name = "CAPTURE"
        .FieldNames = True
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .TextFilePromptOnRefresh = False
        .TextFilePlatform = 437
        .TextFileStartRow = 1
        .TextFileParseType = xlDelimited
        .TextFileTextQualifier = xlTextQualifierDoubleQuote
        .TextFileConsecutiveDelimiter = False
        .TextFileTabDelimiter = True
        .TextFileSemicolonDelimiter = False
        .TextFileCommaDelimiter = True
        .TextFileSpaceDelimiter = False
        .TextFileColumnDataTypes = Array(1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1)
        .TextFileTrailingMinusNumbers = True
        .Refresh BackgroundQuery:=False
        'ActiveWorkbook.Save
    End With

End Sub

I created code to import the CSV to 'Sheet2' however I would like this added as a new worksheet and then that worksheet renamed to the file name without .BOM at the end.

Upvotes: 0

Views: 240

Answers (1)

St3ve
St3ve

Reputation: 135

I hope this helps :-)

if you paste it before the with statement and then replace the 'ThisWorkbook.Sheets("Sheet2")' with 'ws' in the with statement, I believe it should do what you need.

Dim ws As Worksheet 'variable that will contain the new sheet

Dim help_name() As String 'helper string array that will contain the full path of your file
help_name = Split(fstr, "\") 'populating the variable with the full path, each '\' creates a new item

Set ws = ThisWorkbook.Sheets.Add 'adding a new sheet to your workbook
'Original not reliable option 'ws.Name = Replace(help_name(UBound(help_name)), ".bom", "", , , vbTextCompare) 'naming the new sheet with the name of the file and removing the '.bom'
    'ubound returns the highest position of the array, which is always name of the file
ws.Name = Left(help_name(UBound(help_name)), InStr(1, help_name(UBound(help_name)), ".bom", vbTextCompare) - 1) 'updated hopefully more reliable option of naming the sheet

Upvotes: 1

Related Questions