Trying to adding table from csv to new sheet in existing Excel file - ending up empty table

[dikken20]

Hello

Been using an excel sheet for years now.
One of its functionalities is to load a table from an other CSV file and paste its data in a "temp" sheet in order to process the data.
The processed data then copying to its proper sheet while the "temp" sheet is deleted.

One day the process didn't work.
At first I thought that the CSV structured file changed so it can't use the data as it should.
Looking at the CSV today - it looks fine actually.
So I just checked it using F8 line after line, and I realized that the data from the CSV file does not load on the "temp" sheet anymore!
Nothing changed in the code plus the CSV file remain the same structured.

This is the code:

Sheets.Add After:=Sheets(Sheets.Count)
    Sheets(Sheets.Count).Name = "temp"
    Sheets("temp").Activate
    With ActiveSheet.QueryTables.Add(Connection:="TEXT;C:\user\Yesterday.csv", _
        Destination:=Range("$A$1"))
        .Name = "Yesterday"
        .FieldNames = True
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .TextFilePromptOnRefresh = False
        .TextFilePlatform = 1255
        .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)
        .TextFileTrailingMinusNumbers = True
'        .refresh BackgroundQuery:=False
    End With

Help?

 

[rlv01]

If you perform the macro actions manually, can you still add a sheet "temp"?

 

[dikken20]

The "temp" sheet added to the file using the macro, and can be added manually (to be precise - it is added in another name and changed to "temp" )

Basically everything works ok, its just the data that is not "copied" / retrieved to the "temp" sheet and therefore there is not data to be processed while the CSV source data contain data (meaning, it's not empty)

 

[rlv01]

Try un-commenting the refresh line. That is, change:

'        .refresh BackgroundQuery:=False
    End With

to this

        .refresh BackgroundQuery:=False
    End With

 

[dikken20]

I love you
Thanks