locked
VBA Macro Fails After Upgrade to Excel 2016 RRS feed

  • Question

  • I have a macros that works in 2013 but will not run in 2016.  Unfortunately, Office 365 support considers this a "usability" issue and won't support the problem as part of the subscription to the service.  Web searches have shown others having problems -- but no solutions.  Any thoughts from the community?

    Trying to add screen shots, but I can't until I verify my account.  No obvious way to verify my account. It's been a Microsoft kind of day.

    Tuesday, February 23, 2016 8:02 PM

All replies

  • Hi Deuce Sapp,

    Did you mean there was a VBA code cannot be used in Excel 2016 but worked fine in Excel 2013?

    Could you provide the Code about your problem?

    Then you can send the screenshot to our email address:

    ibsofc@microsoft.com

    Please Note: Please add the URL of the case in the email subject or body.

    I'm glad to help and follow up your reply.


    Regards,

    Emi Zhang
    TechNet Community Support


    Please mark the reply as an answer if you find it is helpful.

    If you have feedback for TechNet Support, contact tnmff@microsoft.com.

    Wednesday, February 24, 2016 7:10 AM
  • Thanks for the quick response Emi. Yes, you are correct in your summary of the issue. I will send an email with the errors and code. 
    Wednesday, February 24, 2016 1:04 PM
  • Hi Deuce Sapp,

    Based on your email, I will move your problem to MSDN forum for Excel:

    https://social.msdn.microsoft.com/Forums/en-US/home?forum=exceldev

    The reason why we recommend posting appropriately is you will get the most qualified pool of respondents, and other partners who read the forums regularly can either share their knowledge or learn from your interaction with us. Thank you for your understanding.


    Regards,

    Emi Zhang
    TechNet Community Support


    Please mark the reply as an answer if you find it is helpful.

    If you have feedback for TechNet Support, contact tnmff@microsoft.com.

    Thursday, February 25, 2016 1:36 AM
  • Hi MSDN partner,

    This is the problem VBA code about this user, thanks for helping him to resolve this problem:

    Sub DataRefresh()
    Range("A4").Select
        ActiveCell.FormulaR1C1 = "...Please Wait While Data Loads..."
        ActiveCell.Interior.Color = RGB(255, 255, 0)
    If Worksheets("Params").Range("G1").Value <> Worksheets("Params").Range("G2").Value Then
        ActiveCell.Interior.Color = RGB(255, 0, 0)
        ActiveCell.FormulaR1C1 = "...Dates Must Be In Same Month..."
    Else
        
    Dim sql As String
    Dim sql2 As String
    Dim sql3 As String
    Dim sql4 As String
    sql = "DECLARE  " & vbCrLf
    sql = sql & "    @dFrom AS Date, " & vbCrLf
    sql = sql & "    @dTo AS Date " & vbCrLf
    sql = sql & " SET @dFrom='?1' " & vbCrLf
    sql = sql & " SET @dTo='?2' " & vbCrLf
    sql = sql & " SELECT " & vbCrLf
    sql = sql & " [Name], '1' as Placeholder, " & vbCrLf
    sql = sql & " sum(DebitAmount) as DebitAmount, " & vbCrLf
    sql = sql & " sum(CreditAmount) as CreditAmount " & vbCrLf
    sql = sql & " FROM " & vbCrLf
    sql = sql & " (SELECT  " & vbCrLf
    sql = sql & " case  " & vbCrLf
    sql = sql & "   when GL IN ('1-03-01-00-3010-A','1-03-01-00-3011-A','1-03-01-00-3012-A','1-03-01-00-3013-A','1-03-01-00-3014-A','1-03-01-00-3015-A','1-03-01-00-3016-A','1-03-01-00-3017-A','1-03-01-00-3018-A','1-03-01-00-3019-A','1-03-01-00-3020-A','1-03-01-00-3021-A','1-03-01-00-3050-A','1-03-01-00-3051-A','1-03-01-00-3052-A','1-03-01-00-3053-A','1-03-01-00-3054-A','1-03-01-00-3060-A','1-03-01-00-3061-A','1-03-01-00-3062-A','1-03-01-00-3063-A','1-03-01-00-3064-A','1-03-01-00-3070-A','1-03-01-00-3071-A','1-03-01-00-3072-A','1-03-01-00-3073-A','1-03-01-00-3074-A','1-03-01-00-3080-A','1-03-01-00-3081-A','1-03-01-00-3082-A','1-03-01-00-3083-A','1-03-01-00-3084-A','1-03-01-00-3160-A','1-03-01-00-3161-A','1-03-01-00-3162-A','1-03-01-00-3163-A','1-03-01-00-3164-A','1-03-01-00-3180-A','1-03-01-00-3181-A','1-03-01-00-3182-A','1-03-01-00-3183-A','1-03-01-00-3184-A','1-03-01-00-3612-A','1-03-01-00-3910-A','1-03-01-00-3912-A','1-03-01-00-3916-A') " & vbCrLf
    sql = sql & "   THEN 'RSWPREV'  " & vbCrLf
    sql = sql & "   WHEN GL IN ('1-03-01-00-9701-U','1-03-01-00-9706-U') " & vbCrLf
    sql = sql & "   THEN 'RSWPOR' " & vbCrLf
    sql = sql & "   WHEN GL IN ('1-03-01-00-9700-U','1-03-01-00-9705-U') " & vbCrLf
    sql = sql & "   THEN 'RSWPAR' " & vbCrLf
    sql = sql & "   when GL IN ('1-03-02-13-3210-U','1-03-02-13-3211-U','1-03-02-16-3211-U') " & vbCrLf
    sql = sql & "   THEN 'FBRIVUECUST'  " & vbCrLf
    sql = sql & "   when GL IN ('1-03-02-13-3210-A','1-03-02-13-3211-A','1-03-02-13-3212-A','1-03-02-13-3220-A','1-03-02-13-3221-A','1-03-02-13-3222-A','1-03-02-13-3920-A','1-03-02-13-3921-A','1-03-02-16-3210-A','1-03-02-16-3211-A','1-03-02-16-3220-A','1-03-02-16-3221-A','1-03-02-16-3222-A','1-03-02-16-3920-A','1-03-02-16-3921-A') " & vbCrLf
    sql = sql & "   THEN 'FBRIVUEREV' " & vbCrLf
    sql = sql & "   when GL IN ('1-03-02-14-3212-U') " & vbCrLf
    sql = sql & "   THEN 'FBMTOECUST' " & vbCrLf
    sql = sql & "   when GL IN ('1-03-02-14-3210-A','1-03-02-14-3211-A','1-03-02-14-3212-A','1-03-02-14-3822-A','1-03-02-14-3824-A','1-03-02-14-3920-A') " & vbCrLf
    sql = sql & "   THEN 'FBMTOEREV'  " & vbCrLf
    sql = sql & "   when GL IN ('1-03-02-15-3210-U','1-03-02-15-3212-U','1-03-02-15-3211-U') " & vbCrLf
    sql = sql & "   THEN 'FBCMAGCUST' " & vbCrLf
    sql = sql & "   when GL IN ('1-03-02-15-3210-A','1-03-02-15-3211-A','1-03-02-15-3212-A','1-03-02-15-3220-A','1-03-02-15-3221-A','1-03-02-15-3222-A','1-03-02-15-3920-A','1-03-02-15-3921-A') " & vbCrLf
    sql = sql & "   THEN 'FBCMAGREV'  " & vbCrLf
    sql = sql & "   when GL IN ('1-03-02-17-3211-U') " & vbCrLf
    sql = sql & "   THEN 'FBJSILKSCUST' " & vbCrLf
    sql = sql & "   when GL IN ('1-03-02-17-3210-A','1-03-02-17-3211-A','1-03-02-17-3220-A','1-03-02-17-3221-A','1-03-02-17-3222-A','1-03-02-17-3920-A','1-03-02-17-3921-A') " & vbCrLf
    sql = sql & "   THEN 'FBJSILKSREV' " & vbCrLf
    sql = sql & "   when GL IN ('1-03-02-21-3212-U') " & vbCrLf
    sql = sql & "   THEN 'FBWSIDECUST' " & vbCrLf
    sql = sql & "   when GL IN ('1-03-02-21-3211-A','1-03-02-21-3212-A','1-03-02-21-3220-A','1-03-02-21-3221-A','1-03-02-21-3222-A','1-03-02-21-3920-A','1-03-02-21-3921-A') " & vbCrLf
    sql = sql & "   THEN 'FBWSIDEREV'  " & vbCrLf
    sql = sql & "   when GL IN ('1-03-02-23-3215-U','1-03-02-23-3217-U','1-03-02-23-3216-U') " & vbCrLf
    sql = sql & "   THEN 'FBTHELMACUST' " & vbCrLf
    sql = sql & "   WHEN GL IN ('1-03-02-23-3215-A','1-03-02-23-3216-A','1-03-02-23-3217-A','1-03-02-23-3820-A','1-03-02-23-3822-A','1-03-02-23-3921-A','1-03-02-23-3922-A') THEN 'FBTHELMAREV' " & vbCrLf
    sql = sql & "   WHEN GL IN ('1-03-02-24-3216-U') THEN 'FBALJCUST' " & vbCrLf
    sql = sql & "   WHEN GL IN ('1-03-02-24-3216-A','1-03-02-24-3217-A','1-03-02-24-3223-A','1-03-02-24-3224-A','1-03-02-24-3225-A','1-03-02-24-3922-A','1-03-02-24-3923-A') THEN 'FBALJREV' " & vbCrLf
    sql = sql & "   WHEN GL IN ('0-00-00-00-0000-U') THEN 'FBSPIRITSCUST' " & vbCrLf
    sql = sql & "   WHEN GL IN ('1-03-02-25-3220-A','1-03-02-25-3221-A','1-03-02-25-3230-A','1-03-02-25-3233-A'         ) THEN 'FBSPIRITSREV' " & vbCrLf
    sql = sql & "   WHEN GL IN ('1-03-02-11-3210-U','1-03-02-11-3211-U','1-03-02-11-3212-U','1-03-02-11-3213-U','1-03-02-11-3214-U','1-03-02-11-3215-U','1-03-02-11-3216-U','1-03-02-11-3217-U','1-03-02-11-3218-U','1-03-02-11-3219-U','1-03-02-18-3210-U','1-03-02-18-3211-U','1-03-02-18-3212-U','1-03-02-18-3213-U','1-03-02-18-3214-U','1-03-02-18-3215-U','1-03-02-18-3216-U','1-03-02-18-3217-U','1-03-02-18-3218-U','1-03-02-18-3219-U') THEN 'FBBCCCUST' " & vbCrLf
    sql = sql & "   WHEN GL IN ('1-03-02-11-3210-A','1-03-02-11-3211-A','1-03-02-11-3212-A','1-03-02-11-3213-A','1-03-02-11-3214-A','1-03-02-11-3215-A','1-03-02-11-3216-A','1-03-02-11-3217-A','1-03-02-11-3218-A','1-03-02-11-3219-A','1-03-02-11-3220-A','1-03-02-11-3221-A','1-03-02-11-3222-A','1-03-02-11-3223-A','1-03-02-11-3224-A','1-03-02-11-3225-A','1-03-02-11-3270-A','1-03-02-18-3210-A','1-03-02-18-3211-A','1-03-02-18-3212-A','1-03-02-18-3213-A','1-03-02-18-3214-A','1-03-02-18-3215-A','1-03-02-18-3216-A','1-03-02-18-3217-A','1-03-02-18-3218-A','1-03-02-18-3219-A','1-03-02-18-3220-A','1-03-02-18-3221-A','1-03-02-18-3222-A','1-03-02-18-3223-A','1-03-02-18-3224-A','1-03-02-18-3225-A','1-03-02-18-3270-A') THEN 'FBBCCREV' " & vbCrLf
    sql = sql & "   WHEN GL IN ('1-03-02-12-3210-U','1-03-02-12-3211-U','1-03-02-12-3212-U','1-03-02-12-3215-U','1-03-02-12-3216-U','1-03-02-12-3217-U') THEN 'FBINROOMCUST' " & vbCrLf
    sql = sql & "   WHEN GL IN ('1-03-02-12-3210-A','1-03-02-12-3211-A','1-03-02-12-3212-A','1-03-02-12-3215-A','1-03-02-12-3216-A','1-03-02-12-3217-A','1-03-02-12-3220-A','1-03-02-12-3221-A','1-03-02-12-3222-A','1-03-02-12-3223-A','1-03-02-12-3224-A','1-03-02-12-3925-A','1-03-02-12-3920-A','1-03-02-12-3921-A','1-03-02-12-3922-A','1-03-02-12-3923-A') THEN 'FBINROOMREV' " & vbCrLf
    sql = sql & "   WHEN GL IN ('1-03-03-44-3410-A','1-03-03-44-3411-A','1-03-03-44-3412-A','1-03-03-44-3414-A','1-03-03-44-3415-A','1-03-03-44-3416-A','1-03-03-44-3420-A','1-03-03-44-3421-A','1-03-03-44-3425-A') THEN 'FITNESSREV' " & vbCrLf
    sql = sql & "   WHEN GL IN ('1-03-03-46-3820-A','1-03-03-46-3910-A') THEN 'CHRISTMASREV' " & vbCrLf
    sql = sql & "   WHEN GL IN ('1-03-03-46-9604-U') THEN 'CHRISTMASCUST' " & vbCrLf
    sql = sql & " end as [Name], " & vbCrLf
    sql = sql & " DebitAmount, " & vbCrLf
    sql = sql & " CreditAmount " & vbCrLf
    sql = sql & " FROM( " & vbCrLf
    sql = sql & " SELECT   " & vbCrLf
    sql = sql & "       [PostDate], " & vbCrLf
    sql = sql & "   (ac.AuxiliarySegment7Code+ '-' + ac.AuxiliarySegment1Code  +'-'+ ac.AuxiliarySegment8Code  +'-'+    ac.AuxiliarySegment9Code +'-'+ ac.PrimarySegmentCode+'-'+ac.LedgerSegmentCode) as GL, " & vbCrLf
    sql = sql & "       [DebitAmount], " & vbCrLf
    sql = sql & "       [CreditAmount] " & vbCrLf
    sql = sql & "   FROM [ActivityInns].[dbo].[JournalDetail] jd " & vbCrLf
    sql = sql & "   LEFT JOIN [ActivityInns].[dbo].[Account] a on jd.Account=a.Account " & vbCrLf
    sql = sql & "   LEFT JOIN [ActivityInns].[dbo].[AccountCode] ac on a.Account=ac.Account " & vbCrLf
    sql = sql & "   LEFT JOIN [ActivityInns].[dbo].[PrimarySegmentItem] ps on a.PrimarySegmentItem=ps.PrimarySegmentItem " & vbCrLf
    sql = sql & "   LEFT JOIN [ActivityInns].[dbo].[LedgerSegmentItem] ls on a.LedgerSegmentItem=ls.LedgerSegmentItem " & vbCrLf
    sql = sql & "   where 1=1 " & vbCrLf
    sql = sql & "   AND PostDate between @dFrom and @dTo " & vbCrLf
    sql = sql & "   AND [HoldEntry]='N' " & vbCrLf
    sql = sql & " ) AS Q1 " & vbCrLf
    sql = sql & " UNION ALL " & vbCrLf
    sql = sql & " SELECT 'RSWPREV' AS Name, 0 as debit, 0 as credit " & vbCrLf
    sql = sql & " UNION ALL  " & vbCrLf
    sql = sql & " SELECT 'RSWPOR' AS Name, 0 as debit, 0 as credit " & vbCrLf
    sql = sql & " UNION ALL  " & vbCrLf
    sql = sql & " SELECT 'RSWPAR' AS Name, 0 as debit, 0 as credit " & vbCrLf
    sql = sql & " UNION ALL  " & vbCrLf
    sql = sql & " SELECT 'FBRIVUECUST' AS Name, 0 as debit, 0 as credit " & vbCrLf
    sql = sql & " UNION ALL  " & vbCrLf
    sql = sql & " SELECT 'FBRIVUEREV' AS Name, 0 as debit, 0 as credit " & vbCrLf
    sql = sql & " UNION ALL  " & vbCrLf
    sql = sql & " SELECT 'FBMTOECUST' AS Name, 0 as debit, 0 as credit " & vbCrLf
    sql = sql & " UNION ALL  " & vbCrLf
    sql = sql & " SELECT 'FBMTOEREV' AS Name, 0 as debit, 0 as credit " & vbCrLf
    sql = sql & " UNION ALL  " & vbCrLf
    sql = sql & " SELECT 'FBCMAGCUST' AS Name, 0 as debit, 0 as credit " & vbCrLf
    sql = sql & " UNION ALL  " & vbCrLf
    sql = sql & " SELECT 'FBCMAGREV' AS Name, 0 as debit, 0 as credit " & vbCrLf
    sql = sql & " UNION ALL  " & vbCrLf
    sql = sql & " SELECT 'FBJSILKSCUST' AS Name, 0 as debit, 0 as credit " & vbCrLf
    sql = sql & " UNION ALL  " & vbCrLf
    sql = sql & " SELECT 'FBJSILKSREV' AS Name, 0 as debit, 0 as credit " & vbCrLf
    sql = sql & " UNION ALL  " & vbCrLf
    sql = sql & " SELECT 'FBWSIDEREV' AS Name, 0 as debit, 0 as credit " & vbCrLf
    sql = sql & " UNION ALL  " & vbCrLf
    sql = sql & " SELECT 'FBTHELMACUST' AS Name, 0 as debit, 0 as credit " & vbCrLf
    sql = sql & " UNION ALL  " & vbCrLf
    sql = sql & " SELECT 'FBTHELMAREV' AS Name, 0 as debit, 0 as credit " & vbCrLf
    sql = sql & " UNION ALL  " & vbCrLf
    sql = sql & " SELECT 'FBALJCUST' AS Name, 0 as debit, 0 as credit " & vbCrLf
    sql = sql & " UNION ALL  " & vbCrLf
    sql = sql & " SELECT 'FBALJREV' AS Name, 0 as debit, 0 as credit " & vbCrLf
    sql = sql & " UNION ALL  " & vbCrLf
    sql = sql & " SELECT 'FBSPIRITSCUST' AS Name, 0 as debit, 0 as credit " & vbCrLf
    sql = sql & " UNION ALL  " & vbCrLf
    sql = sql & " SELECT 'FBSPIRITSREV' AS Name, 0 as debit, 0 as credit " & vbCrLf
    sql = sql & " UNION ALL  " & vbCrLf
    sql = sql & " SELECT 'FBBCCCUST' AS Name, 0 as debit, 0 as credit " & vbCrLf
    sql = sql & " UNION ALL  " & vbCrLf
    sql = sql & " SELECT 'FBBCCREV' AS Name, 0 as debit, 0 as credit " & vbCrLf
    sql = sql & " UNION ALL  " & vbCrLf
    sql = sql & " SELECT 'FBINROOMCUST' AS Name, 0 as debit, 0 as credit " & vbCrLf
    sql = sql & " UNION ALL  " & vbCrLf
    sql = sql & " SELECT 'FBINROOMREV' AS Name, 0 as debit, 0 as credit " & vbCrLf
    sql = sql & " UNION ALL  " & vbCrLf
    sql = sql & " SELECT 'FITNESSREV' AS Name, 0 as debit, 0 as credit " & vbCrLf
    sql = sql & " UNION ALL  " & vbCrLf
    sql = sql & " SELECT 'CHRISTMASREV' AS Name, 0 as debit, 0 as credit " & vbCrLf
    sql = sql & " UNION ALL  " & vbCrLf
    sql = sql & " SELECT 'CHRISTMASCUST' AS Name, 0 as debit, 0 as credit " & vbCrLf
    sql = sql & " UNION ALL  " & vbCrLf
    sql = sql & " SELECT 'FBWSIDECUST' AS Name, 0 as debit, 0 as credit " & vbCrLf
    sql = sql & " ) AS Q2 " & vbCrLf
    sql = sql & " Where [name] is not null " & vbCrLf
    sql = sql & " GROUP BY [Name] " & vbCrLf
    
    Range("A4").Select
        ActiveCell.FormulaR1C1 = "...Loading GL Data..."
        ActiveCell.Interior.Color = RGB(255, 255, 0)
    sql = Replace(sql, "?1", Worksheets("Params").Range("B1").Value)
    sql = Replace(sql, "?2", Worksheets("Params").Range("B2").Value)
    With ActiveWorkbook.Connections("Activity.DB").OLEDBConnection
    .CommandText = sql
    .Refresh
    End With
    
    sql2 = " DECLARE  " & vbCrLf
    sql2 = sql2 & "      @dFrom AS Datetime, " & vbCrLf
    sql2 = sql2 & "    @dTo AS Datetime " & vbCrLf
    sql2 = sql2 & " SET @dFrom='?1' " & vbCrLf
    sql2 = sql2 & " SET @dTo='?2' " & vbCrLf
    sql2 = sql2 & " SELECT  " & vbCrLf
    sql2 = sql2 & "    (rtrim(g.groupname)) as groupname, " & vbCrLf
    sql2 = sql2 & "    sum([Total]) as [Hours], " & vbCrLf
    sql2 = sql2 & "    sum([Amount]) as Amount " & vbCrLf
    sql2 = sql2 & " FROM  " & vbCrLf
    sql2 = sql2 & "     (   SELECT dept, 0 as exempt, 0 as Total, 0 as Amount " & vbCrLf
    sql2 = sql2 & "         FROM [AJSReporting].[dbo].[ADP_Consolidated_Group_Dept]  " & vbCrLf
    sql2 = sql2 & "      UNION ALL  " & vbCrLf
    sql2 = sql2 & "         SELECT dept, 1 as exempt, 0 as Total, 0 as Amount " & vbCrLf
    sql2 = sql2 & "         FROM [AJSReporting].[dbo].[ADP_Consolidated_Group_Dept]  " & vbCrLf
    sql2 = sql2 & "      UNION ALL  " & vbCrLf
    sql2 = sql2 & "         SELECT dept, case when e.[type]='Salaried' then 1 else 0 end as exempt, Total, Amount" & vbCrLf
    sql2 = sql2 & "         FROM [AJSReporting].[dbo].[ADP_TimeCard] tc" & vbCrLf
    sql2 = sql2 & "         LEFT JOIN [AJSReporting].[dbo].[ADP_Employee] e on LTRIM(tc.Employee)=e.empnum  " & vbCrLf
    sql2 = sql2 & "         where workdate BETWEEN @dFrom AND @dTo " & vbCrLf
    sql2 = sql2 & "      ) as tc " & vbCrLf
    sql2 = sql2 & " LEFT JOIN  [AJSReporting].[dbo].[ADP_Consolidated_Group_Dept] g " & vbCrLf
    sql2 = sql2 & "     ON tc.[Dept]=g.[dept] and tc.exempt=g.exempt " & vbCrLf
    sql2 = sql2 & " where 1=1 " & vbCrLf
    sql2 = sql2 & " GROUP BY g.groupname "
    
    Range("A4").Select
        ActiveCell.FormulaR1C1 = "...Loading Labor Data..."
        ActiveCell.Interior.Color = RGB(255, 255, 0)
    sql2 = Replace(sql2, "?1", Worksheets("Params").Range("B1").Value)
    sql2 = Replace(sql2, "?2", Worksheets("Params").Range("B2").Value)
    With ActiveWorkbook.Connections("ADP.DB").OLEDBConnection
    .CommandText = sql2
    .Refresh
    End With
    
    sql3 = " DECLARE   " & vbCrLf
    sql3 = sql3 & "  @dFrom AS Date,  " & vbCrLf
    sql3 = sql3 & "  @dTo AS Date,  " & vbCrLf
    sql3 = sql3 & "  @dMonthStart AS Date,  " & vbCrLf
    sql3 = sql3 & "  @dMonthEnd AS Date,  " & vbCrLf
    sql3 = sql3 & "  @dDays as Int  " & vbCrLf
    sql3 = sql3 & "SET @dFrom='?1'  " & vbCrLf
    sql3 = sql3 & "SET @dTo='?2'  " & vbCrLf
    sql3 = sql3 & "SET @dMonthStart=DATEADD(month, DATEDIFF(month, 0, @dFrom), 0)  " & vbCrLf
    sql3 = sql3 & "SET @dMonthEnd=DATEADD(d, -1, DATEADD(m, DATEDIFF(m, 0, @dTo) + 1, 0))  " & vbCrLf
    sql3 = sql3 & "SET @dDays=DATEDIFF(""D"",@dMonthStart,@dMonthEnd)+1  " & vbCrLf
    sql3 = sql3 & "SELECT  " & vbCrLf
    sql3 = sql3 & "GroupName, '1' as Placeholder,   " & vbCrLf
    sql3 = sql3 & "sum(DebitAmount)/@dDays as DailyDebitAmount,  " & vbCrLf
    sql3 = sql3 & "sum(CreditAmount)/@dDays as DailyCreditAmount  " & vbCrLf
    sql3 = sql3 & "FROM(  " & vbCrLf
    sql3 = sql3 & "SELECT    " & vbCrLf
    sql3 = sql3 & "      [PostDate],  " & vbCrLf
    sql3 = sql3 & " (ac.AuxiliarySegment7Code+ '-' + ac.AuxiliarySegment1Code  +'-'+ ac.AuxiliarySegment8Code  +'-'+    ac.AuxiliarySegment9Code +'-'+ ac.PrimarySegmentCode+'-'+ac.LedgerSegmentCode) as GL,  " & vbCrLf
    sql3 = sql3 & "      [DebitAmount],  " & vbCrLf
    sql3 = sql3 & "      [CreditAmount]  " & vbCrLf
    sql3 = sql3 & "  FROM [ActivityInns].[dbo].[JournalDetail] jd  " & vbCrLf
    sql3 = sql3 & " LEFT JOIN [ActivityInns].[dbo].[Account] a on jd.Account=a.Account  " & vbCrLf
    sql3 = sql3 & " LEFT JOIN [ActivityInns].[dbo].[AccountCode] ac on a.Account=ac.Account  " & vbCrLf
    sql3 = sql3 & " LEFT JOIN [ActivityInns].[dbo].[PrimarySegmentItem] ps on a.PrimarySegmentItem=ps.PrimarySegmentItem  " & vbCrLf
    sql3 = sql3 & " LEFT JOIN [ActivityInns].[dbo].[LedgerSegmentItem] ls on a.LedgerSegmentItem=ls.LedgerSegmentItem  " & vbCrLf
    sql3 = sql3 & "  where 1=1  " & vbCrLf
    sql3 = sql3 & " AND PostDate BETWEEN @dMonthStart and @dMonthEnd  " & vbCrLf
    sql3 = sql3 & " AND [HoldEntry]='N'  " & vbCrLf
    sql3 = sql3 & ") AS Q1  " & vbCrLf
    sql3 = sql3 & "RIGHT JOIN  " & vbCrLf
    sql3 = sql3 & "   [AJS_Custom].[dbo].[Budget_Map] b on Q1.GL=b.Code  " & vbCrLf
    sql3 = sql3 & "   GROUP BY GroupName "
    
    Range("A4").Select
        ActiveCell.FormulaR1C1 = "...Loading Labor Budget..."
        ActiveCell.Interior.Color = RGB(255, 255, 0)
    sql3 = Replace(sql3, "?1", Worksheets("Params").Range("B1").Value)
    sql3 = Replace(sql3, "?2", Worksheets("Params").Range("B2").Value)
    With ActiveWorkbook.Connections("Budget").OLEDBConnection
    .CommandText = sql3
    .Refresh
    End With
    
    sql4 = " DECLARE   " & vbCrLf
    sql4 = sql4 & "  @dFrom AS Date,  " & vbCrLf
    sql4 = sql4 & "  @dTo AS Date,  " & vbCrLf
    sql4 = sql4 & "  @dMonthStart AS Date,  " & vbCrLf
    sql4 = sql4 & "  @dMonthEnd AS Date,  " & vbCrLf
    sql4 = sql4 & "  @dDays as Int  " & vbCrLf
    sql4 = sql4 & "SET @dFrom='?1'  " & vbCrLf
    sql4 = sql4 & "SET @dTo='?2'  " & vbCrLf
    sql4 = sql4 & "SET @dMonthStart=DATEADD(month, DATEDIFF(month, 0, @dFrom), 0)  " & vbCrLf
    sql4 = sql4 & "SET @dMonthEnd=DATEADD(d, -1, DATEADD(m, DATEDIFF(m, 0, @dTo) + 1, 0))  " & vbCrLf
    sql4 = sql4 & "SET @dDays=DATEDIFF(""D"",@dMonthStart,@dMonthEnd)+1  " & vbCrLf
    sql4 = sql4 & "SELECT  " & vbCrLf
    sql4 = sql4 & "[Name],'1' as Placeholder,  " & vbCrLf
    sql4 = sql4 & "sum(DebitAmount)/@dDays as DailyDebitAmount,  " & vbCrLf
    sql4 = sql4 & "sum(CreditAmount)/@dDays as DailyCreditAmount  " & vbCrLf
    sql4 = sql4 & "FROM  " & vbCrLf
    sql4 = sql4 & "(SELECT   " & vbCrLf
    sql4 = sql4 & " case   " & vbCrLf
    sql4 = sql4 & "     when GL IN ('1-03-01-00-3010-B','1-03-01-00-3011-B','1-03-01-00-3012-B','1-03-01-00-3013-B','1-03-01-00-3014-B','1-03-01-00-3015-B','1-03-01-00-3016-B','1-03-01-00-3017-B','1-03-01-00-3018-B','1-03-01-00-3019-B','1-03-01-00-3020-B','1-03-01-00-3021-B','1-03-01-00-3050-B','1-03-01-00-3051-B','1-03-01-00-3052-B','1-03-01-00-3053-B','1-03-01-00-3054-B','1-03-01-00-3060-B','1-03-01-00-3061-B','1-03-01-00-3062-B','1-03-01-00-3063-B','1-03-01-00-3064-B','1-03-01-00-3070-B','1-03-01-00-3071-B','1-03-01-00-3072-B','1-03-01-00-3073-B','1-03-01-00-3074-B','1-03-01-00-3080-B','1-03-01-00-3081-B','1-03-01-00-3082-B','1-03-01-00-3083-B','1-03-01-00-3084-B','1-03-01-00-3160-B','1-03-01-00-3161-B','1-03-01-00-3162-B','1-03-01-00-3163-B','1-03-01-00-3164-B','1-03-01-00-3180-B','1-03-01-00-3181-B','1-03-01-00-3182-B','1-03-01-00-3183-B','1-03-01-00-3184-B','1-03-01-00-3612-B','1-03-01-00-3910-B','1-03-01-00-3912-B','1-03-01-00-3916-B') THEN 'RSWPREV'   " & vbCrLf
    sql4 = sql4 & "     WHEN GL IN ('1-03-01-00-9701-V','1-03-01-00-9706-V') THEN 'RSWPOR'  " & vbCrLf
    sql4 = sql4 & "     WHEN GL IN ('1-03-01-00-9700-V','1-03-01-00-9705-V') THEN 'RSWPAR'  " & vbCrLf
    sql4 = sql4 & "     when GL IN ('1-03-02-13-3210-V','1-03-02-13-3211-V','1-03-02-16-3211-V') THEN 'FBRIVUECUST'   " & vbCrLf
    sql4 = sql4 & "     when GL IN ('1-03-02-13-3210-B','1-03-02-13-3211-B','1-03-02-13-3212-B','1-03-02-13-3220-B','1-03-02-13-3221-B','1-03-02-13-3222-B','1-03-02-13-3920-B','1-03-02-13-3921-B','1-03-02-16-3210-B','1-03-02-16-3211-B','1-03-02-16-3220-B','1-03-02-16-3221-B','1-03-02-16-3222-B','1-03-02-16-3920-B','1-03-02-16-3921-B') THEN 'FBRIVUEREV'  " & vbCrLf
    sql4 = sql4 & "     when GL IN ('1-03-02-14-3212-V') THEN 'FBMTOECUST'  " & vbCrLf
    sql4 = sql4 & "     when GL IN ('1-03-02-14-3210-B','1-03-02-14-3211-B','1-03-02-14-3212-B','1-03-02-14-3822-B','1-03-02-14-3824-B','1-03-02-14-3920-B')  THEN 'FBMTOEREV'   " & vbCrLf
    sql4 = sql4 & "     when GL IN ('1-03-02-15-3210-V','1-03-02-15-3212-V','1-03-02-15-3211-V') THEN 'FBCMAGCUST'  " & vbCrLf
    sql4 = sql4 & "     when GL IN ('1-03-02-15-3210-B','1-03-02-15-3211-B','1-03-02-15-3212-B','1-03-02-15-3220-B','1-03-02-15-3221-B','1-03-02-15-3222-B','1-03-02-15-3920-B','1-03-02-15-3921-B') THEN 'FBCMAGREV'   " & vbCrLf
    sql4 = sql4 & "     when GL IN ('1-03-02-17-3211-V') THEN 'FBJSILKSCUST'  " & vbCrLf
    sql4 = sql4 & "     when GL IN ('1-03-02-17-3210-B','1-03-02-17-3211-B','1-03-02-17-3220-B','1-03-02-17-3221-B','1-03-02-17-3222-B','1-03-02-17-3920-B','1-03-02-17-3921-B') THEN 'FBJSILKSREV'  " & vbCrLf
    sql4 = sql4 & "     when GL IN ('1-03-02-21-3212-V') THEN 'FBWSIDECUST'  " & vbCrLf
    sql4 = sql4 & "     when GL IN ('1-03-02-21-3211-B','1-03-02-21-3212-B','1-03-02-21-3220-B','1-03-02-21-3221-B','1-03-02-21-3222-B','1-03-02-21-3920-B','1-03-02-21-3921-B') THEN 'FBWSIDEREV'   " & vbCrLf
    sql4 = sql4 & "     when GL IN ('1-03-02-21-3212-V') THEN 'FBWSIDECUST'   " & vbCrLf
    sql4 = sql4 & "     when GL IN ('1-03-02-23-3215-V','1-03-02-23-3217-V','1-03-02-23-3216-V') THEN 'FBTHELMACUST'  " & vbCrLf
    sql4 = sql4 & "     WHEN GL IN ('1-03-02-23-3215-B','1-03-02-23-3216-B','1-03-02-23-3217-B','1-03-02-23-3820-B','1-03-02-23-3822-B','1-03-02-23-3921-B','1-03-02-23-3922-B') THEN 'FBTHELMAREV'  " & vbCrLf
    sql4 = sql4 & "     WHEN GL IN ('1-03-02-24-3216-V') THEN 'FBALJCUST'  " & vbCrLf
    sql4 = sql4 & "     WHEN GL IN ('1-03-02-24-3216-B','1-03-02-24-3217-B','1-03-02-24-3223-B','1-03-02-24-3224-B','1-03-02-24-3225-B','1-03-02-24-3922-B','1-03-02-24-3923-B') THEN 'FBALJREV'  " & vbCrLf
    sql4 = sql4 & "     WHEN GL IN ('0-00-00-00-0000-V') THEN 'FBSPIRITSCUST'  " & vbCrLf
    sql4 = sql4 & "     WHEN GL IN ('1-03-02-25-3220-B','1-03-02-25-3221-B','1-03-02-25-3230-B','1-03-02-25-3233-B'         ) THEN 'FBSPIRITSREV'  " & vbCrLf
    sql4 = sql4 & "     WHEN GL IN ('1-03-02-11-3210-V','1-03-02-11-3211-V','1-03-02-11-3212-V','1-03-02-11-3213-V','1-03-02-11-3214-V','1-03-02-11-3215-V','1-03-02-11-3216-V','1-03-02-11-3217-V','1-03-02-11-3218-V','1-03-02-11-3219-V','1-03-02-18-3210-V','1-03-02-18-3211-V','1-03-02-18-3212-V','1-03-02-18-3213-V','1-03-02-18-3214-V','1-03-02-18-3215-V','1-03-02-18-3216-V','1-03-02-18-3217-V','1-03-02-18-3218-V','1-03-02-18-3219-V') THEN 'FBBCCCUST'  " & vbCrLf
    sql4 = sql4 & "     WHEN GL IN ('1-03-02-11-3210-B','1-03-02-11-3211-B','1-03-02-11-3212-B','1-03-02-11-3213-B','1-03-02-11-3214-B','1-03-02-11-3215-B','1-03-02-11-3216-B','1-03-02-11-3217-B','1-03-02-11-3218-B','1-03-02-11-3219-B','1-03-02-11-3220-B','1-03-02-11-3221-B','1-03-02-11-3222-B','1-03-02-11-3223-B','1-03-02-11-3224-B','1-03-02-11-3225-B','1-03-02-11-3270-B','1-03-02-18-3210-B','1-03-02-18-3211-B','1-03-02-18-3212-B','1-03-02-18-3213-B','1-03-02-18-3214-B','1-03-02-18-3215-B','1-03-02-18-3216-B','1-03-02-18-3217-B','1-03-02-18-3218-B','1-03-02-18-3219-B','1-03-02-18-3220-B','1-03-02-18-3221-B','1-03-02-18-3222-B','1-03-02-18-3223-B','1-03-02-18-3224-B','1-03-02-18-3225-B','1-03-02-18-3270-B') THEN 'FBBCCREV'  " & vbCrLf
    sql4 = sql4 & "     WHEN GL IN ('1-03-02-12-3210-V','1-03-02-12-3211-V','1-03-02-12-3212-V','1-03-02-12-3215-V','1-03-02-12-3216-V','1-03-02-12-3217-V') THEN 'FBINROOMCUST'  " & vbCrLf
    sql4 = sql4 & "     WHEN GL IN ('1-03-02-12-3210-B','1-03-02-12-3211-B','1-03-02-12-3212-B','1-03-02-12-3215-B','1-03-02-12-3216-B','1-03-02-12-3217-B','1-03-02-12-3220-B','1-03-02-12-3221-B','1-03-02-12-3222-B','1-03-02-12-3223-B','1-03-02-12-3224-B','1-03-02-12-3925-B','1-03-02-12-3920-B','1-03-02-12-3921-B','1-03-02-12-3922-B','1-03-02-12-3923-B') THEN 'FBINROOMREV'  " & vbCrLf
    sql4 = sql4 & "     WHEN GL IN ('1-03-03-44-3410-B','1-03-03-44-3411-B','1-03-03-44-3412-B','1-03-03-44-3414-B','1-03-03-44-3415-B','1-03-03-44-3416-B','1-03-03-44-3420-B','1-03-03-44-3421-B','1-03-03-44-3425-B') THEN 'FITNESSREV'  " & vbCrLf
    sql4 = sql4 & "     WHEN GL IN ('1-03-03-46-3820-B','1-03-03-46-3910-B') THEN 'CHRISTMASREV'  " & vbCrLf
    sql4 = sql4 & "     WHEN GL IN ('1-03-03-46-9604-V') THEN 'CHRISTMASCUST'  " & vbCrLf
    sql4 = sql4 & " end as [Name],  " & vbCrLf
    sql4 = sql4 & "Q1.PostDate,  " & vbCrLf
    sql4 = sql4 & "DebitAmount,  " & vbCrLf
    sql4 = sql4 & "CreditAmount  " & vbCrLf
    sql4 = sql4 & "FROM(  " & vbCrLf
    sql4 = sql4 & "SELECT    " & vbCrLf
    sql4 = sql4 & "      [PostDate],  " & vbCrLf
    sql4 = sql4 & " (ac.AuxiliarySegment7Code+ '-' + ac.AuxiliarySegment1Code  +'-'+ ac.AuxiliarySegment8Code  +'-'+    ac.AuxiliarySegment9Code +'-'+ ac.PrimarySegmentCode+'-'+ac.LedgerSegmentCode) as GL,  " & vbCrLf
    sql4 = sql4 & "      [DebitAmount],  " & vbCrLf
    sql4 = sql4 & "      [CreditAmount]  " & vbCrLf
    sql4 = sql4 & "  FROM [ActivityInns].[dbo].[JournalDetail] jd  " & vbCrLf
    sql4 = sql4 & " LEFT JOIN [ActivityInns].[dbo].[Account] a on jd.Account=a.Account  " & vbCrLf
    sql4 = sql4 & " LEFT JOIN [ActivityInns].[dbo].[AccountCode] ac on a.Account=ac.Account  " & vbCrLf
    sql4 = sql4 & " LEFT JOIN [ActivityInns].[dbo].[PrimarySegmentItem] ps on a.PrimarySegmentItem=ps.PrimarySegmentItem  " & vbCrLf
    sql4 = sql4 & " LEFT JOIN [ActivityInns].[dbo].[LedgerSegmentItem] ls on a.LedgerSegmentItem=ls.LedgerSegmentItem  " & vbCrLf
    sql4 = sql4 & "  where 1=1  " & vbCrLf
    sql4 = sql4 & " AND PostDate BETWEEN @dMonthStart and @dMonthEnd  " & vbCrLf
    sql4 = sql4 & " AND [HoldEntry]='N'  " & vbCrLf
    sql4 = sql4 & ") AS Q1  " & vbCrLf
    sql4 = sql4 & "UNION ALL  " & vbCrLf
    sql4 = sql4 & "SELECT 'RSWPREV' AS Name, @dFrom as postdate, 0 as debit, 0 as credit  " & vbCrLf
    sql4 = sql4 & "UNION ALL   " & vbCrLf
    sql4 = sql4 & "SELECT 'RSWPOR' AS Name, @dFrom as postdate, 0 as debit, 0 as credit  " & vbCrLf
    sql4 = sql4 & "UNION ALL   " & vbCrLf
    sql4 = sql4 & "SELECT 'RSWPAR' AS Name, @dFrom as postdate, 0 as debit, 0 as credit  " & vbCrLf
    sql4 = sql4 & "UNION ALL   " & vbCrLf
    sql4 = sql4 & "SELECT 'FBRIVUECUST' AS Name, @dFrom as postdate, 0 as debit, 0 as credit  " & vbCrLf
    sql4 = sql4 & "UNION ALL   " & vbCrLf
    sql4 = sql4 & "SELECT 'FBRIVUEREV' AS Name, @dFrom as postdate, 0 as debit, 0 as credit  " & vbCrLf
    sql4 = sql4 & "UNION ALL   " & vbCrLf
    sql4 = sql4 & "SELECT 'FBMTOECUST' AS Name, @dFrom as postdate, 0 as debit, 0 as credit  " & vbCrLf
    sql4 = sql4 & "UNION ALL   " & vbCrLf
    sql4 = sql4 & "SELECT 'FBMTOEREV' AS Name, @dFrom as postdate, 0 as debit, 0 as credit  " & vbCrLf
    sql4 = sql4 & "UNION ALL   " & vbCrLf
    sql4 = sql4 & "SELECT 'FBCMAGCUST' AS Name, @dFrom as postdate, 0 as debit, 0 as credit  " & vbCrLf
    sql4 = sql4 & "UNION ALL   " & vbCrLf
    sql4 = sql4 & "SELECT 'FBCMAGREV' AS Name, @dFrom as postdate, 0 as debit, 0 as credit  " & vbCrLf
    sql4 = sql4 & "UNION ALL   " & vbCrLf
    sql4 = sql4 & "SELECT 'FBJSILKSCUST' AS Name, @dFrom as postdate, 0 as debit, 0 as credit  " & vbCrLf
    sql4 = sql4 & "UNION ALL   " & vbCrLf
    sql4 = sql4 & "SELECT 'FBWSIDEREV' AS Name, @dFrom as postdate, 0 as debit, 0 as credit  " & vbCrLf
    sql4 = sql4 & "UNION ALL   " & vbCrLf
    sql4 = sql4 & "SELECT 'FBWSIDECUST' AS Name, @dFrom as postdate, 0 as debit, 0 as credit  " & vbCrLf
    sql4 = sql4 & "UNION ALL   " & vbCrLf
    sql4 = sql4 & "SELECT 'FBTHELMACUST' AS Name, @dFrom as postdate, 0 as debit, 0 as credit  " & vbCrLf
    sql4 = sql4 & "UNION ALL   " & vbCrLf
    sql4 = sql4 & "SELECT 'FBTHELMAREV' AS Name, @dFrom as postdate, 0 as debit, 0 as credit  " & vbCrLf
    sql4 = sql4 & "UNION ALL   " & vbCrLf
    sql4 = sql4 & "SELECT 'FBALJCUST' AS Name, @dFrom as postdate, 0 as debit, 0 as credit  " & vbCrLf
    sql4 = sql4 & "UNION ALL   " & vbCrLf
    sql4 = sql4 & "SELECT 'FBALJREV' AS Name, @dFrom as postdate, 0 as debit, 0 as credit  " & vbCrLf
    sql4 = sql4 & "UNION ALL   " & vbCrLf
    sql4 = sql4 & "SELECT 'FBSPIRITSCUST' AS Name, @dFrom as postdate, 0 as debit, 0 as credit  " & vbCrLf
    sql4 = sql4 & "UNION ALL   " & vbCrLf
    sql4 = sql4 & "SELECT 'FBSPIRITSREV' AS Name, @dFrom as postdate, 0 as debit, 0 as credit  " & vbCrLf
    sql4 = sql4 & "UNION ALL   " & vbCrLf
    sql4 = sql4 & "SELECT 'FBBCCCUST' AS Name, @dFrom as postdate, 0 as debit, 0 as credit  " & vbCrLf
    sql4 = sql4 & "UNION ALL   " & vbCrLf
    sql4 = sql4 & "SELECT 'FBBCCREV' AS Name, @dFrom as postdate, 0 as debit, 0 as credit  " & vbCrLf
    sql4 = sql4 & "UNION ALL   " & vbCrLf
    sql4 = sql4 & "SELECT 'FBINROOMCUST' AS Name, @dFrom as postdate, 0 as debit, 0 as credit  " & vbCrLf
    sql4 = sql4 & "UNION ALL   " & vbCrLf
    sql4 = sql4 & "SELECT 'FBINROOMREV' AS Name, @dFrom as postdate, 0 as debit, 0 as credit  " & vbCrLf
    sql4 = sql4 & "UNION ALL   " & vbCrLf
    sql4 = sql4 & "SELECT 'FITNESSREV' AS Name, @dFrom as postdate, 0 as debit, 0 as credit  " & vbCrLf
    sql4 = sql4 & "UNION ALL   " & vbCrLf
    sql4 = sql4 & "SELECT 'CHRISTMASREV' AS Name, @dFrom as postdate, 0 as debit, 0 as credit  " & vbCrLf
    sql4 = sql4 & "UNION ALL   " & vbCrLf
    sql4 = sql4 & "SELECT 'CHRISTMASCUST' AS Name, @dFrom as postdate, 0 as debit, 0 as credit  " & vbCrLf
    sql4 = sql4 & ") AS Q2  " & vbCrLf
    sql4 = sql4 & "Where [name] is not null  " & vbCrLf
    sql4 = sql4 & "GROUP BY [Name]  "
    
    Range("A4").Select
        ActiveCell.FormulaR1C1 = "...Loading Budgeted Revenue and Covers..."
        ActiveCell.Interior.Color = RGB(255, 255, 0)
    sql4 = Replace(sql4, "?1", Worksheets("Params").Range("B1").Value)
    sql4 = Replace(sql4, "?2", Worksheets("Params").Range("B2").Value)
    With ActiveWorkbook.Connections("BudgetActivity").OLEDBConnection
    .CommandText = sql4
    .Refresh
    End With
    
    
    ActiveWorkbook.RefreshAll
    Range("A4").Select
        ActiveCell.FormulaR1C1 = "Finished Loading."
        ActiveCell.Interior.Color = RGB(0, 255, 0)
    'Sheets("Delphi").Select
    'Range("B6").Select
    ' ActiveCell.FormulaR1C1 = sql
    ' Range("B7").Select
    End If
    End Sub


    Regards,

    Emi Zhang
    TechNet Community Support


    Please mark the reply as an answer if you find it is helpful.

    If you have feedback for TechNet Support, contact tnmff@microsoft.com.

    Thursday, February 25, 2016 1:41 AM
  • Hi Deuce Sapp,

    I am trying to reproduce this issue, however failed. To narrow down this issue, I suggest that you break the code into multiple line to see which property or the function cause this issue. 

    And can you reproduce this issue in a new workbook?

    Regards & Fei



    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Monday, February 29, 2016 7:35 AM