CODEWITHSUNDEEP
sqlexcelvbaimportadodb
Vinnie Novido
Vinnie Novido

Reputation: 92

Importing excel data with SQL-query in VBA jumps over first row

I'm trying to copy data from one excel workbook to another. To do that i'm using a ADODB connection. Through a SQL query i'm copying all data, from the sheets that I want, to the other workbook. However, for some reason it skips the first row in each sheet. Thus the copied data always start on row 2. Maybe one of you can spot my mistake or explain to me why this happens?

Sub ImportExcelSQL()

Dim sheetName, sheetNewName, filepath, strConnection, Sql As String
Dim conn As ADODB.Connection
Dim rs As ADODB.Recordset

'-------- Close workbook updates ----------
Application.Calculation = xlCalculationManual
Application.ScreenUpdating = False
Application.DisplayAlerts = False
Application.AskToUpdateLinks = False
Application.StatusBar = "Importing...."
'------------------------------------------

filepath = Range("filepath")

strConnection = "DRIVER={Microsoft Excel Driver (*.xls, *.xlsx, *.xlsm, *.xlsb)};" _
                  & "DBQ=" + filepath + ";"

' Create the Connection and Recordset objects.
Set conn = New ADODB.Connection
Set rs = New ADODB.Recordset

' Open connection
conn.Open strConnection

' Loop through the sheets
Dim i As Integer
i = 1
Do Until IsEmpty(Range("importSheetNames").Offset(i, 0))

    If Range("importSaveSheetFlags").Offset(i, 0).Value = "Y" Then

    ' Get sheet names and input variables"
    sheetName = Range("importSheetNames").Offset(i, 0).Value
    sheetNewName = Range("exportSheetNames").Offset(i, 0).Value
    filepath = Range("filepath")

    ' Clear data sheet
    Sheets(sheetNewName).UsedRange.ClearContents

    ' ----------------------- SQL CODE ----------------------------
    Sql = "SELECT * FROM [" + sheetName + "$A:CA]"
    'Sql = "SELECT * FROM [" + sheetName + "$A1:CA1000]" 'Does not do any difference

    ' Open the connection and execute.
    'conn.Open strConnection
    Set rs = conn.Execute(Sql)

    ' Check we have data.
    If Not rs.EOF Then
       ' Transfer result.
       Sheets(sheetNewName).Range("A1").CopyFromRecordset rs
       ' Close the recordset
       rs.Close
    Else
       MsgBox "Error: No records returned.", vbCritical
    End If

    ' -------------- End of SQL --------------------------------------------

    End If


    i = i + 1
Loop

' Clean up
If CBool(conn.State And adStateOpen) Then conn.Close
Set conn = Nothing
Set rs = Nothing

'-----------------------------------------------
' Turn on automatic updating
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True
Application.DisplayAlerts = True
Application.AskToUpdateLinks = True
Application.StatusBar = "Finished"
'-----------------------------------------------

End Sub

Upvotes: 1

Views: 1058

Answers (1)

FunThomas
FunThomas

Reputation: 29296

Poblem is that Excel (or, the driver, to be more precise) expects that the first row of the source data contains a header row (holding the name of the columns).

In theory, there is a parameter in the connection string where you define if there is a header row, HDR=YES;, but it seems that this parameter is ignored for this driver, and instead, a value from the registry is read. See https://stackoverflow.com/a/49555650/7599798

As an alternative, you can use the OLE driver: Try

strConnection = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & filepath _ 
              & ";Extended Properties=""Excel 12.0 Xml;HDR=NO;"""

This respects the setting for HDR, so if you write HDR=NO, it will copy the first row, while HDR=YES skips it. If you have a header row, you can access the columns by their name in the SQL-statement, else you have to access them by the column characters.

Upvotes: 1

Related Questions