Results 1 to 3 of 3
  1. #1
    Join Date
    Dec 2003
    Location
    Ottawa, Canada
    Posts
    72

    Unanswered: SQL in VBA for Excel

    All of us have experienced MAJOR problems trying to fit SQL sentences into a VBA piece of code.Here is how I solve the problem.

    Here is the original SQL sentence from the SQL generator:

    Select LH.location_cd , LH.location_name , LH.business_address_1 , LH.business_address_2 , LH.business_city,
    LH.business_state , LH.business_zip , LH.market_cd , LH.market_name,
    CASE LH.Product_Line_Cd WHEN 'CE' THEN 'CEM' WHEN 'FH' THEN 'FUN' END as FunCem , LH.Product_Line_Cd ,
    CASE WHEN EXISTS (select business_entity_id from dbo.TBL_COMBO_LOCATION
    where business_entity_id = BE.business_entity_id or combo_business_entity_id = BE.business_entity_id)
    THEN 'Combo' ELSE 'St-Alone' END as Combo_Cd from dbo.Location_Hierarchy_Flat as LH
    inner join tbl_business_entity as BE
    ON LH.location_cd = BE.location_number
    where LH.Status_code = 'ACTV'
    and LH.Product_Line_Cd IN ('FH','CE')
    and LH.Business_Line_Cd IN ( 'CEM', 'FUNHM', 'DCP' )
    order by LH.Business_Line_Cd, LH.Location_cd

    In this example I do not break the sentence as each line fits well into my VBA code window.

    Basically what I do is concatenate all the lines in a single variable.

    Step 1:I create partial lines of code the first line being varSQL = "" and a certain number of other lines reading varSQL = varSQL & ""

    varSQL = ""
    varSQL = varSQL & ""
    varSQL = varSQL & ""
    varSQL = varSQL & ""
    varSQL = varSQL & ""
    varSQL = varSQL & ""
    varSQL = varSQL & ""
    varSQL = varSQL & ""
    varSQL = varSQL & ""
    varSQL = varSQL & ""
    varSQL = varSQL & ""
    varSQL = varSQL & ""

    Step 2: Then I insert each line of the SQL sentence Betwwen the quotation marks

    Step 3: AND, AND, AND, AND I add a space after the opening quotation makk for all the lines starting with the second one.

    and here is the SQL sentence within a VBA variable

    varSQL = "Select LH.location_cd , LH.location_name , LH.business_address_1 , LH.business_address_2 , LH.business_city,"
    varSQL = varSQL & " LH.business_state , LH.business_zip , LH.market_cd , LH.market_name,"
    varSQL = varSQL & " CASE LH.Product_Line_Cd WHEN 'CE' THEN 'CEM' WHEN 'FH' THEN 'FUN' END as FunCem , LH.Product_Line_Cd ,"
    varSQL = varSQL & " CASE WHEN EXISTS (select business_entity_id from dbo.TBL_COMBO_LOCATION "
    varSQL = varSQL & " where business_entity_id = BE.business_entity_id or combo_business_entity_id = BE.business_entity_id)"
    varSQL = varSQL & " THEN 'Combo' ELSE 'St-Alone' END as Combo_Cd from dbo.Location_Hierarchy_Flat as LH"
    varSQL = varSQL & " inner join tbl_business_entity as BE"
    varSQL = varSQL & " ON LH.location_cd = BE.location_number"
    varSQL = varSQL & " where LH.Status_code = 'ACTV'"
    varSQL = varSQL & " and LH.Product_Line_Cd IN ('FH','CE')"
    varSQL = varSQL & " and LH.Business_Line_Cd IN ( 'CEM', 'FUNHM', 'DCP' )"
    varSQL = varSQL & " order by LH.Business_Line_Cd, LH.Location_cd"

    and I use this variable:

    .CommandText = varSql

    See more on Excel in www.excel-examples.com

    See more on VBA for Excel in www.excel-vba.com
    A piece of data is like a brick
    If you don't build anything with it
    It is just a brick
    www.excel-examples.com

  2. #2
    Join Date
    Feb 2004
    Posts
    533
    Thats a good way of doing it. For even more concise concatonation you can do the same with the line continuation "& _" then your don't have to repeat the variable name on each line. Looks like this.

    strSQL ="Select LH.location_cd , LH.location_name , LH.business_address_1 ," & _
    "LH.business_address_2 , LH.business_city, " & _
    "LH.business_state , LH.business_zip , LH.market_cd , LH.market_name," & _
    "CASE LH.Product_Line_Cd WHEN 'CE' THEN 'CEM' WHEN 'FH' THEN 'FUN' END" & _
    " as FunCem , LH.Product_Line_Cd ," & _
    "CASE WHEN EXISTS (select business_entity_id from " & _
    "dbo.TBL_COMBO_LOCATION" & _
    "where business_entity_id = BE.business_entity_id or " & _
    "combo_business_entity_id = BE.business_entity_id)"

    For some larger projects I create a table of SQL statements with an ID for each, then use a function to get the SQL from the table 'fGetSQL(sqlID)'. That eliminates having to concatonate. You can alter the SQl fairly quickly. If you embed tags for variables {strVar} then use string replace on the SQL to swap out the Tags for your variables. Which makes this technique very versital. Can do this in Access also.
    ~

    Bill

  3. #3
    Join Date
    Dec 2003
    Location
    Ottawa, Canada
    Posts
    72
    SavBill

    One thing I forgot to tell the readers is that this concatenation or continuation thing must be done outside the Visual Basic Editor because the VBA does a loosy job with SQL statements when one tries to insert them in his VBA procedure. It must be done in Notepad or Word or in a text box in excel then copied/pasted into the VBE.

    This tip I am sharing is for users who are not experts at SQL like me. I get Access, Microsoft Query or a good friend of mine to write the statements and I use them in VBA.

    Although I also use dynamic values in my queries the second part of your message in out of my league.
    A piece of data is like a brick
    If you don't build anything with it
    It is just a brick
    www.excel-examples.com

Posting Permissions

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