[SOLVED] sql select statement in vba not pulling headers from excel closed workbook and some other values

sql select statement in vba not pulling headers from excel closed workbook and some other values

I am working with excel workbook to pull data of my closed workbook "RawData.xlsm" sheet name "all_register". following are the code, but this vba code unable to pull all headers (first rows/ columns names) as well as some cells values (value cell format is "General"

Option Explicit

Sub GetDataFromClosedFile()

    Dim cn As ADODB.Connection
    Dim rs As ADODB.Recordset
    
    Set cn = New ADODB.Connection
    
    cn.ConnectionString = _
        "Provider=Microsoft.ACE.OLEDB.12.0;" & _
        "Data Source=" & ThisWorkbook.Path & "/RawData.xlsm;" & _
        "Extended Properties='Excel 12.0 Macro;HDR=NO';"    

    cn.Open
    
    Set rs = New ADODB.Recordset
    
    rs.ActiveConnection = cn
    rs.Source = "SELECT * FROM [all_register$]"
    rs.Open
    
    Sheet1.Range("A1").CopyFromRecordset rs
    
    Sheet1.Range("A1").CurrentRegion.EntireColumn.AutoFit
    
    rs.Close
        
    cn.Close
    
End Sub

with this code some headers showed some not due to mismatch of column data with header (e.g header heading is "date" and below data is date like 23/7/2024. need expert attention to correct my code. I am not expert in VBA, try easy way in your expert advice

Solution

Your connection string specifies that headers should not be received, change HDR to YES

    cn.ConnectionString = _
        "Provider=Microsoft.ACE.OLEDB.12.0;" & _
        "Data Source=" & ThisWorkbook.Path & "/RawData.xlsm;" & _
        "Extended Properties='Excel 12.0 Macro;HDR=YES';"