Toad World® Forums

Export to Excel Macro Enabled Workbook fails and Cannot open Excel Application without rebooting


#1

Toad Data Point 3.8

Windows 7

I have an automation script that contains 2 export templates. The first template runs SQL and export the results to a specific tab within a macro enabled workbook. I have it set to run macro after exporting data to Excel. The next template runs SQL and exports to same file different tab. The automation failed and now the Excel application will not open any files at all. I have had to reboot in order to get Excel to function again. I have also added a macro to save & close the Excel workbook and set to run after each export step. Any solutions would be appreciated. Thanks!

See log file:

6:38:06 PM Thread (3) Export Started [6/26/2018 6:38:06 PM]
6:38:06 PM Thread (3) Export using connection: BHEDWPROD (ai_rpt)
6:38:06 PM Thread (3) Export Wizard: Building object list
6:38:06 PM Thread (3) Exporting Data (1 of 1)
6:38:06 PM Thread (3) Object SQL Query started.
6:38:06 PM Thread (24) InternalReadBackground - start
6:39:14 PM Thread (24) InternalReadBackground - after ExecuteReader - command SELECT TOP 10 DIM.CLM_SRC_MBR_SYS_ID||’-’||DIM.CLM_SRC_MBR_SBSCR_ID MBR_CK, SUM(FCT.CLM_NET_PD_AMT) PAID_AMT INTO #MBRS FROM BHEDW_PROD.DM.TB_DIM_CLM_NET_DTL DIM INNER JOIN BHEDW_PROD.DM.TB_FCT_CLM_NET_DTL FCT ON (DIM.CLM_NET_SYS_ID = FCT.CLM_NET_SYS_ID) WHERE DATEPART(qq,DIM.CLM_NET_DT_SRVC_FROM_DT)||’-’||DATEPART(yy,DIM.CLM_NET_DT_SRVC_FROM_DT) = DATEPART(qq,DATEADD(qq,-1,GETDATE()))||’-’|| CASE WHEN (DATEPART(qq,DATEADD(qq,-1,GETDATE())))=4 THEN DATEPART(yy,DATEADD(yy,-1,GETDATE())) ELSE DATEPART(yy,GETDATE()) END AND DIM.CLM_NET_PRI_PAYE_CD_DESC NOT IN (‘PROVIDER’,‘VENDOR’) AND CASE WHEN (DIM.CLM_SRC_COSM_DIV_CD) = ‘MPC’ AND DIM.CLM_SRC_LEG_ENTY_NBR||’-’||DIM.CLM_SRC_MBR_PRDCT_CD NOT IN (‘01358-01209’, ‘01358-01210’, ‘01358-01211’, ‘01358-01212’, ‘01358-00413’, ‘01358-00603’, ‘01417-01209’, ‘01417-01210’, ‘01417-01211’, ‘01417-01212’, ‘01417-00413’, ‘01417-00603’) THEN ‘MEDICAL’ ELSE ‘BH’ END = ‘BH’ GROUP BY DIM.CLM_SRC_MBR_SYS_ID||’-’||DIM.CLM_SRC_MBR_SBSCR_ID ORDER BY SUM(FCT.CLM_NET_PD_AMT)DESC SELECT DIM.CLM_NET_CLM_FM_TYP_CD, FCT.PROV_SYS_ID , PROV.SRC_PROV_ID, DIM.CLM_SRC_PROV_TAX_ID_NBR TIN, DIM.CLM_SRC_PROV_CLM_NPI_ID NPI, DIM.CLM_SRC_PROV_SRVC_CTY_NM, DIM.CLM_SRC_PROV_SRVC_ST_CD, DIM.CLM_SRC_PROV_SRVC_ZIP_CD, PROV.SRC_PROV_ID_SRC_CD, PROV.PROV_NM, CASE WHEN DIM.CLM_SRC_PROV_PAYE_NM =‘U’ THEN PROV.PROV_NM ELSE DIM.CLM_SRC_PROV_PAYE_NM END AS PAYEE_NM, DIM.CLM_NET_PRI_PAYE_CD_DESC, CASE WHEN (DWP.PROV_REIM_CATGY_NM) IS NULL OR (DWP.PROV_REIM_CATGY_NM) = ‘Unknown’ THEN DWP.PROV_FACL_TYP_DESC ELSE DWP.PROV_REIM_CATGY_NM END PROV_REIM_CATGY_NM, PROV.PROV_DEG_CRDNTL_DESC, DIM.CLM_NET_PROV_NTWK_DERIV_CD, DIM.CLM_SRC_MBR_SYS_ID||’-’||DIM.CLM_SRC_MBR_SBSCR_ID MBR_CK, MEM.MBR_SYS_ID, DIM.CLM_SRC_MBR_SBSCR_ID, GRP.GRP_NM, MEM.MBR_NM_FST_TXT, MEM.MBR_NM_LST_TXT, MEM.BTH_DT, DIM.CLM_SRC_MBR_GDR_CD, MEM.SSN, CAST(DATEDIFF(DD, MEM.BTH_DT, DIM.CLM_NET_DT_SRVC_FROM_DT)/365.25 AS DECIMAL(10,0)) AS AGE_DOS, MEM.ELIG_ADR_LN_1_TXT MBR_ELIG_ADDR1, MEM.ELIG_ADR_LN_2_TXT MBR_ELIG_ADDR2, MEM.ELIG_ADR_CITY_NM MBR_ELIG_CITY, MEM.ELIG_ST_ABBR_CD MBR_ELIG_ST, LEFT(MEM.ELIG_ZIP_CD,5) MBR_ELIG_ZIP, MEM.MAIL_ADR_LN_1_TXT MBR_MAIL_ADDR1, MEM.MAIL_ADR_LN_2_TXT MBR_MAIL_ADDR2, MEM.MAIL_ADR_CITY_NM MBR_MAIL_CITY, MEM.MAIL_ST_ABBR_CD MBR_MAIL_ST, LEFT(MEM.MAIL_ZIP_CD,5) MBR_MAIL_ZIP, DIM.CLM_NET_DIAG_MH_SA_CD, PKG.PRDCT_CMPNT_GRP_DESC, PKG.BH_PLN_TYP_DESC, GRP.ACCT_NM, PKG.BH_UBH_FUND_DESC, SALE.SALE_CHNL_NM, GRP.CUST_NM, GRP.GRP_ALT_ID, GRP.CBMS_ACCT_ID, DIM.CLM_SRC_MBR_PRDCT_CD, DIM.CLM_SRC_LEG_ENTY_NBR, DIM.CLM_AUD_SRC_SYS_CD, DIM.CLM_SRC_COSM_DIV_CD, DIM.CLM_AUD_SRC_ID, DIM.CLM_AUD_NBR, DIM.CLM_NET_PROC_TYP_CD, MAX(CASE WHEN LEN(RTRIM(CASE WHEN(RTRIM(CASE WHEN (DIM.CLM_NET_PROC_TYP_CD IN (‘U’,‘REV’)) AND (STU.BIL_PROC_CD) IS NOT NULL AND (STU.BIL_PROC_CD) != ’ ’ THEN STU.BIL_PROC_CD WHEN (DIM.CLM_NET_PROC_TYP_CD IN (‘U’,‘REV’)) AND ((STU.BIL_PROC_CD) IS NULL OR (STU.BIL_PROC_CD) = ’ ') AND (DIM.CLM_NET_SEC_PROC_CD) != ’ ’ THEN DIM.CLM_NET_SEC_PROC_CD ELSE (CASE WHEN DIM.CLM_NET_PROC_TYP_CD IN (‘U’,‘REV’) AND LEN(DIM.CLM_NET_PROC_CD)=4 AND LEFT(DIM.CLM_NET_PROC_CD,1)=‘0’ THEN RIGHT(DIM.CLM_NET_PROC_CD,3) ELSE DIM.CLM_NET_PROC_CD END) END)) IN (‘240’,‘90899’,‘89240’) AND DIM.BIL_RVNU_CD != ’ ’ THEN (CASE WHEN (LEFT(DIM.BIL_RVNU_CD,1)) = ‘0’ THEN RIGHT(DIM.BIL_RVNU_CD,3) ELSE DIM.BIL_RVNU_CD END) WHEN (RTRIM(CASE WHEN (DIM.CLM_NET_PROC_TYP_CD IN (‘U’,‘REV’)) AND (STU.BIL_PROC_CD) IS NOT NULL AND (STU.BIL_PROC_CD) != ’ ’ THEN STU.BIL_PROC_CD WHEN (DIM.CLM_NET_PROC_TYP_CD IN (‘U’,‘REV’)) AND ((STU.BIL_PROC_CD) IS NULL OR (STU.BIL_PROC_CD) = ’ ') AND (DIM.CLM_NET_SEC_PROC_CD) != ’ ’ THEN DIM.CLM_NET_SEC_PROC_CD ELSE (CASE WHEN DIM.CLM_NET_PROC_TYP_CD IN (‘U’,‘REV’) AND LEN(DIM.CLM_NET_PROC_CD)=4 AND LEFT(DIM.CLM_NET_PROC_CD,1)=‘0’ THEN RIGHT(DIM.CLM_NET_PROC_CD,3) ELSE DIM.CLM_NET_PROC_CD END) END))IN (‘240’,‘90899’,‘89240’) AND DIM.BIL_RVNU_CD = ’ ’ THEN (CASE WHEN (DIM.CLM_NET_PROC_TYP_CD) IN (‘U’,‘REV’) AND LEN(DIM.CLM_NET_PROC_CD)= 4 AND LEFT(DIM.CLM_NET_PROC_CD,1)=‘0’ THEN RIGHT(DIM.CLM_NET_PROC_CD,3) ELSE DIM.CLM_NET_PROC_CD END) ELSE (RTRIM(CASE WHEN (DIM.CLM_NET_PROC_TYP_CD IN (‘U’,‘REV’)) AND (STU.BIL_PROC_CD) IS NOT NULL AND (STU.BIL_PROC_CD) != ’ ’ THEN STU.BIL_PROC_CD WHEN (DIM.CLM_NET_PROC_TYP_CD IN (‘U’,‘REV’)) AND ((STU.BIL_PROC_CD) IS NULL OR (STU.BIL_PROC_CD) = ’ ‘) AND (DIM.CLM_NET_SEC_PROC_CD) != ’ ’ THEN DIM.CLM_NET_SEC_PROC_CD ELSE (CASE WHEN DIM.CLM_NET_PROC_TYP_CD IN (‘U’,‘REV’) AND LEN(DIM.CLM_NET_PROC_CD)=4 AND LEFT(DIM.CLM_NET_PROC_CD,1)=‘0’ THEN RIGHT(DIM.CLM_NET_PROC_CD,3) ELSE DIM.CLM_NET_PROC_CD END) END)) END))= 4 AND LEFT(CASE WHEN(RTRIM(CASE WHEN (DIM.CLM_NET_PROC_TYP_CD IN (‘U’,‘REV’)) AND (STU.BIL_PROC_CD) IS NOT NULL AND (STU.BIL_PROC_CD) != ’ ’ THEN STU.BIL_PROC_CD WHEN (DIM.CLM_NET_PROC_TYP_CD IN (‘U’,‘REV’)) AND ((STU.BIL_PROC_CD) IS NULL OR (STU.BIL_PROC_CD) = ’ ‘) AND (DIM.CLM_NET_SEC_PROC_CD) != ’ ’ THEN DIM.CLM_NET_SEC_PROC_CD ELSE (CASE WHEN DIM.CLM_NET_PROC_TYP_CD IN (‘U’,‘REV’) AND LEN(DIM.CLM_NET_PROC_CD)=4 AND LEFT(DIM.CLM_NET_PROC_CD,1)=‘0’ THEN RIGHT(DIM.CLM_NET_PROC_CD,3) ELSE DIM.CLM_NET_PROC_CD END) END)) IN (‘240’,‘90899’,‘89240’) AND DIM.BIL_RVNU_CD != ’ ’ THEN (CASE WHEN (LEFT(DIM.BIL_RVNU_CD,1)) = ‘0’ THEN RIGHT(DIM.BIL_RVNU_CD,3) ELSE DIM.BIL_RVNU_CD END) WHEN (RTRIM(CASE WHEN (DIM.CLM_NET_PROC_TYP_CD IN (‘U’,‘REV’)) AND (STU.BIL_PROC_CD) IS NOT NULL AND (STU.BIL_PROC_CD) != ’ ’ THEN STU.BIL_PROC_CD WHEN (DIM.CLM_NET_PROC_TYP_CD IN (‘U’,‘REV’)) AND ((STU.BIL_PROC_CD) IS NULL OR (STU.BIL_PROC_CD) = ’ ‘) AND (DIM.CLM_NET_SEC_PROC_CD) != ’ ’ THEN DIM.CLM_NET_SEC_PROC_CD ELSE (CASE WHEN DIM.CLM_NET_PROC_TYP_CD IN (‘U’,‘REV’) AND LEN(DIM.CLM_NET_PROC_CD)=4 AND LEFT(DIM.CLM_NET_PROC_CD,1)=‘0’ THEN RIGHT(DIM.CLM_NET_PROC_CD,3) ELSE DIM.CLM_NET_PROC_CD END) END))IN (‘240’,‘90899’,‘89240’) AND DIM.BIL_RVNU_CD = ’ ’ THEN (CASE WHEN (DIM.CLM_NET_PROC_TYP_CD) IN (‘U’,‘REV’) AND LEN(DIM.CLM_NET_PROC_CD)= 4 AND LEFT(DIM.CLM_NET_PROC_CD,1)=‘0’ THEN RIGHT(DIM.CLM_NET_PROC_CD,3) ELSE DIM.CLM_NET_PROC_CD END) ELSE (RTRIM(CASE WHEN (DIM.CLM_NET_PROC_TYP_CD IN (‘U’,‘REV’)) AND (STU.BIL_PROC_CD) IS NOT NULL AND (STU.BIL_PROC_CD) != ’ ’ THEN STU.BIL_PROC_CD WHEN (DIM.CLM_NET_PROC_TYP_CD IN (‘U’,‘REV’)) AND ((STU.BIL_PROC_CD) IS NULL OR (STU.BIL_PROC_CD) = ’ ‘) AND (DIM.CLM_NET_SEC_PROC_CD) != ’ ’ THEN DIM.CLM_NET_SEC_PROC_CD ELSE (CASE WHEN DIM.CLM_NET_PROC_TYP_CD IN (‘U’,‘REV’) AND LEN(DIM.CLM_NET_PROC_CD)=4 AND LEFT(DIM.CLM_NET_PROC_CD,1)=‘0’ THEN RIGHT(DIM.CLM_NET_PROC_CD,3) ELSE DIM.CLM_NET_PROC_CD END) END)) END,1) = ‘0’ THEN RIGHT(RTRIM(CASE WHEN(RTRIM(CASE WHEN (DIM.CLM_NET_PROC_TYP_CD IN (‘U’,‘REV’)) AND (STU.BIL_PROC_CD) IS NOT NULL AND (STU.BIL_PROC_CD) != ’ ’ THEN STU.BIL_PROC_CD WHEN (DIM.CLM_NET_PROC_TYP_CD IN (‘U’,‘REV’)) AND ((STU.BIL_PROC_CD) IS NULL OR (STU.BIL_PROC_CD) = ’ ‘) AND (DIM.CLM_NET_SEC_PROC_CD) != ’ ’ THEN DIM.CLM_NET_SEC_PROC_CD ELSE (CASE WHEN DIM.CLM_NET_PROC_TYP_CD IN (‘U’,‘REV’) AND LEN(DIM.CLM_NET_PROC_CD)=4 AND LEFT(DIM.CLM_NET_PROC_CD,1)=‘0’ THEN RIGHT(DIM.CLM_NET_PROC_CD,3) ELSE DIM.CLM_NET_PROC_CD END) END)) IN (‘240’,‘90899’,‘89240’) AND DIM.BIL_RVNU_CD != ’ ’ THEN (CASE WHEN (LEFT(DIM.BIL_RVNU_CD,1)) = ‘0’ THEN RIGHT(DIM.BIL_RVNU_CD,3) ELSE DIM.BIL_RVNU_CD END) WHEN (RTRIM(CASE WHEN (DIM.CLM_NET_PROC_TYP_CD IN (‘U’,‘REV’)) AND (STU.BIL_PROC_CD) IS NOT NULL AND (STU.BIL_PROC_CD) != ’ ’ THEN STU.BIL_PROC_CD WHEN (DIM.CLM_NET_PROC_TYP_CD IN (‘U’,‘REV’)) AND ((STU.BIL_PROC_CD) IS NULL OR (STU.BIL_PROC_CD) = ’ ‘) AND (DIM.CLM_NET_SEC_PROC_CD) != ’ ’ THEN DIM.CLM_NET_SEC_PROC_CD ELSE (CASE WHEN DIM.CLM_NET_PROC_TYP_CD IN (‘U’,‘REV’) AND LEN(DIM.CLM_NET_PROC_CD)=4 AND LEFT(DIM.CLM_NET_PROC_CD,1)=‘0’ THEN RIGHT(DIM.CLM_NET_PROC_CD,3) ELSE DIM.CLM_NET_PROC_CD END) END))IN (‘240’,‘90899’,‘89240’) AND DIM.BIL_RVNU_CD = ’ ’ THEN (CASE WHEN (DIM.CLM_NET_PROC_TYP_CD) IN (‘U’,‘REV’) AND LEN(DIM.CLM_NET_PROC_CD)= 4 AND LEFT(DIM.CLM_NET_PROC_CD,1)=‘0’ THEN RIGHT(DIM.CLM_NET_PROC_CD,3) ELSE DIM.CLM_NET_PROC_CD END) ELSE (RTRIM(CASE WHEN (DIM.CLM_NET_PROC_TYP_CD IN (‘U’,‘REV’)) AND (STU.BIL_PROC_CD) IS NOT NULL AND (STU.BIL_PROC_CD) != ’ ’ THEN STU.BIL_PROC_CD WHEN (DIM.CLM_NET_PROC_TYP_CD IN (‘U’,‘REV’)) AND ((STU.BIL_PROC_CD) IS NULL OR (STU.BIL_PROC_CD) = ’ ‘) AND (DIM.CLM_NET_SEC_PROC_CD) != ’ ’ THEN DIM.CLM_NET_SEC_PROC_CD ELSE (CASE WHEN DIM.CLM_NET_PROC_TYP_CD IN (‘U’,‘REV’) AND LEN(DIM.CLM_NET_PROC_CD)=4 AND LEFT(DIM.CLM_NET_PROC_CD,1)=‘0’ THEN RIGHT(DIM.CLM_NET_PROC_CD,3) ELSE DIM.CLM_NET_PROC_CD END) END)) END),3) ELSE (CASE WHEN(RTRIM(CASE WHEN (DIM.CLM_NET_PROC_TYP_CD IN (‘U’,‘REV’)) AND (STU.BIL_PROC_CD) IS NOT NULL AND (STU.BIL_PROC_CD) != ’ ’ THEN STU.BIL_PROC_CD WHEN (DIM.CLM_NET_PROC_TYP_CD IN (‘U’,‘REV’)) AND ((STU.BIL_PROC_CD) IS NULL OR (STU.BIL_PROC_CD) = ’ ‘) AND (DIM.CLM_NET_SEC_PROC_CD) != ’ ’ THEN DIM.CLM_NET_SEC_PROC_CD ELSE (CASE WHEN DIM.CLM_NET_PROC_TYP_CD IN (‘U’,‘REV’) AND LEN(DIM.CLM_NET_PROC_CD)=4 AND LEFT(DIM.CLM_NET_PROC_CD,1)=‘0’ THEN RIGHT(DIM.CLM_NET_PROC_CD,3) ELSE DIM.CLM_NET_PROC_CD END) END)) IN (‘240’,‘90899’,‘89240’) AND DIM.BIL_RVNU_CD != ’ ’ THEN (CASE WHEN (LEFT(DIM.BIL_RVNU_CD,1)) = ‘0’ THEN RIGHT(DIM.BIL_RVNU_CD,3) ELSE DIM.BIL_RVNU_CD END) WHEN (RTRIM(CASE WHEN (DIM.CLM_NET_PROC_TYP_CD IN (‘U’,‘REV’)) AND (STU.BIL_PROC_CD) IS NOT NULL AND (STU.BIL_PROC_CD) != ’ ’ THEN STU.BIL_PROC_CD WHEN (DIM.CLM_NET_PROC_TYP_CD IN (‘U’,‘REV’)) AND ((STU.BIL_PROC_CD) IS NULL OR (STU.BIL_PROC_CD) = ’ ‘) AND (DIM.CLM_NET_SEC_PROC_CD) != ’ ’ THEN DIM.CLM_NET_SEC_PROC_CD ELSE (CASE WHEN DIM.CLM_NET_PROC_TYP_CD IN (‘U’,‘REV’) AND LEN(DIM.CLM_NET_PROC_CD)=4 AND LEFT(DIM.CLM_NET_PROC_CD,1)=‘0’ THEN RIGHT(DIM.CLM_NET_PROC_CD,3) ELSE DIM.CLM_NET_PROC_CD END) END))IN (‘240’,‘90899’,‘89240’) AND DIM.BIL_RVNU_CD = ’ ’ THEN (CASE WHEN (DIM.CLM_NET_PROC_TYP_CD) IN (‘U’,‘REV’) AND LEN(DIM.CLM_NET_PROC_CD)= 4 AND LEFT(DIM.CLM_NET_PROC_CD,1)=‘0’ THEN RIGHT(DIM.CLM_NET_PROC_CD,3) ELSE DIM.CLM_NET_PROC_CD END) ELSE (RTRIM(CASE WHEN (DIM.CLM_NET_PROC_TYP_CD IN (‘U’,‘REV’)) AND (STU.BIL_PROC_CD) IS NOT NULL AND (STU.BIL_PROC_CD) != ’ ’ THEN STU.BIL_PROC_CD WHEN (DIM.CLM_NET_PROC_TYP_CD IN (‘U’,‘REV’)) AND ((STU.BIL_PROC_CD) IS NULL OR (STU.BIL_PROC_CD) = ’ ‘) AND (DIM.CLM_NET_SEC_PROC_CD) != ’ ’ THEN DIM.CLM_NET_SEC_PROC_CD ELSE (CASE WHEN DIM.CLM_NET_PROC_TYP_CD IN (‘U’,‘REV’) AND LEN(DIM.CLM_NET_PROC_CD)=4 AND LEFT(DIM.CLM_NET_PROC_CD,1)=‘0’ THEN RIGHT(DIM.CLM_NET_PROC_CD,3) ELSE DIM.CLM_NET_PROC_CD END) END)) END) END) SVC_CODE, CAST(NULL as varchar(250)) AS SVC_CODE_DESC, CASE WHEN DIM.CLM_NET_PROC_TYP_CD IN (‘U’,‘REV’) AND LEN(DIM.CLM_NET_PROC_CD)= 4 AND LEFT(DIM.CLM_NET_PROC_CD,1)=‘0’ THEN RIGHT(DIM.CLM_NET_PROC_CD,3) ELSE DIM.CLM_NET_PROC_CD END PROC_CODE, DIM.CLM_NET_PROC_MOD_CD, DIM.CLM_NET_PROC_MOD_2_CD, DIM.CLM_NET_PROC_MOD_3_CD, DIM.CLM_NET_PROC_MOD_4_CD, DIM.CLM_NET_PROC_DESC, CASE WHEN (DIM.CLM_NET_PROC_TYP_CD) IN (‘REV’,‘U’) THEN DIM.CLM_NET_SEC_PROC_CD ELSE ‘’ END CLM_NET_SEC_PROC_CD, CASE WHEN (DIM.CLM_NET_SEC_PROC_DESC) = ‘Unknown’ THEN ‘’ WHEN (DIM.CLM_NET_PROC_DESC = DIM.CLM_NET_SEC_PROC_DESC) THEN ‘’ ELSE DIM.CLM_NET_SEC_PROC_DESC END CLM_NET_SEC_PROC_DESC , MAX(DIM.BIL_RVNU_CD) BIL_RVNU_CD, MAX(STU.BIL_PROC_CD) BIL_PROC_CD, MAX(DIM.CLM_NET_DLOC_TXT)CLM_NET_DLOC_TXT , MAX(DIM.CLM_SRC_AUTH_NBR) CLM_SRC_AUTH_NBR, MAX(I.BILLED_UNITS) BILLED_UNITS, CONVERT(VARCHAR(10),DATEADD(dd,-(DAY(DIM.CLM_NET_DT_SRVC_FROM_DT)-1),DIM.CLM_NET_DT_SRVC_FROM_DT),101) DOS_MONTH, TRANS.CLM_ETL_DW_FROM_DT, DIM.CLM_NET_DT_SRVC_FROM_DT, DIM.CLM_NET_DT_SRVC_THRU_DT, DIM.CLM_NET_DT_RCVD_DT , DIM.CLM_NET_PL_OF_SRVC_AMA_CD as POS, DIM.CLM_NET_DIAG_1_CD AS DX1, DIM.CLM_NET_DIAG_1_CD_DESC AS DX1_DESC, DIM.CLM_NET_DIAG_2_CD AS DX2, DIM.CLM_NET_DIAG_2_CD_DESC AS DX2_DESC, DIM.CLM_NET_DIAG_3_CD AS DX3, DIM.CLM_NET_DIAG_3_CD_DESC AS DX3_DESC, SUM(FCT.CLM_NET_SBMT_AMT) AS BILLED_AMT, SUM(FCT.CLM_NET_ALLW_DERIV_AMT) AS ALLW_DERIV_AMT, SUM(FCT.CLM_NET_DSALLW_AMT) AS DSALLW_AMT, SUM(FCT.CLM_NET_DSCNT_AMT) AS DISCOUNT_AMT, SUM(FCT.CLM_NET_MBR_RESP_AMT) AS MBR_RESP_AMT, SUM(FCT.CLM_NET_COB_AMT) AS COB_AMT, SUM(FCT.CLM_NET_PD_AMT) AS PAID_AMT INTO #CLAIMS FROM #MBRS M INNER JOIN BHEDW_PROD.DM.TB_DIM_CLM_NET_DTL DIM ON M.MBR_CK = DIM.CLM_SRC_MBR_SYS_ID||’-’||DIM.CLM_SRC_MBR_SBSCR_ID INNER JOIN BHEDW_PROD.DM.TB_FCT_CLM_NET_DTL FCT ON (DIM.CLM_NET_SYS_ID = FCT.CLM_NET_SYS_ID) INNER JOIN (SELECT DISTINCT DIM.CLM_AUD_SRC_ID, DIM.CLM_TRANS_SYS_ID, DIM.CLM_AUD_LN_NBR, CASE WHEN DIM.CLM_TRANS_PROC_TYP_CD IN (‘U’,‘REV’) AND LEN(DIM.CLM_TRANS_PROC_CD)= 4 AND LEFT(DIM.CLM_TRANS_PROC_CD,1)=‘0’ THEN RIGHT(DIM.CLM_TRANS_PROC_CD,3) ELSE DIM.CLM_TRANS_PROC_CD END PROC_CODE, DIM.CLM_ETL_DW_FROM_DT, DIM.CLM_TRANS_DT_SRVC_FROM_DT FROM BHEDW_PROD.DM.TB_DIM_CLM_TRANS DIM) TRANS ON (DIM.CLM_AUD_SRC_ID = TRANS.CLM_AUD_SRC_ID) AND (DIM.CLM_AUD_LN_NBR = TRANS.CLM_AUD_LN_NBR) AND (CASE WHEN DIM.CLM_NET_PROC_TYP_CD IN (‘U’,‘REV’) AND LEN(DIM.CLM_NET_PROC_CD)= 4 AND LEFT(DIM.CLM_NET_PROC_CD,1)=‘0’ THEN RIGHT(DIM.CLM_NET_PROC_CD,3) ELSE DIM.CLM_NET_PROC_CD END = TRANS.PROC_CODE) AND (DIM.CLM_NET_DT_SRVC_FROM_DT = TRANS.CLM_TRANS_DT_SRVC_FROM_DT) AND (DIM.CLM_NET_SYS_ID = TRANS.CLM_TRANS_SYS_ID) LEFT OUTER JOIN (SELECT DIM.CLM_AUD_SRC_ID, DIM.CLM_AUD_LN_NBR, DIM.CLM_TRANS_DT_SRVC_FROM_DT, MAX(FCT.CLM_TRANS_UNIT_CNT) BILLED_UNITS FROM DM.TB_DIM_CLM_TRANS DIM INNER JOIN BHEDW_PROD.DM.TB_FCT_CLM_TRANS FCT ON (DIM.CLM_TRANS_SYS_ID = FCT.CLM_TRANS_SYS_ID) GROUP BY DIM.CLM_AUD_SRC_ID, DIM.CLM_AUD_LN_NBR, DIM.CLM_TRANS_DT_SRVC_FROM_DT) I ON (DIM.CLM_AUD_SRC_ID = I.CLM_AUD_SRC_ID AND DIM.CLM_AUD_LN_NBR = I.CLM_AUD_LN_NBR AND DIM.CLM_NET_DT_SRVC_FROM_DT = I.CLM_TRANS_DT_SRVC_FROM_DT) LEFT OUTER JOIN BHEDW_PROD.DM.TB_DIM_CUS_GRP GRP ON (FCT.GRP_SYS_ID=GRP.GRP_SYS_ID) LEFT OUTER JOIN BHEDW_PROD.DM.TB_DIM_MBR MEM ON (FCT.MBR_SYS_ID = MEM.MBR_SYS_ID) LEFT OUTER JOIN BHEDW_PROD.DM.TB_DIM_PROV PROV ON (FCT.PROV_SYS_ID = PROV.PROV_SYS_ID) LEFT OUTER JOIN BHEDW_PROD.DM.TB_DIM_PRD_PKG PKG ON (FCT.PKG_SYS_ID = PKG.PKG_SYS_ID) LEFT OUTER JOIN BHEDW_PROD.DM.TB_DIM_ORG_SALE_CHNL SALE ON (FCT.SALE_CHNL_SYS_ID = SALE.SALE_CHNL_SYS_ID) LEFT OUTER JOIN BHEDW_PROD.DM.TB_DIM_RPT_CUST RPT_CUST ON (FCT.RPT_CUST_SYS_ID = RPT_CUST.RPT_CUST_SYS_ID) LEFT OUTER JOIN (SELECT DISTINCT CLM_AUD_NBR, CASE WHEN (LEN(STU.RVNU_CD_DTL_LN_NBR))=2 AND LEFT(STU.RVNU_CD_DTL_LN_NBR,1)=‘0’ THEN RIGHT(STU.RVNU_CD_DTL_LN_NBR,1) ELSE STU.RVNU_CD_DTL_LN_NBR END CLM_AUD_LN_NBR , SBSCR_NBR CLM_SRC_MBR_SBSCR_ID, MAX(BIL_PROC_CD) BIL_PROC_CD, MAX(BIL_RVNU_CD)BIL_RVNU_CD FROM BHEDW_PROD.STG.STG_GALA_UNET_UB92_REVENUE_CODE_STATISTICAL_SERVICE STU GROUP BY CLM_AUD_NBR, CASE WHEN (LEN(STU.RVNU_CD_DTL_LN_NBR))=2 AND LEFT(STU.RVNU_CD_DTL_LN_NBR,1)=‘0’ THEN RIGHT(STU.RVNU_CD_DTL_LN_NBR,1) ELSE STU.RVNU_CD_DTL_LN_NBR END , SBSCR_NBR ) STU ON (DIM.CLM_AUD_NBR = RTRIM(STU.CLM_AUD_NBR) ) AND (DIM.CLM_AUD_LN_NBR = STU.CLM_AUD_LN_NBR) AND (DIM.CLM_SRC_MBR_SBSCR_ID = STU.CLM_SRC_MBR_SBSCR_ID) AND (DIM.BIL_RVNU_CD = STU.BIL_RVNU_CD OR ‘0’||DIM.CLM_NET_PROC_CD = STU.BIL_RVNU_CD OR DIM.CLM_NET_PROC_CD = STU.BIL_RVNU_CD) LEFT OUTER JOIN BHEDW_PROD.DW.TB_PRV_PROVIDER DWP ON (FCT.PROV_SYS_ID = DWP.PROV_SYS_ID) WHERE DATEPART(qq,DIM.CLM_NET_DT_SRVC_FROM_DT)||’-’||DATEPART(yy,DIM.CLM_NET_DT_SRVC_FROM_DT) = DATEPART(qq,DATEADD(qq,-1,GETDATE()))||’-’|| CASE WHEN (DATEPART(qq,DATEADD(qq,-1,GETDATE()))) =4 THEN DATEPART(yy,DATEADD(yy,-1,GETDATE())) ELSE DATEPART(yy,GETDATE()) END AND DIM.CLM_NET_PRI_PAYE_CD_DESC NOT IN(‘PROVIDER’,‘VENDOR’) AND DIM.CLM_SRC_LEG_ENTY_NBR NOT IN (‘TRNA’,‘TRNS’) AND DIM.CLM_SRC_MBR_SYS_ID != ‘0’ AND CASE WHEN (DIM.CLM_SRC_COSM_DIV_CD) = ‘MPC’ AND DIM.CLM_SRC_LEG_ENTY_NBR||’-’||DIM.CLM_SRC_MBR_PRDCT_CD NOT IN (‘01358-01209’, ‘01358-01210’, ‘01358-01211’, ‘01358-01212’, ‘01358-00413’, ‘01358-00603’, ‘01417-01209’, ‘01417-01210’, ‘01417-01211’, ‘01417-01212’, ‘01417-00413’, ‘01417-00603’) THEN ‘MEDICAL’ ELSE ‘BH’ END = ‘BH’ GROUP BY DIM.CLM_NET_CLM_FM_TYP_CD, FCT.PROV_SYS_ID , PROV.SRC_PROV_ID, DIM.CLM_SRC_PROV_TAX_ID_NBR , DIM.CLM_SRC_PROV_CLM_NPI_ID , DIM.CLM_SRC_PROV_SRVC_CTY_NM, DIM.CLM_SRC_PROV_SRVC_ST_CD, DIM.CLM_SRC_PROV_SRVC_ZIP_CD, PROV.SRC_PROV_ID_SRC_CD, PROV.PROV_NM, CASE WHEN DIM.CLM_SRC_PROV_PAYE_NM =‘U’ THEN PROV.PROV_NM ELSE DIM.CLM_SRC_PROV_PAYE_NM END , DIM.CLM_NET_PRI_PAYE_CD_DESC, CASE WHEN (DWP.PROV_REIM_CATGY_NM) IS NULL OR (DWP.PROV_REIM_CATGY_NM) = ‘Unknown’ THEN DWP.PROV_FACL_TYP_DESC ELSE DWP.PROV_REIM_CATGY_NM END, PROV.PROV_DEG_CRDNTL_DESC, DIM.CLM_NET_PROV_NTWK_DERIV_CD, DIM.CLM_SRC_MBR_SYS_ID||’-’||DIM.CLM_SRC_MBR_SBSCR_ID , MEM.MBR_SYS_ID, DIM.CLM_SRC_MBR_SBSCR_ID, GRP.GRP_NM, MEM.MBR_NM_FST_TXT, MEM.MBR_NM_LST_TXT, MEM.BTH_DT, DIM.CLM_SRC_MBR_GDR_CD, MEM.SSN, CAST(DATEDIFF(DD, MEM.BTH_DT, DIM.CLM_NET_DT_SRVC_FROM_DT)/365.25 AS DECIMAL(10,0)) , MEM.ELIG_ADR_LN_1_TXT , MEM.ELIG_ADR_LN_2_TXT , MEM.ELIG_ADR_CITY_NM , MEM.ELIG_ST_ABBR_CD , LEFT(MEM.ELIG_ZIP_CD,5) , MEM.MAIL_ADR_LN_1_TXT , MEM.MAIL_ADR_LN_2_TXT , MEM.MAIL_ADR_CITY_NM , MEM.MAIL_ST_ABBR_CD , LEFT(MEM.MAIL_ZIP_CD,5) , DIM.CLM_NET_DIAG_MH_SA_CD, PKG.PRDCT_CMPNT_GRP_DESC, PKG.BH_PLN_TYP_DESC, GRP.ACCT_NM, PKG.BH_UBH_FUND_DESC, SALE.SALE_CHNL_NM, GRP.CUST_NM, GRP.GRP_ALT_ID, GRP.CBMS_ACCT_ID, DIM.CLM_SRC_MBR_PRDCT_CD, DIM.CLM_SRC_LEG_ENTY_NBR, DIM.CLM_AUD_SRC_SYS_CD, DIM.CLM_SRC_COSM_DIV_CD, DIM.CLM_AUD_SRC_ID, DIM.CLM_AUD_NBR, DIM.CLM_NET_PROC_TYP_CD, CASE WHEN DIM.CLM_NET_PROC_TYP_CD IN (‘U’,‘REV’) AND LEN(DIM.CLM_NET_PROC_CD)= 4 AND LEFT(DIM.CLM_NET_PROC_CD,1)=‘0’ THEN RIGHT(DIM.CLM_NET_PROC_CD,3) ELSE DIM.CLM_NET_PROC_CD END , DIM.CLM_NET_PROC_MOD_CD, DIM.CLM_NET_PROC_MOD_2_CD, DIM.CLM_NET_PROC_MOD_3_CD, DIM.CLM_NET_PROC_MOD_4_CD, DIM.CLM_NET_PROC_DESC, CASE WHEN (DIM.CLM_NET_PROC_TYP_CD) IN (‘REV’,‘U’) THEN DIM.CLM_NET_SEC_PROC_CD ELSE ‘’ END , CASE WHEN (DIM.CLM_NET_SEC_PROC_DESC) = ‘Unknown’ THEN ‘’ WHEN (DIM.CLM_NET_PROC_DESC = DIM.CLM_NET_SEC_PROC_DESC) THEN ‘’ ELSE DIM.CLM_NET_SEC_PROC_DESC END , DIM.CLM_NET_ENT_DIAG_REL_GRP_CD, DIM.CLM_NET_SYS_DIAG_REL_GRP_CD, CONVERT(VARCHAR(10),DATEADD(dd,-(DAY(DIM.CLM_NET_DT_SRVC_FROM_DT)-1),DIM.CLM_NET_DT_SRVC_FROM_DT),101), TRANS.CLM_ETL_DW_FROM_DT, DIM.CLM_NET_DT_SRVC_FROM_DT, DIM.CLM_NET_DT_SRVC_THRU_DT, DIM.CLM_NET_DT_RCVD_DT , DIM.CLM_NET_PL_OF_SRVC_AMA_CD , DIM.CLM_NET_DIAG_1_CD, DIM.CLM_NET_DIAG_1_CD_DESC , DIM.CLM_NET_DIAG_2_CD , DIM.CLM_NET_DIAG_2_CD_DESC , DIM.CLM_NET_DIAG_3_CD , DIM.CLM_NET_DIAG_3_CD_DESC HAVING SUM(FCT.CLM_NET_PD_AMT) > 0 UPDATE #CLAIMS F SET F.BIL_PROC_CD = S.BIL_PROC_CD, F.BIL_RVNU_CD = S.BIL_RVNU_CD FROM (SELECT F.CLM_AUD_NBR, S.BIL_PROC_CD, S.BIL_RVNU_CD INTO #STG FROM (SELECT CLM_AUD_NBR, SUM(PAID_AMT) PAID_AMT FROM #CLAIMS GROUP BY CLM_AUD_NBR) F INNER JOIN (SELECT CLM_AUD_NBR, MAX(BIL_PROC_CD)BIL_PROC_CD, MAX(BIL_RVNU_CD)BIL_RVNU_CD, SUM(ALLOC_SRC_PD_AMT) PAID_AMT FROM STG.STG_GALA_UNET_UB92_REVENUE_CODE_STATISTICAL_SERVICE GROUP BY CLM_AUD_NBR) S ON (F.CLM_AUD_NBR = S.CLM_AUD_NBR) AND (F.PAID_AMT = S.PAID_AMT)) S WHERE F.CLM_AUD_NBR = S.CLM_AUD_NBR UPDATE #CLAIMS F SET F.BIL_PROC_CD = S.BIL_PROC_CD, F.BIL_RVNU_CD = S.BIL_RVNU_CD FROM (SELECT F.CLM_AUD_NBR, S.BIL_PROC_CD, S.BIL_RVNU_CD FROM (SELECT CLM_AUD_NBR, SUM(PAID_AMT) PAID_AMT FROM #CLAIMS GROUP BY CLM_AUD_NBR) F INNER JOIN (SELECT DISTINCT CLM_AUD_NBR, BIL_PROC_CD, BIL_RVNU_CD, ALLOC_SRC_PD_AMT PAID_AMT FROM STG.STG_GALA_UNET_UB92_REVENUE_CODE_STATISTICAL_SERVICE ) S ON (F.CLM_AUD_NBR = S.CLM_AUD_NBR) AND (F.PAID_AMT = S.PAID_AMT)) S WHERE F.CLM_AUD_NBR = S.CLM_AUD_NBR AND ((F.BIL_RVNU_CD IS NULL AND F.BIL_PROC_CD IS NULL) OR (F.BIL_RVNU_CD = ’ ’ AND F.BIL_PROC_CD = ’ ')) SELECT [MBR_CK], [CLM_SRC_MBR_SBSCR_ID], [MBR_NM_FST_TXT], [MBR_NM_LST_TXT], [BTH_DT], COUNT(DISTINCT [CLM_AUD_NBR]) AS DISTINCT_CLMS, SUM([BILLED_AMT]) AS BILLED, SUM([PAID_AMT]) AS PAID INTO #SUMMARY FROM #CLAIMS GROUP BY [MBR_CK], [CLM_SRC_MBR_SBSCR_ID], [MBR_NM_FST_TXT], [MBR_NM_LST_TXT], [BTH_DT] ORDER BY SUM([PAID_AMT]) DESC SELECT * FROM #SUMMARY
6:39:14 PM Thread (20) Excel file currently in use, waiting 5 seconds for retry, up to 30 seconds.
6:39:19 PM Thread (20) Excel file currently in use, waiting 5 seconds for retry, up to 30 seconds.
6:39:24 PM Thread (20) Excel file currently in use, waiting 5 seconds for retry, up to 30 seconds.
6:39:29 PM Thread (20) Excel file currently in use, waiting 5 seconds for retry, up to 30 seconds.
6:39:34 PM Thread (20) Excel file currently in use, waiting 5 seconds for retry, up to 30 seconds.
6:39:39 PM Thread (20) Excel file currently in use, waiting 5 seconds for retry, up to 30 seconds.
6:39:44 PM Thread (20) User cancelled
6:39:44 PM Thread (19) DoneReadTreadWriter
6:39:44 PM Thread (19) DoneReadThreadWriter finally - adapter.RowsRead 10
6:39:44 PM Thread (3) Object SQL Query finished with error: The process cannot access the file ‘C:~Data\Reporting\HCQAI\2_Periodic_Reports\Top_10_MbrsPaid\Qtrly_Top 10 Mbr_Claims - TEST.xlsm’ because it is being used by another process.
6:39:44 PM Thread (3) at System.IO.__Error.WinIOError(Int32 errorCode, String maybeFullPath)
at System.IO.FileStream.Init(String path, FileMode mode, FileAccess access, Int32 rights, Boolean useRights, FileShare share, Int32 bufferSize, FileOptions options, SECURITY_ATTRIBUTES secAttrs, String msgPath, Boolean bFromProxy, Boolean useLongPath, Boolean checkHost)
at System.IO.FileStream…ctor(String path, FileMode mode, FileAccess access, FileShare share)
at Syncfusion.XlsIO.Implementation.Collections.WorkbooksCollection.Open(String fileName, ExcelOpenType openType, ExcelVersion version, ExcelParseOptions options)
at Syncfusion.XlsIO.Implementation.Collections.WorkbooksCollection.Open(String fileName, ExcelOpenType openType)
at Quest.Toad.ImportExport.Writers.WriterExcel.TryOpenExcelFile()
at Quest.Toad.ImportExport.Writers.WriterExcel.TryOpenExcelFile()
at Quest.Toad.ImportExport.Writers.WriterExcel.TryOpenExcelFile()
at Quest.Toad.ImportExport.Writers.WriterExcel.TryOpenExcelFile()
at Quest.Toad.ImportExport.Writers.WriterExcel.TryOpenExcelFile()
at Quest.Toad.ImportExport.Writers.WriterExcel.TryOpenExcelFile()
at Quest.Toad.ImportExport.Writers.WriterExcel.TryOpenExcelFile()
at Quest.Toad.ImportExport.Writers.WriterExcel.InitializeFileLocation()
at Quest.Toad.ImportExport.Writers.WriterExcel.WriteHeader(Object tableObject)
at Quest.Toad.ImportExport.ExportObjectData.InitializeSchema()
6:39:44 PM Thread (3) Export Finished [6/26/2018 6:39:44 PM]


