EXCEL VBA ADODB Query Error

[kashif.special2005]

Hi,

I am trying to transpose data from Columnar to Tabular Dataset and I am using below code however, it is giving an error message that
"The SELECT statement includes a reserved word or an argument name that is misspelled or missing, or the punctuation is incorrect.", I am not able to understand what the error means, I don't know where I am doing wrong in the query. Please help me to solve this problem.

Sheet1 Data:

Line Of Business Manager Jan Feb Mar
Copier Sale Jim 167642 1908785 2185037
Parts Mike 2378240 2372974 2537793

Note:- The data is around 40000

Sheet2 Headings:-

Line Of Business Manager Month Value

Sub TransposeTable()
    Dim Con As ADODB.Connection
    Dim rsMySet As ADODB.Recordset
    Dim strSQL As String
    Dim strCon As String
    Dim i As Integer
     
    strCon = "provider=microsoft.ace.oledb.12.0;Data Source=C:\Users\Kashif\Desktop\Transpose Data.xlsm;Extended properties=Excel 12.0;"
    Set Con = New ADODB.Connection
    Con.Open strCon
    
    Set rsMySet = New ADODB.Recordset
    rsMySet.Open "[Sheet1$]", Con, adOpenDynamic, adLockBatchOptimistic
    
    For i = 2 To rsMySet.Fields.Count - 1
     
        'Use the recordset field.name property to build out the SQL string for the current field
        strSQL = "INSERT INTO [Sheet2$] ([Line Of Business], [Manager], [Month], [Value]) " & _
        "SELECT [Line Of Business], [Manager], " & _
        "'" & rsMySet.Fields(i).Name & "'" & " AS Month, " & _
        "[" & rsMySet.Fields(i).Name & "] " & _
        "FROM [Sheet1$];"
         
        Con.Execute strSQL


    Next i
    
    Con.Close
    Set Con = Nothing
    Set rsMySet = Nothing
 
End Sub

Thanks
Kashif

 

[RoryA]

I imagine Month is a reserved word so try using a different alias for your field. Not really sure why you have the same field in there twice?

 

[kashif.special2005]

Hi RoryA,

Thanks for reply, yes I changed the name from Month to Months and it is working fine .

Thanks
Kashif