Best way to use XLRunCode?

[ocnuybear]

Trying to understand how exactly does XLRunCode binds to an Excel process, look at first example:
(Have a couple of CSV files located in C:\Downloadsand needs to open them into one workbook)

Code: Select all

XLCreate>c:\Downloads\Temp.xlsx,1,xlH
LabelToVar>vba_code,theCode
XLRunCode>xlH,theCode
/*
vba_code:

    Dim oFSO, oFolder, oFile As Object
    Dim StrFile As Variant
    Dim i, n As Integer
    Dim Wkb As Workbook
    Dim ws As Worksheet
    Dim Your_Files() As String

    Set oFSO = CreateObject("Scripting.FileSystemObject")
    Set oFolder = oFSO.GetFolder("C:\Downloads")
    n = -1
    
    'Only retrieves CSV files into array
    For Each oFile In oFolder.Files
        If Right(oFile, 3) = "csv" Then
            n = n + 1
            ReDim Preserve Your_Files(n)
            Your_Files(n) = oFile
        End If
    Next oFile
    
    'Pulls all CSV files into the current workbook
    Set xTempWb = Workbooks.Open(Your_Files(0))
    
    xTempWb.Sheets(1).Copy
    Set xWb = Application.ActiveWorkbook
    xTempWb.Close False
    For i = 1 To 5
        Set xTempWb = Workbooks.Open(Your_Files(i))
        xTempWb.Sheets(1).Move , xWb.Sheets(xWb.Sheets.Count)
    Next

*/

The above pulls all CSV files into one Excel workbook, BUT as soon as you save and close file, Excel sort of hangs and then crashes & if you open another file Excel complains that it has recovered the following file: C:\Temp.xlsx

Now I have just made a small change in my VBA code as seen below:

Only thing changed was in stead of opening each & every CSV file and moving them to one book, the files was pulled in using a data query which is also much faster and it does not crash, WHY?

Code: Select all

XLCreate>c:\Downloads\Temp.xlsx,1,xlH
LabelToVar>vba_code,theCode
XLRunCode>xlH,theCode
/*
vba_code:

    Dim oFSO, oFolder, oFile As Object
    Dim StrFile As Variant
    Dim i, n As Integer
    Dim Wkb As Workbook
    Dim ws As Worksheet
    Dim Your_Files() As String

    Set oFSO = CreateObject("Scripting.FileSystemObject")
    Set oFolder = oFSO.GetFolder("C:\Downloads")
    n = -1
    
    'Only retrieves CSV files into array
    For Each oFile In oFolder.Files
        If Right(oFile, 3) = "csv" Then
            n = n + 1
            ReDim Preserve Your_Files(n)
            Your_Files(n) = oFile
        End If
    Next oFile
    
    'Pulls all CSV files into the current workbook
    Set Wkb = ActiveWorkbook
    For Each StrFile In Your_Files
        Wkb.Sheets.Add
        Set ws = Wkb.ActiveSheet
        With ws.QueryTables.Add(Connection:="TEXT;" & StrFile, Destination:=ws.Range("A1"))
            .TextFileParseType = xlDelimited
            .TextFileCommaDelimiter = True
            .Refresh
        End With
    Next

*/