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.

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


    I can provide this:


    Code
    1. 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
    2. .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.

  • 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
    1. Sub dataextract2()
    2. sqlstring = "SQLSTRING"
    3. connstring = _
    4. "ODBC;DSN=SERVER;Description=SERVER;UID=user;Trusted_Connection=Yes;APP=Microsoft Office 2010;WSID=server;DATABASE=server"
    5. With ActiveSheet.QueryTables.Add(Connection:=connstring, _
    6. Destination:=Range("A1"), Sql:=sqlstring)
    7. .Refresh
    8. End With
    9. 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.

  • 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
    1. 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.

  • 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

  • My Madam thinks it's a good solution.
    [ATTACH=JSON]{"data-align":"none","data-linktype":"1","data-linkurl":"https:\/\/vk.com\/parka_shop","data-size":"full","title":"!_TOP_5.png","data-attachmentid":1218601}[/ATTACH]
    [ATTACH=JSON]{"data-align":"none","data-linktype":"1","data-linkurl":"http:\/\/tradimm.ru\/","data-size":"full","title":"!_TOP_5.png","data-attachmentid":1218602}[/ATTACH]
    [ATTACH=JSON]{"data-align":"none","data-linktype":"1","data-linkurl":"https:\/\/aleba.ru\/","data-size":"full","title":"!_TOP_5.png","data-attachmentid":1218603}[/ATTACH]
    [ATTACH=JSON]{"data-align":"none","data-linktype":"1","data-linkurl":"https:\/\/tesorogold.ru\/","data-size":"full","title":"!_TOP_5.png","data-attachmentid":1218604}[/ATTACH]
    [ATTACH=JSON]{"data-align":"none","data-linktype":"1","data-linkurl":"http:\/\/ekolmil.ru\/","data-size":"full","title":"!_TOP_5.png","data-attachmentid":1218605}[/ATTACH]

    Images

    • !_TOP_5.png
    • !_TOP_5.png
    • !_TOP_5.png
    • !_TOP_5.png
    • !_TOP_5.png