Incorrect syntax near the keyword ''IN'' error

  • Hi,

    i'm trying to insert data queried from SQL server into an Excel sheet using the following query.

    "INSERT INTO [sheet1$] IN '" & App.Path & strWBName & ".xls' 'Excel 8.0;' Select * from TEST.dbo.Testing"

     Later i execute the query using the SQL server connection.

    I'm getting following error:

    Run-Time error '-214721900 (80040e14)':

    [Microsoft][ODBC SQL Server Driver][SQL Server] Incorrect syntax near the keyword 'IN'".

    Can anyone help me out of the situation?

    Thanks in advance

    Regards

    Naresh

     

  • Have you thought about using a querytable in excel. It might be easier?

  • This is the format you want

    "INSERT INTO OPENROWSET('Microsoft.Jet.OLEDB.4.0', 'Excel 8.0;Database='" & App.Path & strWBName + "', [sheet1$]) (col1,col2,col3) SELECT col1,col2,col3 FROM TEST.dbo.Testing'"

    Note that the spreadsheet must exist and have columnheaders in row 1 (e.g. col1,col2,col3 above) it will not work otherwise

    Otherwise use DTS

     

    Far away is close at hand in the images of elsewhere.
    Anon.

  • Much easier to use a query table unless you have a specific reason not too!

  • I looked up 'Query Table' in Excel to see what you were talking about, couldn't find it. Are you talking about importing the data into the Excel sheet, from Excel, by using a database query? In other words, importing the data fro within, rather than injecting it from without?

    Just curious, thought I might learn something if I ask...

     

  • Yes. In Excel goto Data/Get External Data.New Database query. Follow the steps. It's fairly straightforward and is a good quick way to display your database data in a spreadsheet. Hope it's helpful. Sounds like you might be interested in pivot tables as well (same sort of thing, but you can split the data up and get counts, averages etc).

    cheers

    Andy

  • I create hidden spreadsheet in a workbook with a macro that automatically starts when the workbook is opened and creates a new workbook, populates it with the data and closes the original workbork leaving the data behind. The user can then choose what they do with the results.

    The code is standard apart from the connection string and the stored procedure used. It creates the headings from the sql column names and the data type to determine data format.

    Far away is close at hand in the images of elsewhere.
    Anon.

  • Can you please paste the macro code in here...I guess it would be great to learn its functionality.

    Naveen

  • Here you go

    Sub Auto_Open()

        Dim SQL

        Dim r As Integer

        Dim c As Integer

        Dim i As Integer

        Dim OldBook As Workbook

        Dim NewBook As Workbook

        Dim Conn As ADODB.Connection

        Dim RS As ADODB.Recordset

        Set OldBook = ThisWorkbook

        Set Conn = New ADODB.Connection

        connstr = "connection string here"

        SQL = "sql here"

        Conn.Open connstr

        Set RS = Conn.Execute(SQL)

        Workbooks.Add

        r = 1

        If RS.EOF = False Then

            i = RS.Fields.Count - 1

            For c = 0 To i

                ActiveSheet.Cells(r, c + 1).Value = RS.Fields(c).Name

            Next c

        End If

        With Range(ActiveSheet.Cells(r, 1), ActiveSheet.Cells(r, i + 1)).Interior

            .ColorIndex = 15

            .Pattern = xlSolid

            .PatternColorIndex = xlAutomatic

        End With

        With Range(ActiveSheet.Cells(r, 1), ActiveSheet.Cells(r, i + 1)).Borders

            .LineStyle = xlContinuous

            .Weight = xlThin

            .ColorIndex = xlAutomatic

        End With

        Do Until RS.EOF

            r = r + 1

            For c = 0 To i

                Select Case RS.Fields(c).Type

                    Case 3    'int

                        ActiveSheet.Cells(r, c + 1).NumberFormat = "0"

                        ActiveSheet.Cells(r, c + 1).Value = RS(c)

                    Case 131  'Numeric

                        ActiveSheet.Cells(r, c + 1).NumberFormat = "#,#0.00"

                        ActiveSheet.Cells(r, c + 1).Value = Val(RS(c))

                    Case 135  'Date

                        ActiveSheet.Cells(r, c + 1).NumberFormat = "dd/mm/yyyy"

                        ActiveSheet.Cells(r, c + 1).HorizontalAlignment = xlLeft

                        ActiveSheet.Cells(r, c + 1).Value = RS(c) & ""

                    Case 200  'varchar

                        ActiveSheet.Cells(r, c + 1).NumberFormat = "@"

                        ActiveSheet.Cells(r, c + 1).Value = RS(c) & ""

                    Case Else

                        ActiveSheet.Cells(r, c + 1).Value = RS(c) & ""

                End Select

            Next c

            RS.MoveNext

        Loop

        ActiveSheet.Columns.AutoFit

        ActiveSheet.Range("A1").Select

        RS.Close

        Conn.Close

        OldBook.Close

    End Sub

    Far away is close at hand in the images of elsewhere.
    Anon.

  • That's not a query table though... It can be cumbersome going through a recordset record by record. This is the VBA to add a query table. You need to put your DSN name in and obviously change the command text to the sql you want to use.

    Public Sub CreateQueryTable()

        With ActiveWorkbook.ActiveSheet.QueryTables.Add(Connection:= _

            "ODBC;DSN=YOURDSNNAME" _

            , Destination:=Range("A1"))

            .CommandText = Array("SELECT * FROM TESTTABLE")

            .Name = "WHATEVERQUERYNAMEYOULIKE"

            .FieldNames = True

            .RowNumbers = False

            .FillAdjacentFormulas = False

            .PreserveFormatting = True

            .RefreshOnFileOpen = False

            .BackgroundQuery = True

            .RefreshStyle = xlInsertDeleteCells

            .SavePassword = True

            .SaveData = True

            .AdjustColumnWidth = True

            .RefreshPeriod = 0

            .PreserveColumnInfo = True

            .Refresh BackgroundQuery:=False

        End With

    End Sub

     

     

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply