Posts by pulsar777

    Re: Starting with DLL function


    Hi S O,
    really thanks for a nice reference.
    I found some basic examples with common libraries like Kerner32, or User32.


    In one macro file I have some functions, e.g. one that connects and another that retrieves data from an OLAP cube,
    both referring to same library file with extension .XLL. (which is part of add-in installation)


    I was wondering, how to proceed, in order to find out about any other functions that I could use in conjunction with that add-in.
    Either to find it under Tools / References and use object browser in some way, or is there any sort of a "reader" to do that ?


    As for the data types and VBA version backwards compatibility, let's suppose these would be secondary in this case.


    Thanks!

    Hi !
    I've never used a DLL function in my syntax yet but would like to know how to:


    1. find out list of functions / subs available in DLL
    2. corretly refer to function (by its name, lib name, arguments, passing args, Alias etc)


    I have installed a special add-in and noticed in one file there is a "Lib function" referred to .xll file,
    which is part of the installation package.
    But I wasn't able to find out the way how function is built


    Can you advise please ?

    Re: Wait until Refresh is done


    Hi, does someone know what

    Code
    Application.CalculateUntilAsyncQueriesDone

    does exactly ?
    The Microsoft help only says: " Runs all pending queries to OLEDB and OLAP data sources. "
    At first sight this works to solve my issue, however I'm not certain about it.

    Hi, I use ListObject.QueryTable property to get external data. Driver ODBC, BackgroundQuery = False.
    I have some lines of code after QueryTable.Refresh, that are executed even the Query still runs.
    I'd like them to wait until the Refresh is done.
    I tried to switch BackgroundQuery to True, Application.Wait, also a DoEvents method, but these don't do the trick.

    Re: ADODB CASE WHEN problem


    Finally I achieved what I desired.


    forum.ozgrid.com/index.php?attachment/67741/


    I left ADO recordset and went with QueryTable property. I believe it works when conditions are sorted from most to least (ABC = 4 to JKM = 1) .
    Only small downsize is that I seem not to get rid of column reader that comes with retrieved categories, so I query results on sheet3 and link just values without header.


    Can this be somehow overcome?
    Also in SQL statement, is there a way not to refer to data$ sheet but rather a listobject name?


    Thank you

    Re: ADODB CASE WHEN problem


    Can I use Sheet.Querytables().CommandText after connecting to ThisWorkbook from within? If yes, how the connection string is built? I tried same connection string as at ADODB, but it fails.

    Re: ADODB CASE WHEN problem


    Thanks again cytop. A user starts manually filling Category names in Col B, lookup sheet and defining the way, how category name value shall be assigned to each data record, into col H. So that 1st row on data sheet if it has ITEM = "CD36917" AND SUBGROUP like "0999*" AND .. (every populated cell on lookup grid), it gets "ABC" in col H. If that data record has different values than ABC category dictates, then macro shall go through rest of the combinations (lookup sh) until it finds that which meets the most of values the data record has.
    So I though of using CASE WHEN statement.


    It SWITCH is the alternative here, I'd need to make it something like:


    SQL
    SELECT SWITCH ([ITEM1]='CD36917' AND [SUBGROUP] LIKE '0999%', 'ABC', TRUE, 'XXX') FROM [data$]


    I just tried to connect to recordset and it has not failed yet. I'll try the next stuff later. ..

    forum.ozgrid.com/index.php?attachment/67720/


    Hi folks!



    When connecting ThisWorkbook using ADODB I get following error:


    [ATTACH=CONFIG]67719[/ATTACH]


    The Source string for recordset printed in immediate window is as follows:


    SQL
    SELECT 
    CASE WHEN [ITEM1] = "CD36917" AND [SUBGROUP] Like "0999%" AND  [LINE CODE] = "XY00000ZS2N8WV1" AND [GROUP1] Like "%555%" THEN "ABC"  WHEN [ITEM1] = "KM39352" AND [LINE CODE] = "XY00000ZS36W4" AND [GROUP1]  Like "%555%" THEN "DEF" WHEN [ITEM1] = "VW40341" AND [SUBGROUP] Like  "0999%" AND [GROUP1] Like "%555%" THEN "GHI" WHEN [LINE CODE] =  "XY00000ZS2N8WV1" AND [GROUP1] Like "%555%" THEN "JKL" WHEN [ITEM1] =  "LB789516" THEN "MNO" WHEN [LINE CODE] = "ZZ00000TV37Z6" AND [GROUP1]  Like "%555%" THEN "PQR" WHEN [CITY] = "PARIS" THEN "STU" 
    END 
    FROM  [data$]



    What macro shall do:
    1. Go through every row on data sheet.
    2. For that row, run a loop of every category combination on lookup_ref sheet
    3. Take the category which meets most of the criteria on ref sheet and paste that from Ref onto data sheet, col H.
    4. If more category rows meet the logic per particular data row (like row 4 = either MNO or STU) then take the last one (STU)
    > unfortunately, data cannot be organized different way.

    Re: Function with variable number of conditions


    Thanks xladept! And it was in front of my eyes all time!


    Anyways, I've been fascinated with ADO option that cytop offered. I tried to carry on that as an SQL newbie and got stuck at opening recordset, when the keyword "WHEN" from CASE - END is not recognized.
    A short recap:
    1. Macro should go through every row on data sheet.
    2. For that row, run a loop of every category combination on lookup_ref sheet
    3. Take the category which meets most of the criteria on ref sheet and paste that from Ref onto data sheet, col H.
    4. If more category rows meet the logic per particular data row (like row 4 = either MNO or STU) then take the last one (STU)
    > unfortunately, data cannot be organized different way.


    Therefore, can I ask you SQL geeks to help me out?
    Here's my code:


    forum.ozgrid.com/index.php?attachment/67705/

    Re: Function with variable number of conditions


    Quote from xladept;761219

    The logic fails:



    *the red will always pass the first test???


    Hi xladept, I think this line breaks the logic;



    Code
    For d = 3 To wd.Range("B" & Rows.count).End(xlUp).Row: CAT = [COLOR=#ff0000]wd.Cells(d, 8)[/COLOR]


    Sorry if my first post was not in plain English. The red categories on Data sheet were already hard-coded results. Initially they will be blank and after macro is executed, values from Lookup_ref sheet shall be returned there.
    But thanks for a solid analysis here. I will try to modify your code for variable number of columns on ref sheet. Have a nice day

    Re: Function with variable number of conditions


    Quote from mikerickson;761218

    If you want this as a function, you should specify all the arguments in the declaration statement.



    Hi mikerickson, unfortunately, the upper number of arguments can be more (or less) than 4 at every user. Haven't tried to pass a dynamic array yet. Thanks for your note.