ExternalData_1 when importing CSV

[jaryszek]

Hi Guys,

crossed post from here:
ExternalData_1 when importing CSV to workbook

i am using this code to import csv into workbook:

Sub CSVDownload()

    Dim varCsvData As Variant
    Dim path As Variant
    
    path = "D:\Pulpit\Newest Pull request\ImportingBundles\CSVTest.csv"

   GetCSVData (path)
    
End Sub


Public Function GetCSVData(ByVal strFile As String) As Object
    Dim lngSplit As Long, strTable As String, strPath As String

Dim cnnConnect As Object
Dim rstRecordset As Object
Dim rngDestination As Range

Set cnnConnect = CreateObject("ADODB.connection")
Set rstRecordset = CreateObject("ADODB.recordset")
Set rst = CreateObject("ADODB.recordset")
    Dim strConnection As String, strSQL As String
    
    lngSplit = InStrRev(strFile, "\")
    
    strTable = Mid$(strFile, lngSplit + 1)
    strPath = Left$(strFile, lngSplit - 1)
    
    strConnection = "Provider=Microsoft.ACE.OLEDB.12.0;" & _
                    "Data Source=" & strPath & ";" & _
                    "Extended Properties=Text;"
                    
    strSQL = "SELECT * FROM " & strTable & ";"
    
    rstRecordset.Open _
    Source:=strSQL, _
    ActiveConnection:=strConnection, _
    CursorType:=1, _
    LockType:=3, _
    Options:=1
    
On Error Resume Next
Dim qt As QueryTable
Set qt = Worksheets(1).ListObjects(1).QueryTable
On Error GoTo 0

    If Not qt Is Nothing Then
''        With ActiveSheet.ListObjects(1)
''''        If .Range.Columns.Count > 1 Then
''''            .Range.Resize(, .Range.Columns.Count - 1).Columns.Delete
''''        End If
''
''        End With
        With qt
            .PreserveFormatting = False
             Set .Recordset = rstRecordset
            .Refresh True
        End With
    Else
         With ActiveSheet.ListObjects.Add(SourceType:=4, Source:=rstRecordset, Destination:=Range("$A$1")).QueryTable
            .RowNumbers = False
            .PreserveFormatting = True
            .RefreshStyle = 1
            .AdjustColumnWidth = True
            .ListObject.DisplayName = "Table_SampleTextFile4a"
            .Refresh True
        End With
    End If
    
    Set rstRecordset = Nothing
    Set qt = Nothing
    
End Function

Example workbooks you can download from crossed link.

Please help,
Jacek

 

[Joe4]

Thank you for posting the links to the Cross-Post.
However, I do not see any question here, just some code. What exactly is your question?

Though you mentioned the Cross-Post and provided the link to it, please still post the complete question you have here.
People can refer to the Cross-Post to see what else has already been suggested/tried on this problem, but they shouldn't have to go there to find out exactly what the question is.
That should be contained here as well, so this is a complete post/question.

 

[jaryszek]

Hello,

sorry for ma late answer.
My question was if connnections created automatially in big number like 50 are not harmfull?

I got 1 answer and i think it should be not taking Excel memory and space.

Best,
Jacek