+ Reply to Thread
Results 1 to 12 of 12

Macro to change odbc connection settings

  1. #1
    bugmenot
    Guest

    Macro to change odbc connection settings

    Running the macro recorder I got this:

    Please Login or Register  to view this content.
    Now I want the 2 YEARS to be able to change based on cell data... Or if it helps the 2 cells are just This Year and last year (ie. NOW() and NOW()-365 but formatted as custom = YYYY)

    So I have this, but it seems to throw me an error everytime I try it:
    Please Login or Register  to view this content.
    The error I get now in Microsoft VB is

    Run-time error '1004':
    Application-defined or object-defined error

    I also tried changing
    Please Login or Register  to view this content.
    to
    Please Login or Register  to view this content.
    after making
    Please Login or Register  to view this content.

    I also tried
    Please Login or Register  to view this content.
    after setting D10 on sheet3 to =GetSQLString()


    Thanks for your help!
    Last edited by bugmenot; 07-15-2009 at 05:21 PM. Reason: Changed title to be more clear

  2. #2
    Forum Guru romperstomper's Avatar
    Join Date
    11-04-2008
    Location
    A1
    MS-Off Ver
    Most
    Posts
    12,302

    Re: Macro to change odbc connection settings

    Have you tried:
    Please Login or Register  to view this content.
    Remember what the dormouse said
    Feed your head

  3. #3
    Valued Forum Contributor
    Join Date
    08-26-2006
    Location
    -
    MS-Off Ver
    2010
    Posts
    388

    Re: Macro to change odbc connection settings

    Have you looked at Microsoft Query?
    In Excel 2007 - Data - From Other Sources - From Microsoft Query.
    Use two cells to hold date parameters as criteria in the query. Data can be automatically refreshed when date is changed. No macros required with this method.

  4. #4
    bugmenot
    Guest

    Re: Macro to change odbc connection settings

    Re: romperstomper
    Tried that and still get an error


    Re:T-J
    I am just using a custom sql command so the option to add criteria is blacked out... am I missing something?
    EDIT: Oops, T-J: I forgot to mention that I do not know how to do this, but I was told by people that develop the plugin I am using to connect to a database that currently that is a bug for sp_reports, but if you could point me in the direction to figure out how to do that, it would be great so I can confirm it is a bug and/or suggest anything else
    Last edited by bugmenot; 07-16-2009 at 01:24 PM.

  5. #5
    Valued Forum Contributor
    Join Date
    08-26-2006
    Location
    -
    MS-Off Ver
    2010
    Posts
    388

    Re: Macro to change odbc connection settings

    In the last step of the MS Query wizard select 'View data or edit query in Microsoft Query', then enter parameter(s) in the criteria grid.
    For more information see Microsoft Query help topic "Create a parameter query"

    If that doesn't work there are two other ways I know of to specify parameters in a query.

    1. Edit the SQL command text in Connection Properties and enter question marks for each parameter in the WHERE clause
    e.g. querying the Northwind Orders table:
    Please Login or Register  to view this content.
    2. Save MS Query file (*.dqy), edit this file in Notepad, add parameters to the SQL statement as above and enter parameter names and data types on the lines below, see http://support.microsoft.com/kb/164729

  6. #6
    Forum Guru romperstomper's Avatar
    Join Date
    11-04-2008
    Location
    A1
    MS-Off Ver
    Most
    Posts
    12,302

    Re: Macro to change odbc connection settings

    Odd. What about:
    Please Login or Register  to view this content.
    If not, is it still the same error and the same line?

  7. #7
    bugmenot
    Guest

    Re: Macro to change odbc connection settings

    RE: romperstomper
    Same error, same line


    RE: T-J
    Since I am not accessing a data table but rather generating a report I can not edit parameters as I am using a SQL statement that generates a report I do not have the option of editing parameters. I pretty much can only edit the SQL statement


    If anyone could provide any input that would be much appreciated

    Okay so I realized that I am not defining what sheet to use
    so I tried after .CommandText =
    Please Login or Register  to view this content.
    and it still did not work, I also tried
    Please Login or Register  to view this content.
    And when making a cell =SQL() I get a #value! error whereas before when i did not include the Sheet3. it worked and gave me

    sp_report BalanceSheetStandard show Label, Amount parameters DateFrom = {d'2008-01-01'}, DateTo = {d'2009-12-31'}, SummarizeColumnsBy = 'Month'
    Last edited by bugmenot; 07-20-2009 at 02:59 PM.

  8. #8
    bugmenot
    Guest

    Re: Macro to change odbc connection settings

    Update:
    the =SQL problem was fixed, I just realized it did not work when the debugger was open

  9. #9
    bugmenot
    Guest

    Re: Macro to change odbc connection settings

    Okay weird problem:

    I redid the macro, got this:
    Please Login or Register  to view this content.
    and tried running just that and got an error.... Is excel recording the macro wrong?
    Last edited by bugmenot; 07-21-2009 at 01:53 PM.

  10. #10
    Forum Guru romperstomper's Avatar
    Join Date
    11-04-2008
    Location
    A1
    MS-Off Ver
    Most
    Posts
    12,302

    Re: Macro to change odbc connection settings

    Which line and which error? The Refresh line, or earlier?

  11. #11
    bugmenot
    Guest

    Re: Macro to change odbc connection settings

    Same line as before... the .Commandtext line
    Edit: And the same error

    I posted another thread
    Click Here

    I think it is a bug in excel. One person in a link posted in the other thread mentioned that they had to change the connection type, then change the SQL command, then change the connection type back.

    Here is the link where they talk about that:
    http://p2p.wrox.com/excel-vba/29037-...has-1-rpt.html
    Last edited by bugmenot; 07-21-2009 at 06:22 PM. Reason: Clarity

  12. #12
    Forum Guru romperstomper's Avatar
    Join Date
    11-04-2008
    Location
    A1
    MS-Off Ver
    Most
    Posts
    12,302

    Re: Macro to change odbc connection settings

    I've posted in that other question (since the answer was more directly relevant there than here). Changing the commandtext has always been problematic in Excel if you have more than one PT running off a single pivotcache.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1