Import issues, excel creates new worksheet

[JCLLE]

Hello everyone


Only been doing this a couple of months so very new to all of this.
Everytime I run the macro excel creates a new worksheet. Since I need to put formulas in the worksheet I need excel to put the data in the same worksheet everytime.
Thanks in advance!

'
    Range("A1").Select
    ActiveWorkbook.Queries.Add Name:="son (3)", Formula:= _
        "let" & Chr(13) & "" & Chr(10) & "    Source = Csv.Document(File.Contents(""L:\PRI_PRINGLES\C&S - customization&shipping\16. Scripts\Cans\son.txt""),[Delimiter=""|"", Columns=18, Encoding=1252, QuoteStyle=QuoteStyle.None])," & Chr(13) & "" & Chr(10) & "    #""Changed Type"" = Table.TransformColumnTypes(Source,{{""Column1"", type text}, {""Column2"", type text}, {""Column3"", type text}, {""Column4"", type text}, {""Column" & _
        "5"", type text}, {""Column6"", type text}, {""Column7"", type text}, {""Column8"", type text}, {""Column9"", type text}, {""Column10"", type text}, {""Column11"", type text}, {""Column12"", type text}, {""Column13"", type text}, {""Column14"", type text}, {""Column15"", type text}, {""Column16"", type text}, {""Column17"", type text}, {""Column18"", type text}})" & Chr(13) & "" & Chr(10) & "in" & _
        "" & Chr(13) & "" & Chr(10) & "    #""Changed Type"""
    ActiveWorkbook.Worksheets.Add
    With ActiveSheet.ListObjects.Add(SourceType:=0, Source:= _
        "OLEDB;Provider=Microsoft.Mashup.OleDb.1;Data Source=$Workbook$;Location=""son (3)"";Extended Properties=""""" _
        , Destination:=Range("$A$1")).QueryTable
        .CommandType = xlCmdSql
        .CommandText = Array("SELECT * FROM [son (3)]")
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .BackgroundQuery = True
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .PreserveColumnInfo = True
        .ListObject.DisplayName = "son__3"
        .Refresh BackgroundQuery:=False
    End With
End Sub

 

[Herakles]

I am assuming that you want the data in the worksheet replaced when you import the csv file.

This works for me.

Public Sub subCSV_Import()
Dim Ws As Worksheet
Dim varFile As Variant

    ActiveWorkbook.Save

    Set Ws = ActiveWorkbook.Sheets("PO Data")
        
    Ws.Activate
    
    Ws.Cells.ClearContents
    
    ' Hardcode the name of the file to import or
    ' varFile = "C:\Dump\ImportData.csv"
    
    ' select the file.
    varFile = Application.GetOpenFilename("Text Files (*.csv),*.csv", , "Please select text file...")
    
    If varFile <> False Then
    
        With Ws.QueryTables.Add(Connection:="TEXT;" & varFile, Destination:=Ws.Range("A1"))
             .TextFileParseType = xlDelimited
             .TextFileCommaDelimiter = True
             .Refresh
        End With

    End If

End Sub

 

[Fluff]

Hi & welcome to MrExcel.
What happens if you just remove this line

ActiveWorkbook.Worksheets.Add