#2

I recommend try to use beta build and try reproduce this bug on this version.

In new version is a lot of changes. For example new component for excel and fixed some bugs.


#3

Unfortunately, my company does not allow downloads of beta versions of software. We have a very stringent process to obtain “company approved, licensed” software.

____________________________

Elizabeth Shields ** |****
Optum**

Manager – Data Analysis, Behavioral and Physical Health Operations – Program & Network Integrity

612-642-7651

Elizabeth.Shields@Optum.com

www.optum.com

Our ** United Culture. The way forward.**


Integrity ■
Compassion

Relationships
■ Innovation

Performance

From: Petr.Hodovsky [mailto:bounce-PetrHodovsky@toadworld.com]

Sent: Wednesday, June 27, 2018 5:18 AM

To: toaddatapoint@toadworld.com

Subject: RE: [Toad Data Point - Discussion Forum] Export to Excel Macro Enabled Workbook fails and Cannot open Excel Application without rebooting

RE: Export to Excel Macro Enabled Workbook fails and Cannot open Excel Application without rebooting

Reply by Petr.Hodovsky

I recommend try to use beta build and try reproduce this bug on this version.

In new version is a lot of changes. For example new component for excel and fixed some bugs.

To reply, please reply-all to this email.

Stop receiving emails on this subject.

Or
Unsubscribe from Toad Data Point Forum
notifications altogether.

Toad Data Point - Discussion Forum

Flag
this post as spam/abuse.

This e-mail, including attachments, may include confidential and/or

proprietary information, and may be used only by the person or entity

to which it is addressed. If the reader of this e-mail is not the intended

recipient or his or her authorized agent, the reader is hereby notified

that any dissemination, distribution or copying of this e-mail is

prohibited. If you have received this e-mail in error, please notify the

sender by replying to this message and delete this e-mail immediately.


#4

I recognize the error stack and as Petr mentions we have done a lot of changes for support of Macros. Are you able to download the latest Trial software? The fixes are in the GA release.