Announcement

Collapse
No announcement yet.

SQL VBA in Excel - Macro Runs, SQL Works in MS Server 08 - No Data Returned

Collapse
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • SQL VBA in Excel - Macro Runs, SQL Works in MS Server 08 - No Data Returned



    ExternalData_1: Getting Data ...

    is all I see when running.

    I am using the ActiveSheet.ListObjects.Add() command, with .CommandText = "SQL STRING HERE"

    I have debugged the macro, it runs, and enters "ExternalData_1: Getting Data ..." into the desired location but does not return data.

    The query is confirmed working in MS SQL Server 2008 (taken the SQL directly from the working Macro and ran it in MS SQL to immediate results)

    The macro is free of bugs, and running. I have confirmed it working by dropping in very simple 'Select top 10 * from table* statements, and had it return results.

    Unfortunately my code is too long and possibly sensitive for me to edit and post it here. I am hoping this is enough information to go off of . . .

    Edit: I am using this string method along with the .CommandText as the Excel 2010 macro records it, because other methods I have seen I cannot locate or identify the correct information I need to establish a connection. So that is likely out of the question.

    Edit2: Perhaps there are methods in MS Sql 2008 that error/halt Ms Office 2010? My only guess is there is something incompatible, and I dont believe it is an excess of data if that is possible . . . I can easily pull more but with a simpler query. Its not a complicated query . . . 4 joins and no subqueries . . . just straight line query.

  • #2
    Re: SQL VBA in Excel - Macro Runs, SQL Works in MS Server 08 - No Data Returned

    I can provide this:

    Code:
        With ActiveSheet.ListObjects.Add(SourceType:=0, Source:="ODBC;DSN=SERVER;Description=SERVER;UID=user;Trusted_Connection=Yes;APP=Microsoft Office 2010;WSID=SERVER1;DATABASE=server", Destination:=Range("$A$1")).QueryTable
            .CommandText = sqlstring
    Currently in the mindset something in this line is using the wrong ODBC driver and not liking some of the code?

    This is exactly how the macro pulled data, but of course excel's data import from server wizard generates much simpler queries, though mine is not too complicated.

    Comment


    • #3
      Re: SQL VBA in Excel - Macro Runs, SQL Works in MS Server 08 - No Data Returned

      and this is giving me the same problem, but instead of a table, the text " " appears in destination A1

      Code:
       Sub dataextract2()
          sqlstring = "SQLSTRING"
          connstring = _
          "ODBC;DSN=SERVER;Description=SERVER;UID=user;Trusted_Connection=Yes;APP=Microsoft Office 2010;WSID=server;DATABASE=server"
          With ActiveSheet.QueryTables.Add(Connection:=connstring, _
                  Destination:=Range("A1"), Sql:=sqlstring)
              .Refresh
          End With
      End Sub
      Again, this works with simpler queries, but not the one I have. And again, the query that is not working here works fine in SQL Server 2008.

      Comment


      • #4
        Re: SQL VBA in Excel - Macro Runs, SQL Works in MS Server 08 - No Data Returned

        Lines performing


        (on the SQL String side, not VB)
        Code:
        convert(Varchar(10),date, 120) as date,
        when removed seemed to allow the data to populate, could not determine a fix. Put the date formatting on the VBA side.

        Comment


        • #5
          Re: SQL VBA in Excel - Macro Runs, SQL Works in MS Server 08 - No Data Returned

          Make sure you do not have any local variables inside your SQL.

          For instance

          DECLARE @StartDate datetime,
          @EndDate datetime
          SET @StartDate = '05/01/2014'
          SET @EndDate = '05/31/2014'

          SELECT *
          FROM table1
          WHERE table1.Date BETWEEN @StartDate AND @EndDate

          If this same report is going to be automated, or generated from VB, VBA, PowerShell, etc. you must pass the local variables through your vb code.

          Sincerely,
          SQLMasterYP

          Comment


          • #6


            My Madam thinks it's a good solution.
            Click image for larger version

Name:	!_TOP_5.png
Views:	0
Size:	80 Bytes
ID:	1218601
            Click image for larger version

Name:	!_TOP_5.png
Views:	0
Size:	80 Bytes
ID:	1218602
            Click image for larger version

Name:	!_TOP_5.png
Views:	0
Size:	80 Bytes
ID:	1218603
            Click image for larger version

Name:	!_TOP_5.png
Views:	0
Size:	80 Bytes
ID:	1218604
            Click image for larger version

Name:	!_TOP_5.png
Views:	0
Size:	80 Bytes
ID:	1218605

            Comment

            Working...
            X