Copy Data from Multiple Workbooks Using ADO Format Issue

[BenChod]

Hello All -

I am using the following code using ADO connection to extract data from multiple workbooks without them being opened to speed up the process. I made minor modifications based on the my specific need. I am stuck on one part: I want to copy the values and format from the source files and paste to my target workbook. As it stands, only the values are being copied. Hoping someone can take a quick look and provide guidance on how to copy over the format as well. Thanks in advance for the help.

Sub GetData_Example6()
    Dim MyPath As String
    Dim FilesInPath As String
    Dim sh As Worksheet
    Dim MyFiles() As String
    Dim Fnum As Long
    Dim rnum As Long
    Dim destrange As Range

    MyPath = "C:\Data\Test"    

    'Add a slash at the end if the user forget it
    If Right(MyPath, 1) <> "\" Then
        MyPath = MyPath & "\"
    End If

    'If there are no Excel files in the folder exit the sub
    FilesInPath = Dir(MyPath & "*.xl*")
    If FilesInPath = "" Then
        MsgBox "No files found"
        Exit Sub
    End If

    On Error GoTo CleanUp
    Application.ScreenUpdating = False

    'Add worksheet to the Activeworkbook 
    Set sh = ActiveWorkbook.Worksheets.Add
    sh.Name = "Test"

    'Fill the array(myFiles)with the list of Excel files in the folder
    Fnum = 0
    Do While FilesInPath <> ""
        Fnum = Fnum + 1
        ReDim Preserve MyFiles(1 To Fnum)
        MyFiles(Fnum) = FilesInPath
        FilesInPath = Dir()
    Loop

    'Loop through all files in the array(myFiles)
    If Fnum > 0 Then
        For Fnum = LBound(MyFiles) To UBound(MyFiles)

            'Find the last row with data
            rnum = LastRow(sh)

            'create the destination cell address
            Set destrange = sh.Cells(rnum + 1, "A")

            'Get the cell values and copy it in the destrange
            
            MyFiles(Fnum).Copy
            destrange.PasteSpecial xlValues
            destrange.PasteSpecial xlPasteFormats
            Application.CutCopyMode = False
                                    
            GetData MyPath & MyFiles(Fnum), "Sheet1", "A1:AC5100", destrange, False, False
        Next
    End If

CleanUp:
    Application.ScreenUpdating = True
End Sub

[Aflatoon]

You can't copy formatting using ADO, I'm afraid.

[BenChod]

Thanks for responding. Disappointing to know you can't copy formatting using ADO. Plus this ADO is quirky. When I use it to copy and paste data, not all the fields are copied over. Several fields come up blank.

[Aflatoon]

I would suspect that you have mixed data types in one column. Proper databases don't allow that but of course Excel does. ADO doesn't generally cope very well with that. Adding IMEX=1 to the connection string can help but it is by no means infallible.

[BenChod]

I added IMEX=1 and that worked. Now all the fields are being populated. Thanks for the help.