ADODB CopyFromRecordset returns everthing perfectly except for plain numerical values

[L0ne_]

Good afternoon,

First off, thank you all so much for all the help you have provided my lurking self throughout my history of learning excel and vba.

On to the question:
I am using the following code to compile a bunch of daily inventory sheets into one master sheet. It functions perfectly, except returns blanks in the master sheet anywhere that there are plain numbers in the inventory sheets (aka serial numbers disappear). I have tried different formatting options in the inventory sheets without success.

Any and all help is greatly appreciated.

Thanks,
Ben

Sub loopthroughfolderINV()

  Dim fileStr As String
  fileStr = Format(Now, "dd-mmm-yyyy")
  
  Dim strFILE_PATH As String
  strFILE_PATH = "xxxxx"
  Dim i As Long
  Dim arFileNames() As String
  Dim strConn As String
  Dim strFileName As String
  Dim objRS As Object
 
  ReDim arFileNames(1 To 1000)
  strFileName = Dir(strFILE_PATH)
  strConn = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" & _
      strFILE_PATH & strFileName & ";Extended Properties=""Excel 8.0;"""

  Do While Len(strFileName)
    i = i + 1
    arFileNames(i) = "SELECT * FROM `" & strFILE_PATH & strFileName & "`.[Inventory$]"
    strFileName = Dir
  Loop

  If i = 0 Then MsgBox "No files found": Exit Sub

  ReDim Preserve arFileNames(1 To i)
  Set objRS = CreateObject("ADODB.Recordset")

  With objRS
    .Open Join$(arFileNames, " UNION ALL "), strConn
    Cells(Rows.Count, 1).End(xlUp).Offset(1).CopyFromRecordset objRS
    .Close
  End With

  Set objRS = Nothing
  
  Range("A:A").SpecialCells(xlCellTypeBlanks).EntireRow.Delete
  
End Sub

 

[L0ne_]

Please see attached (and rather childish )example.
Source (inventory) is on the left, the red line represents the running of the macro, and the output (master sheet) is on the right.