VERSION 5.00
Begin VB.Form ExportExcelForm 
   Caption         =   "Export Excel"
   ClientHeight    =   6750
   ClientLeft      =   60
   ClientTop       =   345
   ClientWidth     =   6915
   ControlBox      =   0   'False
   LinkTopic       =   "Form1"
   LockControls    =   -1  'True
   MaxButton       =   0   'False
   ScaleHeight     =   6750
   ScaleWidth      =   6915
   StartUpPosition =   2  'CenterScreen
   Begin Project1.ArmExcel ArmExcel1 
      Height          =   6675
      Left            =   0
      TabIndex        =   0
      Top             =   0
      Width           =   6855
      _ExtentX        =   12091
      _ExtentY        =   11774
   End
End
Attribute VB_Name = "ExportExcelForm"
Attribute VB_GlobalNameSpace = False
Attribute VB_Creatable = False
Attribute VB_PredeclaredId = True
Attribute VB_Exposed = False
Option Explicit

'what is new
'2.6.4 bugfix: Cap_ProjectProbability included in MultiKeyRequest (JN)
'2.6.6 Added export of project's Comment field (JN)

' **************************************************************************************************
' **************************************** USER DEFINED ERRORS *************************************
' **************************************************************************************************

Private Const SEP1 As String = "®"
Private Const SEP2 As String = "©"
Private Const SEP As String = SEP1 + SEP2
Private Const C_APPNAME As String = "EXPORTEXCEL" ' for error log

Private Enum ArmErr
    DBCnxFailed = vbObjectError + 1 ' Unable to connect to the database
    CPTAlreadyInitialized = vbObjectError + 2   ' We try to initialize a component that is already initialized
    CPTNotInitialized = vbObjectError + 3       ' We try to use or free that is not initialized yet
    InvalidArgument = vbObjectError + 4
    PropertyNotSet = vbObjectError + 5
    CompFncFailed = vbObjectError + 6          ' when component function fail
    QuietException = vbObjectError + 7              ' do not display error message
    WarMsgSelectRow = vbObjectError + 8
    SQLBadRowAffectedCount = vbObjectError + 9   ' A SQL request has not affected the expected rowcount (ex: one Update do nothing)
End Enum

Private mo_ArmDb As Object
Private ms_UID As String
Private ms_LanguageCode As String
Private ms_ConnectString As String
Private mb_use_framework_connection As Boolean

Private met_DataType As DataTypeEnum

Private mo_Grid As ArmGrid

' WORKING CURSORS FOR EXPORTREFPROJECT
Private ml_cursorExCountries As Long
Private ml_cursorShapes As Long
Private ml_cursorPhotographer As Long


Public Property Let DataType(aet_DataType As DataTypeEnum)

    #If CapDebug Then
        If Not go_Trace Is Nothing Then go_Trace.WriteTraceProc True, "ExportExcelForm:DataType_Let", "aet_DataType=" & aet_DataType
    #End If
    CapMouseOff
    
    met_DataType = aet_DataType
    
    If mo_Grid.SelectedCount = 0 Then
        ArmExcel1.SelectedRowsOnlyEnabled = False
        ArmExcel1.SelectedRowsOnly = False
    End If

    Select Case aet_DataType
        Case project
            ExportProject
        Case action
            ExportAction
        Case Customer
            ExportCustomer
        Case contact
            ExportContact
        Case product
            ExportProduct
        Case project_search
            ExportProject_Search
        Case customer_search
            ExportCustomer_Search
        Case Contact_Search
            ExportContact_Search
        Case Action_Search
            ExportAction_Search
        Case Product_Search
            ExportProduct_Search
        Case Reference_Project
            Call ExportProjectReference
    End Select
    
Trace_End:
    #If CapDebug Then
        If Not go_Trace Is Nothing Then go_Trace.WriteTraceProc False, "ExportExcelForm:DataType_Let"
    #End If
    CapMouseOn
End Property

Public Property Set ArmDb(ByRef ao_local_connection As Object)
    #If CapDebug Then
        If Not go_Trace Is Nothing Then go_Trace.WriteTraceProc True, "ExportExcelForm:ArmDb_Set"
    #End If
    CapMouseOff
    
    If Not (ao_local_connection Is Nothing) Then
        Set mo_ArmDb = ao_local_connection
        mb_use_framework_connection = True
        Set ArmExcel1.ArmDb = mo_ArmDb
    End If
Trace_End:
    #If CapDebug Then
        If Not go_Trace Is Nothing Then go_Trace.WriteTraceProc False, "ExportExcelForm:ArmDb_Set"
    #End If
    CapMouseOn
End Property

Property Let ConnectString(as_ConnectString As String)
    
    #If CapDebug Then
        If Not go_Trace Is Nothing Then go_Trace.WriteTraceProc True, "ExportExcelForm:ConnectString_Let", "as_ConnectString=" & as_ConnectString
    #End If
    CapMouseOff
    
    ms_UID = ConnectionStringValidation(as_ConnectString)
    If ms_UID = "" Then GoTo Trace_End
    
    ms_ConnectString = as_ConnectString
    
    ArmExcel1.ConnectString = ms_ConnectString
    
Trace_End:
    #If CapDebug Then
        If Not go_Trace Is Nothing Then go_Trace.WriteTraceProc False, "ExportExcelForm:ConnectString_Let"
    #End If
    CapMouseOn
End Property

Property Let Language(ALng As String)
    #If CapDebug Then
        If Not go_Trace Is Nothing Then go_Trace.WriteTraceProc True, "ExportExcelForm:Language_Let", "ALng=" & ALng
    #End If
    
    ms_LanguageCode = ALng
    
    ArmExcel1.Language_Code = ms_LanguageCode
    
    #If CapDebug Then
        If Not go_Trace Is Nothing Then go_Trace.WriteTraceProc False, "ExportExcelForm:Language_Let"
    #End If
End Property

Public Property Set Grid(ao_grid As ArmGrid)
    #If CapDebug Then
        If Not go_Trace Is Nothing Then go_Trace.WriteTraceProc True, "ExportExcelForm:Grid_Set"
    #End If

    Set mo_Grid = ao_grid

    #If CapDebug Then
        If Not go_Trace Is Nothing Then go_Trace.WriteTraceProc False, "ExportExcelForm:Grid_Set"
    #End If
End Property

Public Sub Load_A_Com()
Dim ls_LocalID As String

    #If CapDebug Then
        If Not go_Trace Is Nothing Then go_Trace.WriteTraceProc True, "ExportExcelForm:Load_A_Com"
    #End If
    CapMouseOff

    ArmExcel1.Language_Request = "EXEC language_lst"
    ArmExcel1.Load_A_Com
    ArmExcel1.LoadLanguages
    ArmExcel1.Selected_Language_Code = ms_LanguageCode
    
    
    
    
    'GetCharSetFromCodePage (GetDefaultConfigCode("Capture_Cfg", "Charset"))
    
    'ls_LocalID = GetLCIDFromCodePage(GetCodePageFromLanguage(mo_ArmDb, ms_LanguageCode)) ' GetDefaultConfigCode("Capture_Cfg", "LocalID")
    ls_LocalID = GetDefaultConfigCode("Capture_Cfg", "LocalID")
    If ls_LocalID <> "" Then
        ArmExcel1.LocalID = CInt(ls_LocalID)
    Else
        ArmExcel1.LocalID = 0
    End If
    
Trace_End:
    #If CapDebug Then
        If Not go_Trace Is Nothing Then go_Trace.WriteTraceProc False, "ExportExcelForm:Load_A_Com"
    #End If
    CapMouseOn
End Sub

Public Sub Unload_A_Com()
    #If CapDebug Then
        If Not go_Trace Is Nothing Then go_Trace.WriteTraceProc True, "ExportExcelForm:Unload_A_Com"
    #End If
    CapMouseOff

    ArmExcel1.Unload_A_Com
    
Trace_End:
    #If CapDebug Then
        If Not go_Trace Is Nothing Then go_Trace.WriteTraceProc False, "ExportExcelForm:Unload_A_Com"
    #End If
    CapMouseOn
End Sub

Private Sub ArmExcel1_BeforeRowExport(ByVal av_Fields As Variant, av_data As Variant, ByVal as_Language_Code As String)
On Error GoTo ErrHandler
    Select Case met_DataType
        Case Reference_Project
            ' update data in the array
            Call UpdateProjectReferenceFields(av_Fields, av_data, as_Language_Code)
    End Select
    Exit Sub
ErrHandler:
    Call ErrorMessage("ArmExcel1_BeforeRowExport()")
End Sub

Private Sub ArmExcel1_ButtonPressed(as_Name As String)
On Error GoTo Trace_Err
Dim ll_CurrentLine As Long

    #If CapDebug Then
        If Not go_Trace Is Nothing Then go_Trace.WriteTraceProc True, "ExportExcelForm:ArmExcel1_ButtonPressed", "as_Name=" & as_Name
    #End If
    CapMouseOff
    
    Select Case as_Name
    Case "cmd_Export"
        If ArmExcel1.SelectedRowsOnly = False Then
            If ASC_SendMessage(mo_ArmDb, ms_LanguageCode, 917, "#Warning: this solution can take long time. Would you really export all the grid ?", , vbYesNo) = vbYes Then
                'ArmExcel1.SelectedRowsOnly = True
                ll_CurrentLine = mo_Grid.Row
                mo_Grid.LastLine
                If ll_CurrentLine = -1 Then
                    mo_Grid.FirstLine
                Else
                    mo_Grid.Row = ll_CurrentLine
                End If
                ArmExcel1.MultiKey = mo_Grid.MultiKey
                If ArmExcel1.Export() Then
                    Call Unload(Me)
                End If
            End If
        Else
            ArmExcel1.MultiKey = mo_Grid.MultiSelectedKey
            If ArmExcel1.Export() Then
                Call Unload(Me)
            End If
        End If
  Case "cmd_Cancel"
    Call Unload(Me)
  End Select
  
Trace_End:
    #If CapDebug Then
        If Not go_Trace Is Nothing Then go_Trace.WriteTraceProc False, "ExportExcelForm:ArmExcel1_ButtonPressed"
    #End If
    If met_DataType = Reference_Project Then
    ' close all working cursors
      If ml_cursorExCountries > 0 Then
          Call mo_ArmDb.Close(ml_cursorExCountries)
          ml_cursorExCountries = 0
      End If
      If ml_cursorShapes > 0 Then
          Call mo_ArmDb.Close(ml_cursorShapes)
          ml_cursorShapes = 0
      End If
      If ml_cursorPhotographer > 0 Then
          Call mo_ArmDb.Close(ml_cursorPhotographer)
          ml_cursorPhotographer = 0
      End If
    End If
    
    CapMouseOn
    Exit Sub
    
Trace_Err:
    If Not go_Trace Is Nothing Then go_Trace.WriteTraceError Err.Number, Err.Description, "ExportExcelForm:ArmExcel1_ButtonPressed"
  ' close all working cursors
    If ml_cursorExCountries > 0 Then
        Call mo_ArmDb.Close(ml_cursorExCountries)
        ml_cursorExCountries = 0
    End If
    If ml_cursorShapes > 0 Then
        Call mo_ArmDb.Close(ml_cursorShapes)
        ml_cursorShapes = 0
    End If
    If ml_cursorPhotographer > 0 Then
        Call mo_ArmDb.Close(ml_cursorPhotographer)
        ml_cursorPhotographer = 0
    End If
    CapMouseOn
End Sub

Public Sub ExportProject()
    
    #If CapDebug Then
        If Not go_Trace Is Nothing Then go_Trace.WriteTraceProc True, "ExportExcelForm:ExportProject"
    #End If
    CapMouseOff
    
    ArmExcel1.User_ID = ms_UID
    ArmExcel1.InsertTempKeyCount = 10
    
    ArmExcel1.CreateTempTableRequest = "CREATE TABLE [#Cap_ExcelExport] ([Code] [int] Identity(1,1),[User_ID] [varchar] (30) NULL ,[CapKey1] [varchar] (20) NULL ,    [CapKey2] [varchar] (20) NULL) ON [PRIMARY]"
    ArmExcel1.DropTempTableRequest = "DROP TABLE [#Cap_ExcelExport]"
    ArmExcel1.InsertTempKeyRequest = "exec Cap_ExcelExport_ins '$User_ID$','$0$','$1$','$2$','$3$','$4$', '$5$', '$6$', '$7$', '$8$', '$9$'"
    
    ArmExcel1.MaxPreselections = 10
    
    ArmExcel1.PreSelRequest_Ins = "Cap_ExportExcelPreSel_ins '$User_ID$','PROJECT','$PreSelName$','$PreSelFields$'"
    ArmExcel1.PreSelRequest_Del = "Cap_ExportExcelPreSel_del '$User_ID$','PROJECT','$PreSelName$'"
    ArmExcel1.PreSelRequest_Sel = "Cap_ExportExcelPreSel_lst '$User_ID$','PROJECT'"
' taks 299.2 begin update request to join 3 new tables
    ArmExcel1.MultiKeyRequest = "SELECT $SelectFields$ " & vbCrLf & _
                                "FROM #Cap_ExcelExport EE " & vbCrLf & _
                                "JOIN Sales_Project SP ON SP.Sp_CapKey = EE.CapKey1 AND SP.Language_Code='E' " & vbCrLf & _
                                "LEFT JOIN Cap_ProjectDescription PD ON SP.PD_code = PD.PD_code AND PD.Language_code = '$Language_Code$' " & vbCrLf & _
                                "LEFT JOIN Countries CT ON SP.CT_Code = CT.CT_Code And CT.Language_Code = '$Language_Code$'" & vbCrLf & _
                                "LEFT JOIN Cap_County CTY ON SP.CTY_Code = CTY.CTY_Code AND CTY.CT_Code = CT.CT_Code AND CTY.Language_Code = '$Language_Code$'" & vbCrLf & _
                                "LEFT JOIN Cap_NewRenovation NR ON SP.NR_code = NR.NR_code AND NR.Language_code = '$Language_Code$' " & vbCrLf & _
                                "LEFT JOIN Cap_SalesStatus SS ON SP.SS_code = SS.SS_code AND SS.Language_code = '$Language_Code$' " & vbCrLf & _
                                "LEFT JOIN Cap_ProjectType PT ON SP.PT_code = PT.PT_code AND PT.Language_code = '$Language_Code$' " & vbCrLf & _
                                "LEFT JOIN Cap_ProjectPriority PP ON SP.PP_code = PP.PP_code AND PP.Language_code = '$Language_Code$' " & vbCrLf & _
                                "LEFT JOIN Cap_StratAccount SA ON SP.SA_code = SA.SA_code AND SA.Language_code = '$Language_Code$' " & vbCrLf & _
                                "LEFT JOIN Cap_ProjectRep PR1 ON SP.SP_CapKey=PR1.SP_CapKey AND PR1.RR_Code=2 -- Customer role " & vbCrLf & _
                                "LEFT JOIN Sales_Reps SR1 ON PR1.SR_Code=SR1.SR_Code AND SR1.Language_Code='$Language_Code$' " & vbCrLf & _
                                "LEFT JOIN Cap_RepRole RR1 ON RR1.RR_Code= PR1.RR_Code AND RR1.Language_Code='$Language_Code$' " & vbCrLf & _
                                "LEFT JOIN Cap_ProjectRep PR2 ON SP.SP_CapKey=PR2.SP_CapKey AND PR2.RR_Code=1 -- Specifier role " & vbCrLf & _
                                "LEFT JOIN Sales_Reps SR2 ON PR2.SR_Code=SR2.SR_Code AND SR2.Language_Code='$Language_Code$' " & vbCrLf & _
                                "LEFT JOIN Cap_RepRole RR2 ON RR2.RR_Code= PR2.RR_Code AND RR2.Language_Code='$Language_Code$' " & vbCrLf & _
                                "LEFT JOIN Cap_SAType SAT ON SP.SAT_Code= SAT.SAT_Code AND SAT.Language_Code='$Language_Code$' " & vbCrLf & _
                                "LEFT JOIN Cap_ProjectLevel LEV ON SP.LEV_Code= LEV.LEV_Code AND LEV.Language_Code='$Language_Code$' " & vbCrLf & _
                                "LEFT JOIN Cap_SystemSolutionType SST ON SP.SST_Code= SST.SST_Code AND SST.Language_Code='$Language_Code$' " & vbCrLf & _
                                "LEFT JOIN Cap_ProjectProbability PPB ON SP.spare3_real = PPB.PPB_ID AND PPB.Language_Code='$Language_Code$' " & vbCrLf & _
                                "LEFT JOIN Cap_Memo CM ON CM.spare1_varchar = SP.SP_CapKey AND CM.Spare3_real = 1 " & vbCrLf & _
                                "WHERE EE.USER_ID = '$User_ID$' " & vbCrLf & _
                                "ORDER BY EE.Code "
' taks 299.2 end
                        
    'ArmExcel1.SpecialFields = ""
    Call ArmExcel1.LoadConstants(EXptSQL, "cap_ExportExcel_Fields_lst2 'Cap_ExportExcel_Project','ArmExcel','$Language_code$'", EXctFields)
    
    Call ArmExcel1.LoadConstants(EXptSQL, "screen_csts_lst2 'Cap_ExportExcel_Select', 'ArmExcel', '$Language_code$'", EXctControl)
    
    Call ArmExcel1.LoadConstants(EXptStatic, Join(Array("Enter the name of the preselection (only 15 chars) :", _
                                                 "This name is already used, please choose another", _
                                                 "Are you sure to delete this item : "), CH_LDELIMIT), EXctMessages)
    Call ArmExcel1.LoadPreselections

Trace_End:
    #If CapDebug Then
        If Not go_Trace Is Nothing Then go_Trace.WriteTraceProc False, "ExportExcelForm:ExportProject"
    #End If
    CapMouseOn
    Exit Sub
    
Trace_Err:
    If Not go_Trace Is Nothing Then go_Trace.WriteTraceError Err.Number, Err.Description, "ExportExcelForm:ExportProject"
    CapMouseOn
End Sub

Public Sub ExportAction()
    
    #If CapDebug Then
        If Not go_Trace Is Nothing Then go_Trace.WriteTraceProc True, "ExportExcelForm:ExportAction"
    #End If
    CapMouseOff
    
    ArmExcel1.User_ID = ms_UID
    ArmExcel1.InsertTempKeyCount = 9
    
    ArmExcel1.CreateTempTableRequest = "CREATE TABLE [#Cap_ExcelExport] ([Code] [int] Identity(1,1),[User_ID] [varchar] (30) NULL ,[CapKey1] [varchar] (20) NULL ,    [CapKey2] [varchar] (20) NULL, [CapKey3] [varchar] (20) NULL) ON [PRIMARY]"
    ArmExcel1.DropTempTableRequest = "DROP TABLE [#Cap_ExcelExport]"
    ArmExcel1.InsertTempKeyRequest = "exec Cap_ExcelExport_ins2 '$User_ID$','$0$','$1$','$2$','$3$','$4$', '$5$', '$6$', '$7$', '$8$'"
    
    ArmExcel1.MaxPreselections = 10
    
    ArmExcel1.PreSelRequest_Ins = "Cap_ExportExcelPreSel_ins '$User_ID$','ACTION','$PreSelName$','$PreSelFields$'"
    ArmExcel1.PreSelRequest_Del = "Cap_ExportExcelPreSel_del '$User_ID$','ACTION','$PreSelName$'"
    ArmExcel1.PreSelRequest_Sel = "Cap_ExportExcelPreSel_lst '$User_ID$','ACTION'"
    '$Language_Code$'
    ' task 299.3 fix
    ArmExcel1.MultiKeyRequest = "SELECT $SelectFields$ " & vbCrLf & _
                                "FROM #Cap_ExcelExport EE " & vbCrLf & _
                                "INNER JOIN Cap_Action CA ON CA.AC_Code = EE.Capkey1 AND CA.Language_Code = 'E'" & vbCrLf & _
                                "LEFT JOIN Cap_Memo CM ON CM.spare1_varchar=CA.AC_code AND CM.spare3_real=2 AND CM.Language_code='E'" & vbCrLf & _
                                "INNER JOIN Cap_ActionType CAT ON CAT.AT_Code = CA.AT_Code AND CAT.Language_Code = '$Language_Code$'" & vbCrLf & _
                                "INNER JOIN Cap_ActionStatus CAS ON CAS.AS_Code = CA.AS_Code AND CAS.Language_Code = '$Language_Code$'" & vbCrLf & _
                                "INNER JOIN Cap_ActionReason CAR ON CAR.AR_Code = CA.AR_Code AND CAR.Language_Code = '$Language_Code$'" & vbCrLf & _
                                "LEFT OUTER JOIN Cap_ActionContact CAC ON CAC. AC_Code = CA.AC_Code" & vbCrLf & _
                                "LEFT OUTER JOIN Cap_Contact CNT ON CNT.CNT_Code = CAC.CNT_Code" & vbCrLf & _
                                "LEFT OUTER JOIN Unit_Of_Measure UOM ON UOM.UM_Code = CA.UM_Code AND UOM.Language_Code = '$Language_Code$'" & vbCrLf & _
                                "INNER JOIN Claim_Customer CC ON CC.CCU_Capkey = CA.CCU_Capkey" & vbCrLf & _
                                "LEFT OUTER JOIN Cap_ActionProject CAP ON CAP.AC_Code = CA.AC_Code" & vbCrLf & _
                                "LEFT OUTER JOIN Sales_Project SP ON SP.SP_Capkey = CAP.SP_Capkey" & vbCrLf & _
                                "LEFT OUTER JOIN Cap_SalesStatus CSS ON CSS.SS_Code = SP.SS_Code AND CSS.Language_Code = '$Language_Code$'" & vbCrLf & _
                                "INNER JOIN Countries C ON C.CT_Code = CC.CT_Code AND C.Language_Code = '$Language_Code$'" & vbCrLf & _
                                "INNER JOIN Cap_CompanyRep CCR ON CCR.CCU_Capkey = CC.CCU_Capkey" & vbCrLf & _
                                "INNER JOIN Cap_RepRole CRR ON CRR.RR_Code = CCR.RR_Code AND CRR.BU_GROUPING LIKE 'X%' AND CRR.Language_Code = '$Language_Code$'" & vbCrLf & _
                                "INNER JOIN Sales_Reps SR ON SR.SR_Code = CCR.SR_Code AND SR.Language_Code = 'E'" & vbCrLf & _
                                "$SpecialJoin1$" & vbCrLf & _
                                "WHERE EE.USER_ID = '$User_ID$' " & vbCrLf & _
                                "ORDER BY EE.Code "
                                
                                
    Dim lr_SampleJoin As Variant
    ' BEGIN T298.WP7
    lr_SampleJoin = Array("ESG.SDG_Desc®©ASD.SD_Code®©SD.SD_Desc®©ASD.ASD_Qty®©ASD.HandOrFullFlag®©PE.PE_Desc®©ASD.ASD_Comment", "", "LEFT OUTER JOIN Cap_ActionSampleDoc ASD ON ASD.AC_Code = CA.AC_Code " & vbCrLf & _
                                "LEFT OUTER JOIN Cap_SampleDoc SD ON SD.SD_Code = ASD.SD_Code AND SD.Language_Code = '$Language_Code$'" & vbCrLf & _
                                "LEFT JOIN Cap_E_SampleDoc ES ON ES.ESD_Code = SD.ESD_Code AND ES.Language_Code = 'E' " & _
                                "LEFT JOIN Cap_E_SampleDocGroup ESG ON ESG.SDG_code = ES.SDG_code  AND ESG.language_code='$Language_code$' " & _
                                "LEFT OUTER JOIN Products_Edges PE ON PE.PE_Code = ASD.PE_Code AND PE.Language_Code = '$Language_Code$'" & vbCrLf, "")
    ' END T298.WP7
    ArmExcel1.SpecialFields = Array(lr_SampleJoin)
                         
    Call ArmExcel1.LoadConstants(EXptSQL, "cap_ExportExcel_Fields_lst2 'Cap_ExportExcel_Action','ArmExcel','$Language_code$'", EXctFields)
    
    Call ArmExcel1.LoadConstants(EXptSQL, "screen_csts_lst2 'Cap_ExportExcel_Select', 'ArmExcel', '$Language_code$'", EXctControl)
    
    Call ArmExcel1.LoadConstants(EXptStatic, Join(Array("#Enter the name of the preselection (only 15 chars) :", _
                                                 "This name is already used, please choose another", _
                                                 "Are you sure to delete this item : "), CH_LDELIMIT), EXctMessages)
    Call ArmExcel1.LoadPreselections

Trace_End:
    #If CapDebug Then
        If Not go_Trace Is Nothing Then go_Trace.WriteTraceProc False, "ExportExcelForm:ExportAction"
    #End If
    CapMouseOn
    Exit Sub
    
Trace_Err:
    If Not go_Trace Is Nothing Then go_Trace.WriteTraceError Err.Number, Err.Description, "ExportExcelForm:ExportAction"
    CapMouseOn
End Sub

Public Sub ExportCustomer()
    
    #If CapDebug Then
        If Not go_Trace Is Nothing Then go_Trace.WriteTraceProc True, "ExportExcelForm:ExportCustomer"
    #End If
    CapMouseOff
    
    ArmExcel1.User_ID = ms_UID
    ArmExcel1.InsertTempKeyCount = 10
    
    ArmExcel1.CreateTempTableRequest = "CREATE TABLE [#Cap_ExcelExport] ([Code] [int] Identity(1,1),[User_ID] [varchar] (30) NULL ,[CapKey1] [varchar] (20) NULL ,    [CapKey2] [varchar] (20) NULL) ON [PRIMARY]"
    ArmExcel1.DropTempTableRequest = "DROP TABLE [#Cap_ExcelExport]"
    ArmExcel1.InsertTempKeyRequest = "exec Cap_ExcelExport_ins '$User_ID$','$0$','$1$','$2$','$3$','$4$', '$5$', '$6$', '$7$', '$8$', '$9$'"
    
    ArmExcel1.MaxPreselections = 10
    
    ArmExcel1.PreSelRequest_Ins = "Cap_ExportExcelPreSel_ins '$User_ID$','CUSTOMER','$PreSelName$','$PreSelFields$'"
    ArmExcel1.PreSelRequest_Del = "Cap_ExportExcelPreSel_del '$User_ID$','CUSTOMER','$PreSelName$'"
    ArmExcel1.PreSelRequest_Sel = "Cap_ExportExcelPreSel_lst '$User_ID$','CUSTOMER'"
' IMPLEMENTED PROCESS T298.1-A.PR_004
    ArmExcel1.MultiKeyRequest = "SELECT $SelectFields$ " & vbCrLf & _
                                "FROM #Cap_ExcelExport EE " & vbCrLf & _
                                "INNER JOIN Claim_Customer CC ON CC.CCU_Capkey = EE.Capkey1 AND CC.Language_Code = 'E' AND CC.SYS_Code = 11" & vbCrLf & _
                                "INNER JOIN Cap_County CTY ON CTY.CTY_Code = CC.CTY_Code AND CTY.Language_Code = '$Language_Code$'" & vbCrLf & _
                                "INNER JOIN Countries CT ON CT.CT_Code = CC.CT_Code AND CT.Language_Code = '$Language_Code$'" & vbCrLf & _
                                "INNER JOIN Cap_CompanyPriority CP ON CP.CP_Code = CC.CP_Code AND CP.Language_Code ='$Language_Code$'" & vbCrLf & _
                                "INNER JOIN Cap_CompanyType CCT ON CCT.CCT_Code = CC.CCT_Code AND CCT.Language_Code = '$Language_Code$'" & vbCrLf & _
                                "INNER JOIN Cap_CompanyRep CCR ON CCR.CCU_Capkey = CC.CCU_Capkey" & vbCrLf & _
                                "INNER JOIN Cap_RepRole CRR ON CRR.RR_Code = CCR.RR_Code AND CRR.BU_GROUPING LIKE 'X%' AND CRR.Language_Code=  '$Language_Code$'" & vbCrLf & _
                                "INNER JOIN Sales_Reps SR ON SR.SR_Code = CCR.SR_Code AND SR.Language_Code = 'E'" & vbCrLf & _
                                "LEFT OUTER JOIN Cap_Contact CNT ON CNT.CNT_Code = CC.CCU_PrimCNT " & vbCrLf & _
                                "LEFT OUTER JOIN Cap_CustomerLoyalty LOY ON LOY.LOY_Code = CC.LOY_Code AND LOY.Language_Code = '$Language_Code$'" & vbCrLf & _
                                "    LEFT OUTER JOIN Cap_Title CTT ON CTT.TIT_Code = CNT.TIT_Code AND CTT.Language_Code = '$Language_Code$' " & vbCrLf & _
                                "$SpecialJoin1$" & vbCrLf & _
                                "WHERE EE.USER_ID = '$User_ID$' " & vbCrLf & _
                                "ORDER BY EE.Code "
' END PROCESS T298.1-A.PR_004
                         
    Dim lr_AttrJoin As Variant
    lr_AttrJoin = Array("CTTA.CTA_Desc", "", "LEFT OUTER JOIN Cap_CustomerAttribute CTA ON CTA.CCU_Capkey = CC.CCU_Capkey" & vbCrLf & _
                                 "LEFT OUTER JOIN Cap_CompanyTypeAttribute CTTA ON CTTA.CTA_Code = CTA.CTA_Code AND CTTA.Language_Code = '$Language_Code$'" & vbCrLf, "")
    ArmExcel1.SpecialFields = Array(lr_AttrJoin)
    Call ArmExcel1.LoadConstants(EXptSQL, "cap_ExportExcel_Fields_lst2 'Cap_ExportExcel_Customer','ArmExcel','$Language_code$'", EXctFields)
    
    Call ArmExcel1.LoadConstants(EXptSQL, "screen_csts_lst2 'Cap_ExportExcel_Select', 'ArmExcel', '$Language_code$'", EXctControl)
    
    Call ArmExcel1.LoadConstants(EXptStatic, Join(Array("Enter the name of the preselection (only 15 chars) :", _
                                                 "This name is already used, please choose another", _
                                                 "Are you sure to delete this item : "), CH_LDELIMIT), EXctMessages)
    Call ArmExcel1.LoadPreselections

Trace_End:
    #If CapDebug Then
        If Not go_Trace Is Nothing Then go_Trace.WriteTraceProc False, "ExportExcelForm:ExportCustomer"
    #End If
    CapMouseOn
    Exit Sub
    
Trace_Err:
    If Not go_Trace Is Nothing Then go_Trace.WriteTraceError Err.Number, Err.Description, "ExportExcelForm:ExportCustomer"
    CapMouseOn
End Sub

Public Sub ExportContact()
    
    #If CapDebug Then
        If Not go_Trace Is Nothing Then go_Trace.WriteTraceProc True, "ExportExcelForm:ExportContact"
    #End If
    CapMouseOff
    
    ArmExcel1.User_ID = ms_UID
    ArmExcel1.InsertTempKeyCount = 9
    
    ArmExcel1.CreateTempTableRequest = "CREATE TABLE [#Cap_ExcelExport] ([Code] [int] Identity(1,1),[User_ID] [varchar] (30) NULL ,[CapKey1] [varchar] (20) NULL ,    [CapKey3] [varchar] (20) NULL) ON [PRIMARY]"
    ArmExcel1.DropTempTableRequest = "DROP TABLE [#Cap_ExcelExport]"
    ArmExcel1.InsertTempKeyRequest = "exec Cap_ExcelExport_ins2 '$User_ID$','$0$','$1$','$2$','$3$','$4$', '$5$', '$6$', '$7$', '$8$'"
    
    ArmExcel1.MaxPreselections = 10
    
    ArmExcel1.PreSelRequest_Ins = "Cap_ExportExcelPreSel_ins '$User_ID$','CONTACT','$PreSelName$','$PreSelFields$'"
    ArmExcel1.PreSelRequest_Del = "Cap_ExportExcelPreSel_del '$User_ID$','CONTACT','$PreSelName$'"
    ArmExcel1.PreSelRequest_Sel = "Cap_ExportExcelPreSel_lst '$User_ID$','CONTACT'"
    
    ArmExcel1.MultiKeyRequest = "SELECT $SelectFields$ " & vbCrLf & _
                                "FROM #Cap_ExcelExport EE " & vbCrLf & _
                                "INNER JOIN Cap_Contact CNT ON CNT.CNT_Code = EE.Capkey1 AND CNT.language_Code = 'E'" & vbCrLf & _
                                "INNER JOIN Cap_Title TIT ON TIT.TIT_Code = CNT.TIT_Code AND TIT.Language_Code = '$Language_code$'" & vbCrLf & _
                                "INNER JOIN Cap_Job JOB ON JOB.JOB_Code = CNT.JOB_Code AND JOB.Language_Code = '$Language_code$'" & vbCrLf & _
                                "INNER JOIN Claim_Customer CC ON CC.CCU_Capkey = CNT.CCU_Capkey AND CC.SYS_Code = 11" & vbCrLf & _
                                "INNER JOIN Cap_County CTY ON CTY.CTY_Code = CC.CTY_Code AND CTY.Language_Code = '$Language_code$'" & vbCrLf & _
                                "INNER JOIN Countries CT ON CT.CT_Code = CC.CT_Code AND CT.Language_Code = '$Language_code$'" & vbCrLf & _
                                "INNER JOIN Cap_CompanyPriority CP ON CP.CP_Code = CC.CP_Code AND CP.Language_Code = '$Language_code$'" & vbCrLf & _
                                "INNER JOIN Cap_CompanyType CCT ON CCT.CCT_Code = CC.CCT_Code AND CCT.Language_Code = '$Language_code$'" & vbCrLf & _
                                "$SpecialJoin1$" & vbCrLf & _
                                "INNER JOIN Cap_CompanyRep CCR ON CCR.CCU_Capkey = CC.CCU_Capkey" & vbCrLf & _
                                "INNER JOIN Cap_RepRole CRR ON CRR.RR_Code = CCR.RR_Code AND CRR.BU_GROUPING LIKE 'X%' AND CRR.Language_Code=  '$Language_code$'" & vbCrLf & _
                                "INNER JOIN Sales_Reps SR ON SR.SR_Code = CCR.SR_Code AND SR.Language_Code = 'E'" & vbCrLf & _
                                "LEFT OUTER JOIN Cap_Contact PCNT ON PCNT.CNT_Code = CC.CCU_PrimCNT " & vbCrLf & _
                                "LEFT OUTER JOIN Cap_Title PTIT ON PTIT.TIT_Code = PCNT.TIT_Code AND PTIT.Language_Code = '$Language_code$'" & vbCrLf & _
                                "WHERE EE.USER_ID = '$User_ID$' " & vbCrLf & _
                                "ORDER BY EE.Code "
    
    Dim lr_AttrJoin As Variant
    lr_AttrJoin = Array("CTTA.CTA_Desc", "", "LEFT OUTER JOIN Cap_CustomerAttribute CTA ON CTA.CCU_Capkey = CC.CCU_Capkey" & vbCrLf & _
                                 "LEFT OUTER JOIN Cap_CompanyTypeAttribute CTTA ON CTTA.CTA_Code = CTA.CTA_Code AND CTTA.Language_Code = '$Language_Code$'" & vbCrLf, "")
    ArmExcel1.SpecialFields = Array(lr_AttrJoin)
                         
    
    Call ArmExcel1.LoadConstants(EXptSQL, "cap_ExportExcel_Fields_lst2 'Cap_ExportExcel_Contact','ArmExcel','$Language_code$'", EXctFields)
    
    Call ArmExcel1.LoadConstants(EXptSQL, "screen_csts_lst2 'Cap_ExportExcel_Select', 'ArmExcel', '$Language_code$'", EXctControl)
    
    Call ArmExcel1.LoadConstants(EXptStatic, Join(Array("Enter the name of the preselection (only 15 chars) :", _
                                                 "This name is already used, please choose another", _
                                                 "Are you sure to delete this item : "), CH_LDELIMIT), EXctMessages)
    Call ArmExcel1.LoadPreselections

Trace_End:
    #If CapDebug Then
        If Not go_Trace Is Nothing Then go_Trace.WriteTraceProc False, "ExportExcelForm:ExportContact"
    #End If
    CapMouseOn
    Exit Sub
    
Trace_Err:
    If Not go_Trace Is Nothing Then go_Trace.WriteTraceError Err.Number, Err.Description, "ExportExcelForm:ExportContact"
    CapMouseOn
End Sub

Public Sub ExportProduct()
    
    #If CapDebug Then
        If Not go_Trace Is Nothing Then go_Trace.WriteTraceProc True, "ExportExcelForm:ExportProduct"
    #End If
    CapMouseOff
    
    ArmExcel1.User_ID = ms_UID
    ArmExcel1.InsertTempKeyCount = 9
    
    ArmExcel1.CreateTempTableRequest = "CREATE TABLE [#Cap_ExcelExport] ([Code] [int] Identity(1,1),[User_ID] [varchar] (30) NULL ,[CapKey1] [varchar] (20) NULL ,    [CapKey2] [varchar] (20) NULL, [CapKey3] [varchar] (20) NULL) ON [PRIMARY]"
    ArmExcel1.DropTempTableRequest = "DROP TABLE [#Cap_ExcelExport]"
    ArmExcel1.InsertTempKeyRequest = "exec Cap_ExcelExport_ins3 '$User_ID$','$0$','$1$','$2$','$3$','$4$', '$5$', '$6$', '$7$', '$8$'"
    
    ArmExcel1.MaxPreselections = 10
    
    ArmExcel1.PreSelRequest_Ins = "Cap_ExportExcelPreSel_ins '$User_ID$','PRODUCT','$PreSelName$','$PreSelFields$'"
    ArmExcel1.PreSelRequest_Del = "Cap_ExportExcelPreSel_del '$User_ID$','PRODUCT','$PreSelName$'"
    ArmExcel1.PreSelRequest_Sel = "Cap_ExportExcelPreSel_lst '$User_ID$','PRODUCT'"
    
    ArmExcel1.MultiKeyRequest = "SELECT $SelectFields$ " & vbCrLf & _
                                "FROM #Cap_ExcelExport EE " & vbCrLf & _
                                "INNER JOIN Cap_SolutionSystem S ON S.SOL_Code = EE.Capkey1 AND S.Language_Code = 'E' AND S.S_Code = EE.Capkey2" & vbCrLf & _
                                "INNER JOIN Cap_System SYS ON SYS.S_Code = S.S_Code AND SYS.Language_Code = '$Language_Code$'" & vbCrLf & _
                                "LEFT OUTER JOIN Product_Module PM ON PM.PM_Code = SYS.PM_Code AND PM.Language_Code = 'E'" & vbCrLf & _
                                "INNER JOIN Cap_MainAlternate MA ON MA.MA_Code = S.MA_Code AND MA.Language_Code = '$Language_Code$'" & vbCrLf & _
                                "INNER JOIN Cap_Competitor COMP ON COMP.CPT_Code = SYS.CPT_Code AND COMP.Language_Code = '$Language_Code$'" & vbCrLf & _
                                "INNER JOIN Sales_Project SP ON SP.SP_Capkey = S.SP_Capkey AND SP. SP_System = 'capture'" & vbCrLf & _
                                "INNER JOIN Countries CT ON CT.CT_Code = SP.CT_Code AND CT.Language_Code = '$Language_Code$'" & vbCrLf & _
                                "INNER JOIN Cap_County CTY ON CTY.CTY_Code = SP.CTY_Code AND CTY.Language_Code = '$Language_Code$'" & vbCrLf & _
                                "INNER JOIN Cap_ProjectType PT ON PT.PT_Code = SP.PT_Code AND PT.Language_Code =   '$Language_Code$'" & vbCrLf & _
                                "INNER JOIN Cap_ProjectPriority PP ON PP.PP_Code = SP.PP_Code AND PP.Language_code = '$Language_Code$'" & vbCrLf & _
                                "INNER JOIN Cap_NewRenovation NR ON NR.NR_Code = SP.NR_Code AND NR.Language_Code = '$Language_Code$'" & vbCrLf & _
                                "LEFT OUTER JOIN Cap_ProjectDescription PD ON PD.PD_Code = SP.PD_Code AND PD.Language_Code = '$Language_Code$'" & vbCrLf & _
                                "INNER JOIN Cap_SalesStatus SS ON SS.SS_Code = SP.SS_Code AND SS.Language_Code = '$Language_Code$'" & vbCrLf & _
                                "LEFT OUTER JOIN Cap_StratAccount SA ON SA.SA_Code = SP.SA_Code AND SA.Language_Code = '$Language_Code$'" & vbCrLf & _
                                "INNER JOIN Cap_ProjectRep CPR ON CPR.SP_Capkey = SP.SP_Capkey AND CPR.Language_Code = 'E'" & vbCrLf & _
                                "INNER JOIN Cap_RepRole CRR ON CRR.RR_Code = CPR.RR_Code AND CRR.BU_GROUPING = 'X2' AND CRR.Language_Code = 'E'" & vbCrLf & _
                                "INNER JOIN Sales_Reps SR ON SR.SR_Code = CPR.SR_Code AND SR.Language_Code = '$Language_Code$'" & vbCrLf & _
                                "INNER JOIN Cap_SystemClassif CSC ON CSC.SC_Code = SYS.SC_Code AND CSC.Language_code = '$Language_Code$'" & vbCrLf & _
                                "WHERE EE.USER_ID = '$User_ID$' " & vbCrLf & _
                                "ORDER BY EE.Code "
                         
    Call ArmExcel1.LoadConstants(EXptSQL, "cap_ExportExcel_Fields_lst2 'Cap_ExportExcel_Product','ArmExcel','$Language_code$'", EXctFields)
    
    Call ArmExcel1.LoadConstants(EXptSQL, "screen_csts_lst2 'Cap_ExportExcel_Select', 'ArmExcel', '$Language_code$'", EXctControl)
    
    Call ArmExcel1.LoadConstants(EXptStatic, Join(Array("Enter the name of the preselection (only 15 chars) :", _
                                                 "This name is already used, please choose another", _
                                                 "Are you sure to delete this item : "), CH_LDELIMIT), EXctMessages)
    Call ArmExcel1.LoadPreselections

Trace_End:
    #If CapDebug Then
        If Not go_Trace Is Nothing Then go_Trace.WriteTraceProc False, "ExportExcelForm:ExportProduct"
    #End If
    CapMouseOn
    Exit Sub
    
Trace_Err:
    If Not go_Trace Is Nothing Then go_Trace.WriteTraceError Err.Number, Err.Description, "ExportExcelForm:ExportProduct"
    CapMouseOn
End Sub

Private Sub ExportProjectReference()

    #If CapDebug Then
        If Not go_Trace Is Nothing Then go_Trace.WriteTraceProc True, "ExportExcelForm:ExportProjectReference"
    #End If
    CapMouseOff
    
    ArmExcel1.User_ID = ms_UID
    ArmExcel1.InsertTempKeyCount = 10
    
    ArmExcel1.CreateTempTableRequest = "CREATE TABLE [#Cap_ExcelExport] ([Code] [int] Identity(1,1),[User_ID] [varchar] (30) NULL ,[CapKey1] [varchar] (20) NULL ,    [CapKey2] [varchar] (20) NULL, [CapKey3] [varchar] (20) NULL) ON [PRIMARY]"
    ArmExcel1.DropTempTableRequest = "DROP TABLE [#Cap_ExcelExport]"
    ArmExcel1.InsertTempKeyRequest = "exec Cap_ExcelExport_ins4 '$User_ID$','$0$','$1$','$2$','$3$','$4$', '$5$', '$6$', '$7$', '$8$', '$9$'"
    
    ArmExcel1.MaxPreselections = 10
    
    ArmExcel1.PreSelRequest_Ins = "Cap_ExportExcelPreSel_ins '$User_ID$','REF_PROJECT','$PreSelName$','$PreSelFields$'"
    ArmExcel1.PreSelRequest_Del = "Cap_ExportExcelPreSel_del '$User_ID$','REF_PROJECT','$PreSelName$'"
    ArmExcel1.PreSelRequest_Sel = "Cap_ExportExcelPreSel_lst '$User_ID$','REF_PROJECT'"
    
    ArmExcel1.MultiKeyRequest = "SELECT $SelectFields$ " & vbCrLf & _
                                "FROM #Cap_ExcelExport EE " & vbCrLf & _
                                "INNER JOIN RP_Reference_Project RP ON RP.RP_Code = EE.Capkey1 AND RP.Language_Code='E'" & vbCrLf & _
                                "INNER JOIN RP_LangInfo LI ON LI.RP_Code = RP.RP_Code AND LI.Language_Code = '$Language_Code$' AND LI.Z_State = 1" & vbCrLf & _
                                "INNER JOIN Countries C ON C.CT_Code = RP.CT_Code AND C.Language_Code = '$Language_Code$' AND NOT C.drop_flag = 'X'" & vbCrLf & _
                                "INNER JOIN Gen_Systems_Users GEN ON GEN.U_code = RP.Z_Creator" & vbCrLf & _
                                "INNER JOIN GEN_People GENP ON GENP.P_Code = GEN.P_Code" & vbCrLf & _
                                "WHERE EE.USER_ID = '$User_ID$' AND RP.Z_State = 1" & vbCrLf & _
                                "ORDER BY EE.Code"

    Call ArmExcel1.LoadConstants(EXptSQL, "cap_ExportExcel_Fields_lst2 'Cap_ExportExcel_RefProject','ArmExcel','$Language_code$'", EXctFields)
    
    Call ArmExcel1.LoadConstants(EXptSQL, "screen_csts_lst2 'Cap_ExportExcel_Select', 'ArmExcel', '$Language_code$'", EXctControl)
    
    Call ArmExcel1.LoadConstants(EXptStatic, Join(Array("Enter the name of the preselection (only 15 chars) :", _
                                                 "This name is already used, please choose another", _
                                                 "Are you sure to delete this item : "), CH_LDELIMIT), EXctMessages)
    Call ArmExcel1.LoadPreselections

Trace_End:
    #If CapDebug Then
        If Not go_Trace Is Nothing Then go_Trace.WriteTraceProc False, "ExportExcelForm:ExportProjectReference"
    #End If
    CapMouseOn
    Exit Sub
    
Trace_Err:
    If Not go_Trace Is Nothing Then go_Trace.WriteTraceError Err.Number, Err.Description, "ExportExcelForm:ExportProjectReference"
    CapMouseOn
End Sub

Private Sub UpdateProjectReferenceFields(ByVal av_Fields As Variant, av_data As Variant, ByVal as_Language_Code As String)
On Error GoTo ErrHandler
    Const C_REQ_EXC As String = "EXEC RP_ExCountries_lst2 '$USER_ID$', '$language_code$'"
    Const C_REQ_SH As String = "EXEC RP_BI_Marketing_Link_lst2 '$USER_ID$', '$language_code$'"
    Const C_REQ_RF As String = "EXEC RP_Document_Photographer_lst '$USER_ID$', '$language_code$'"
    Dim ls_Req As String
    ' check for specia fields
    If IsArray(av_Fields) Then
        Dim ll_i As Long
        For ll_i = LBound(av_Fields) To UBound(av_Fields)
            Select Case av_Fields(ll_i)
                Case "RP_CODE_EXC"     ' Exluded countries
                    If ml_cursorExCountries = 0 Then
                        ls_Req = Replace(C_REQ_EXC, "$USER_ID$", ms_UID, , , vbTextCompare)
                        ls_Req = Replace(ls_Req, "$language_code$", as_Language_Code, , , vbTextCompare)
                        ml_cursorExCountries = OpenSQLSafe(mo_ArmDb, ls_Req)
                    End If
                    av_data(ll_i) = ConcatReqData(ml_cursorExCountries, av_data(ll_i), "RP_Code", "CT_Desc")
                Case "RP_CODE_SH"      ' Shapes
                    If ml_cursorShapes = 0 Then
                        ls_Req = Replace(C_REQ_SH, "$USER_ID$", ms_UID, , , vbTextCompare)
                        ls_Req = Replace(ls_Req, "$language_code$", as_Language_Code, , , vbTextCompare)
                        ml_cursorShapes = OpenSQLSafe(mo_ArmDb, ls_Req)
                    End If
                    av_data(ll_i) = ConcatReqData(ml_cursorShapes, av_data(ll_i), "RP_Code", "SH_Desc")
                Case "RP_CODE_RF"      ' Photographers
                    If ml_cursorPhotographer = 0 Then
                        ls_Req = Replace(C_REQ_RF, "$USER_ID$", ms_UID, , , vbTextCompare)
                        ls_Req = Replace(ls_Req, "$language_code$", as_Language_Code, , , vbTextCompare)
                        ml_cursorPhotographer = OpenSQLSafe(mo_ArmDb, ls_Req)
                    End If
                    av_data(ll_i) = ConcatReqData(ml_cursorPhotographer, av_data(ll_i), "RP_Code", "RF_Desc")
            End Select
        Next
    End If
    Exit Sub
ErrHandler:
    Call ErrorHandler("UpdateProjectReferenceFields()")
End Sub

Private Function ConcatReqData(ByVal al_Cursor As Long, ByVal as_keyValue As String, ByVal as_keyFieldName As String, ByVal av_field As Variant, Optional as_SEP As String = ",") As String
On Error GoTo ErrHandler
    
    Dim ls_ret As String
    ConcatReqData = ""
    ls_ret = ""
    Dim a As ArmDb
    If mo_ArmDb.Find(al_Cursor, as_keyFieldName, as_keyValue) >= 0 Then
        While (Not mo_ArmDb.EOF(al_Cursor)) And (mo_ArmDb.GetFields(al_Cursor, as_keyFieldName) = as_keyValue)
            ls_ret = IIf(ls_ret = "", "", ls_ret & as_SEP) & mo_ArmDb.GetFields(al_Cursor, av_field)
            Call mo_ArmDb.Next(al_Cursor)
        Wend
    End If

    ConcatReqData = ls_ret
    Exit Function
ErrHandler:
    Call ErrorHandler("ConcatReqData()")
End Function


Private Sub Form_Resize()
    #If CapDebug Then
        If Not go_Trace Is Nothing Then go_Trace.WriteTraceProc True, "ExportExcelForm:Form_Resize"
    #End If
    CapMouseOff
    
    ArmExcel1.Left = 0
    ArmExcel1.Top = 0
    ArmExcel1.Width = 6900
    Me.Width = 6900
    Me.ScaleWidth = 6900
    ArmExcel1.Height = Me.ScaleHeight

Trace_End:
    #If CapDebug Then
        If Not go_Trace Is Nothing Then go_Trace.WriteTraceProc False, "ExportExcelForm:Form_Resize"
    #End If
    CapMouseOn
    Exit Sub
    
Trace_Err:
    If Not go_Trace Is Nothing Then go_Trace.WriteTraceError Err.Number, Err.Description, "ExportExcelForm:Form_Resize"
    CapMouseOn
End Sub


Public Sub ExportProject_Search()
    
    #If CapDebug Then
        If Not go_Trace Is Nothing Then go_Trace.WriteTraceProc True, "ExportExcelForm:ExportProject_Search"
    #End If
    CapMouseOff
    
    ArmExcel1.User_ID = ms_UID
    ArmExcel1.InsertTempKeyCount = 10
    
    ArmExcel1.CreateTempTableRequest = "CREATE TABLE [#Cap_ExcelExport] ([Code] [int] Identity(1,1),[User_ID] [varchar] (30) NULL ,[CapKey1] [varchar] (20) NULL ) ON [PRIMARY]"
    ArmExcel1.DropTempTableRequest = "DROP TABLE [#Cap_ExcelExport]"
    ArmExcel1.InsertTempKeyRequest = "exec Cap_ExcelExport_ins4 '$User_ID$','$0$','$1$','$2$','$3$','$4$', '$5$', '$6$', '$7$', '$8$', '$9$'"
    
    ArmExcel1.MaxPreselections = 10
    
    ArmExcel1.PreSelRequest_Ins = "Cap_ExportExcelPreSel_ins '$User_ID$','PROJECT','$PreSelName$','$PreSelFields$'"
    ArmExcel1.PreSelRequest_Del = "Cap_ExportExcelPreSel_del '$User_ID$','PROJECT','$PreSelName$'"
    ArmExcel1.PreSelRequest_Sel = "Cap_ExportExcelPreSel_lst '$User_ID$','PROJECT'"
    
    ArmExcel1.MultiKeyRequest = "SELECT $SelectFields$ " & vbCrLf & _
                                "FROM #Cap_ExcelExport EE " & vbCrLf & _
                                "JOIN Sales_Project SP ON SP.Sp_CapKey = EE.CapKey1 AND SP.Language_Code='E' " & vbCrLf & _
                                "LEFT JOIN Cap_ProjectDescription PD ON SP.PD_code = PD.PD_code AND PD.Language_code = '$Language_Code$' " & vbCrLf & _
                                "LEFT JOIN Countries CT ON SP.CT_Code = CT.CT_Code And CT.Language_Code = '$Language_Code$'" & _
                                "LEFT JOIN Cap_County CTY ON SP.CTY_Code = CTY.CTY_Code AND CTY.CT_Code = CT.CT_Code AND CTY.Language_Code = '$Language_Code$'" & _
                                "LEFT JOIN Cap_NewRenovation NR ON SP.NR_code = NR.NR_code AND NR.Language_code = '$Language_Code$' " & vbCrLf & _
                                "LEFT JOIN Cap_SalesStatus SS ON SP.SS_code = SS.SS_code AND SS.Language_code = '$Language_Code$' " & vbCrLf & _
                                "LEFT JOIN Cap_ProjectType PT ON SP.PT_code = PT.PT_code AND PT.Language_code = '$Language_Code$' " & vbCrLf & _
                                "LEFT JOIN Cap_ProjectPriority PP ON SP.PP_code = PP.PP_code AND PP.Language_code = '$Language_Code$' " & vbCrLf & _
                                "LEFT JOIN Cap_StratAccount SA ON SP.SA_code = SA.SA_code AND SA.Language_code = '$Language_Code$' " & vbCrLf & _
                                "LEFT JOIN Cap_ProjectRep PR1 ON SP.SP_CapKey=PR1.SP_CapKey AND PR1.RR_Code=2 -- Customer role " & vbCrLf & _
                                "LEFT JOIN Sales_Reps SR1 ON PR1.SR_Code=SR1.SR_Code AND SR1.Language_Code='$Language_Code$' " & vbCrLf & _
                                "LEFT JOIN Cap_RepRole RR1 ON RR1.RR_Code= PR1.RR_Code AND RR1.Language_Code='$Language_Code$' " & vbCrLf & _
                                "LEFT JOIN Cap_ProjectRep PR2 ON SP.SP_CapKey=PR2.SP_CapKey AND PR2.RR_Code=1 -- Specifier role " & vbCrLf & _
                                "LEFT JOIN Sales_Reps SR2 ON PR2.SR_Code=SR2.SR_Code AND SR2.Language_Code='$Language_Code$' " & vbCrLf & _
                                "LEFT JOIN Cap_RepRole RR2 ON RR2.RR_Code= PR2.RR_Code AND RR2.Language_Code='$Language_Code$' " & vbCrLf & _
                                "LEFT JOIN Cap_SAType SAT ON SP.SAT_Code= SAT.SAT_Code AND SAT.Language_Code='$Language_Code$' " & vbCrLf & _
                                "LEFT JOIN Cap_ProjectLevel LEV ON SP.LEV_Code= LEV.LEV_Code AND LEV.Language_Code='$Language_Code$' " & vbCrLf & _
                                "LEFT JOIN Cap_SystemSolutionType SST ON SP.SST_Code= SST.SST_Code AND SST.Language_Code='$Language_Code$' " & vbCrLf & _
                                "LEFT JOIN Cap_ProjectProbability PPB ON SP.spare3_real = PPB.PPB_ID AND PPB.Language_Code='$Language_Code$' " & vbCrLf & _
                                "WHERE EE.USER_ID = '$User_ID$' " & vbCrLf & _
                                "ORDER BY EE.Code "
                        
    'ArmExcel1.SpecialFields = ""
    Call ArmExcel1.LoadConstants(EXptSQL, "cap_ExportExcel_Fields_lst2 'Cap_ExportExcel_Project','ArmExcel','$Language_code$'", EXctFields)
    
    Call ArmExcel1.LoadConstants(EXptSQL, "screen_csts_lst2 'Cap_ExportExcel_Select', 'ArmExcel', '$Language_code$'", EXctControl)
    
    Call ArmExcel1.LoadConstants(EXptStatic, Join(Array("Enter the name of the preselection (only 15 chars) :", _
                                                 "This name is already used, please choose another", _
                                                 "Are you sure to delete this item : "), CH_LDELIMIT), EXctMessages)
    Call ArmExcel1.LoadPreselections

Trace_End:
    #If CapDebug Then
        If Not go_Trace Is Nothing Then go_Trace.WriteTraceProc False, "ExportExcelForm:ExportProject_Search"
    #End If
    CapMouseOn
    Exit Sub
    
Trace_Err:
    If Not go_Trace Is Nothing Then go_Trace.WriteTraceError Err.Number, Err.Description, "ExportExcelForm:ExportProject_Search"
    CapMouseOn
End Sub



Public Sub ExportCustomer_Search()
    
    #If CapDebug Then
        If Not go_Trace Is Nothing Then go_Trace.WriteTraceProc True, "ExportExcelForm:ExportCustomer_Search"
    #End If
    CapMouseOff
    
    ArmExcel1.User_ID = ms_UID
    ArmExcel1.InsertTempKeyCount = 10
    
    ArmExcel1.CreateTempTableRequest = "CREATE TABLE [#Cap_ExcelExport] ([Code] [int] Identity(1,1),[User_ID] [varchar] (30) NULL ,[CapKey1] [varchar] (20) NULL) ON [PRIMARY]"
    ArmExcel1.DropTempTableRequest = "DROP TABLE [#Cap_ExcelExport]"
    ArmExcel1.InsertTempKeyRequest = "exec Cap_ExcelExport_ins4 '$User_ID$','$0$','$1$','$2$','$3$','$4$', '$5$', '$6$', '$7$', '$8$', '$9$'"
    
    ArmExcel1.MaxPreselections = 10
    
    ArmExcel1.PreSelRequest_Ins = "Cap_ExportExcelPreSel_ins '$User_ID$','CUSTOMER','$PreSelName$','$PreSelFields$'"
    ArmExcel1.PreSelRequest_Del = "Cap_ExportExcelPreSel_del '$User_ID$','CUSTOMER','$PreSelName$'"
    ArmExcel1.PreSelRequest_Sel = "Cap_ExportExcelPreSel_lst '$User_ID$','CUSTOMER'"
    
' IMPLEMENT PROCESS T298.1-A.PR_005
    ArmExcel1.MultiKeyRequest = "SELECT $SelectFields$ " & vbCrLf & _
                                "FROM #Cap_ExcelExport EE " & vbCrLf & _
                                "INNER JOIN Claim_Customer CC ON CC.CCU_Capkey = EE.Capkey1 AND CC.Language_Code = 'E' AND CC.SYS_Code = 11" & vbCrLf & _
                                "INNER JOIN Cap_County CTY ON CTY.CTY_Code = CC.CTY_Code AND CTY.Language_Code = '$Language_Code$'" & vbCrLf & _
                                "INNER JOIN Countries CT ON CT.CT_Code = CC.CT_Code AND CT.Language_Code = '$Language_Code$'" & vbCrLf & _
                                "INNER JOIN Cap_CompanyPriority CP ON CP.CP_Code = CC.CP_Code AND CP.Language_Code ='$Language_Code$'" & vbCrLf & _
                                "INNER JOIN Cap_CompanyType CCT ON CCT.CCT_Code = CC.CCT_Code AND CCT.Language_Code = '$Language_Code$'" & vbCrLf & _
                                "INNER JOIN Cap_CompanyRep CCR ON CCR.CCU_Capkey = CC.CCU_Capkey" & vbCrLf & _
                                "INNER JOIN Cap_RepRole CRR ON CRR.RR_Code = CCR.RR_Code AND CRR.BU_GROUPING LIKE 'X%' AND CRR.Language_Code=  '$Language_Code$'" & vbCrLf & _
                                "INNER JOIN Sales_Reps SR ON SR.SR_Code = CCR.SR_Code AND SR.Language_Code = 'E'" & vbCrLf & _
                                "LEFT OUTER JOIN Cap_Contact CNT ON CNT.CNT_Code = CC.CCU_PrimCNT " & vbCrLf & _
                                "LEFT OUTER JOIN Cap_CustomerLoyalty LOY ON LOY.LOY_Code = CC.LOY_Code AND LOY.Language_Code = '$Language_Code$'" & vbCrLf & _
                                "    LEFT OUTER JOIN Cap_Title CTT ON CTT.TIT_Code = CNT.TIT_Code AND CTT.Language_Code = '$Language_Code$' " & vbCrLf & _
                                "$SpecialJoin1$" & vbCrLf & _
                                "WHERE EE.USER_ID = '$User_ID$' " & vbCrLf & _
                                "ORDER BY EE.Code "
' END PROCESS T298.1-A.PR_005
                         
    Dim lr_AttrJoin As Variant
    lr_AttrJoin = Array("CTTA.CTA_Desc", "", "LEFT OUTER JOIN Cap_CustomerAttribute CTA ON CTA.CCU_Capkey = CC.CCU_Capkey" & vbCrLf & _
                                 "LEFT OUTER JOIN Cap_CompanyTypeAttribute CTTA ON CTTA.CTA_Code = CTA.CTA_Code AND CTTA.Language_Code = '$Language_Code$'" & vbCrLf, "")
    ArmExcel1.SpecialFields = Array(lr_AttrJoin)
    Call ArmExcel1.LoadConstants(EXptSQL, "cap_ExportExcel_Fields_lst2 'Cap_ExportExcel_Customer','ArmExcel','$Language_code$'", EXctFields)
    
    Call ArmExcel1.LoadConstants(EXptSQL, "screen_csts_lst2 'Cap_ExportExcel_Select', 'ArmExcel', '$Language_code$'", EXctControl)
    
    Call ArmExcel1.LoadConstants(EXptStatic, Join(Array("Enter the name of the preselection (only 15 chars) :", _
                                                 "This name is already used, please choose another", _
                                                 "Are you sure to delete this item : "), CH_LDELIMIT), EXctMessages)
    Call ArmExcel1.LoadPreselections

Trace_End:
    #If CapDebug Then
        If Not go_Trace Is Nothing Then go_Trace.WriteTraceProc False, "ExportExcelForm:ExportCustomer_Search"
    #End If
    CapMouseOn
    Exit Sub
    
Trace_Err:
    If Not go_Trace Is Nothing Then go_Trace.WriteTraceError Err.Number, Err.Description, "ExportExcelForm:ExportCustomer_Search"
    CapMouseOn
End Sub


Public Sub ExportContact_Search()
    
    
    #If CapDebug Then
        If Not go_Trace Is Nothing Then go_Trace.WriteTraceProc True, "ExportExcelForm:ExportContact_Search"
    #End If
    CapMouseOff
    
    ArmExcel1.User_ID = ms_UID
    
    'ArmExcel1.InsertTempKeyCount = 9
    
    'ArmExcel1.CreateTempTableRequest = "CREATE TABLE [#Cap_ExcelExport] ([Code] [int] Identity(1,1),[User_ID] [varchar] (30) NULL ,[CapKey1] [varchar] (20) NULL ,    [CapKey3] [varchar] (20) NULL) ON [PRIMARY]"
    'ArmExcel1.DropTempTableRequest = "DROP TABLE [#Cap_ExcelExport]"
    'ArmExcel1.InsertTempKeyRequest = "exec Cap_ExcelExport_ins2 '$User_ID$','$0$','$1$','$2$','$3$','$4$', '$5$', '$6$', '$7$', '$8$'"
    
    ArmExcel1.InsertTempKeyCount = 10
    
    ArmExcel1.CreateTempTableRequest = "CREATE TABLE [#Cap_ExcelExport] ([Code] [int] Identity(1,1),[User_ID] [varchar] (30) NULL ,[CapKey1] [varchar] (20) NULL ,    [CapKey2] [varchar] (20) NULL) ON [PRIMARY]"
    ArmExcel1.DropTempTableRequest = "DROP TABLE [#Cap_ExcelExport]"
    ArmExcel1.InsertTempKeyRequest = "exec Cap_ExcelExport_ins '$User_ID$','$0$','$1$','$2$','$3$','$4$', '$5$', '$6$', '$7$', '$8$', '$9$'"

    
    ArmExcel1.MaxPreselections = 10
    
    ArmExcel1.PreSelRequest_Ins = "Cap_ExportExcelPreSel_ins '$User_ID$','CONTACT','$PreSelName$','$PreSelFields$'"
    ArmExcel1.PreSelRequest_Del = "Cap_ExportExcelPreSel_del '$User_ID$','CONTACT','$PreSelName$'"
    ArmExcel1.PreSelRequest_Sel = "Cap_ExportExcelPreSel_lst '$User_ID$','CONTACT'"
    
    ArmExcel1.MultiKeyRequest = "SELECT $SelectFields$ " & vbCrLf & _
                                "FROM #Cap_ExcelExport EE " & vbCrLf & _
                                "INNER JOIN Cap_Contact CNT ON CNT.CNT_Code = EE.Capkey1 AND CNT.language_Code = 'E'" & vbCrLf & _
                                "INNER JOIN Cap_Title TIT ON TIT.TIT_Code = CNT.TIT_Code AND TIT.Language_Code = '$Language_code$'" & vbCrLf & _
                                "INNER JOIN Cap_Job JOB ON JOB.JOB_Code = CNT.JOB_Code AND JOB.Language_Code = '$Language_code$'" & vbCrLf & _
                                "INNER JOIN Claim_Customer CC ON CC.CCU_Capkey = CNT.CCU_Capkey AND CC.SYS_Code = 11" & vbCrLf & _
                                "INNER JOIN Cap_County CTY ON CTY.CTY_Code = CC.CTY_Code AND CTY.Language_Code = '$Language_code$'" & vbCrLf & _
                                "INNER JOIN Countries CT ON CT.CT_Code = CC.CT_Code AND CT.Language_Code = '$Language_code$'" & vbCrLf & _
                                "INNER JOIN Cap_CompanyPriority CP ON CP.CP_Code = CC.CP_Code AND CP.Language_Code = '$Language_code$'" & vbCrLf & _
                                "INNER JOIN Cap_CompanyType CCT ON CCT.CCT_Code = CC.CCT_Code AND CCT.Language_Code = '$Language_code$'" & vbCrLf & _
                                "$SpecialJoin1$" & vbCrLf & _
                                "INNER JOIN Cap_CompanyRep CCR ON CCR.CCU_Capkey = CC.CCU_Capkey" & vbCrLf & _
                                "INNER JOIN Cap_RepRole CRR ON CRR.RR_Code = CCR.RR_Code AND CRR.BU_GROUPING LIKE 'X%' AND CRR.Language_Code=  '$Language_code$'" & vbCrLf & _
                                "INNER JOIN Sales_Reps SR ON SR.SR_Code = CCR.SR_Code AND SR.Language_Code = 'E'" & vbCrLf & _
                                "LEFT OUTER JOIN Cap_Contact PCNT ON PCNT.CNT_Code = CC.CCU_PrimCNT " & vbCrLf & _
                                "LEFT OUTER JOIN Cap_Title PTIT ON PTIT.TIT_Code = PCNT.TIT_Code AND PTIT.Language_Code = '$Language_code$'" & vbCrLf & _
                                "WHERE EE.USER_ID = '$User_ID$' " & vbCrLf & _
                                "ORDER BY EE.Code "
    
    Dim lr_AttrJoin As Variant
    lr_AttrJoin = Array("CTTA.CTA_Desc", "", "LEFT OUTER JOIN Cap_CustomerAttribute CTA ON CTA.CCU_Capkey = CC.CCU_Capkey" & vbCrLf & _
                                 "LEFT OUTER JOIN Cap_CompanyTypeAttribute CTTA ON CTTA.CTA_Code = CTA.CTA_Code AND CTTA.Language_Code = '$Language_Code$'" & vbCrLf, "")
    ArmExcel1.SpecialFields = Array(lr_AttrJoin)
                         
    
    Call ArmExcel1.LoadConstants(EXptSQL, "cap_ExportExcel_Fields_lst2 'Cap_ExportExcel_Contact','ArmExcel','$Language_code$'", EXctFields)
    
    Call ArmExcel1.LoadConstants(EXptSQL, "screen_csts_lst2 'Cap_ExportExcel_Select', 'ArmExcel', '$Language_code$'", EXctControl)
    
    Call ArmExcel1.LoadConstants(EXptStatic, Join(Array("Enter the name of the preselection (only 15 chars) :", _
                                                 "This name is already used, please choose another", _
                                                 "Are you sure to delete this item : "), CH_LDELIMIT), EXctMessages)
    Call ArmExcel1.LoadPreselections

Trace_End:
    #If CapDebug Then
        If Not go_Trace Is Nothing Then go_Trace.WriteTraceProc False, "ExportExcelForm:ExportContact_Search"
    #End If
    CapMouseOn
    Exit Sub
    
Trace_Err:
    If Not go_Trace Is Nothing Then go_Trace.WriteTraceError Err.Number, Err.Description, "ExportExcelForm:ExportContact_Search"
    CapMouseOn

End Sub


Public Sub ExportProduct_Search()
    
    #If CapDebug Then
        If Not go_Trace Is Nothing Then go_Trace.WriteTraceProc True, "ExportExcelForm:ExportProduct_Search"
    #End If
    CapMouseOff
    
    ArmExcel1.User_ID = ms_UID
    
    ArmExcel1.InsertTempKeyCount = 8
    
    ArmExcel1.CreateTempTableRequest = "CREATE TABLE [#Cap_ExcelExport] ([Code] [int] Identity(1,1),[User_ID] [varchar] (30) NULL ,[CapKey1] [varchar] (20) NULL ,    [CapKey2] [varchar] (20) NULL) ON [PRIMARY]"
    ArmExcel1.DropTempTableRequest = "DROP TABLE [#Cap_ExcelExport]"
    ArmExcel1.InsertTempKeyRequest = "exec Cap_ExcelExport_ins5 '$User_ID$','$0$','$1$','$2$','$3$','$4$', '$5$', '$6$', '$7$'"

    
    ArmExcel1.MaxPreselections = 10
    
    ArmExcel1.PreSelRequest_Ins = "Cap_ExportExcelPreSel_ins '$User_ID$','PRODUCT','$PreSelName$','$PreSelFields$'"
    ArmExcel1.PreSelRequest_Del = "Cap_ExportExcelPreSel_del '$User_ID$','PRODUCT','$PreSelName$'"
    ArmExcel1.PreSelRequest_Sel = "Cap_ExportExcelPreSel_lst '$User_ID$','PRODUCT'"
    
    ArmExcel1.MultiKeyRequest = "SELECT $SelectFields$ " & vbCrLf & _
                                "FROM #Cap_ExcelExport EE " & vbCrLf & _
                                "INNER JOIN Cap_SolutionSystem S ON S.SOL_Code = EE.Capkey1  AND S.Language_Code = 'E' AND S.S_Code = EE.Capkey2" & vbCrLf & _
                                "INNER JOIN Cap_System SYS ON SYS.S_Code = S.S_Code AND SYS.Language_Code = '$Language_Code$'" & vbCrLf & _
                                "LEFT OUTER JOIN Product_Module PM ON PM.PM_Code = SYS.PM_Code AND PM.Language_Code = 'E'" & vbCrLf & _
                                "INNER JOIN Cap_MainAlternate MA ON MA.MA_Code = S.MA_Code AND MA.Language_Code = '$Language_Code$'" & vbCrLf & _
                                "INNER JOIN Cap_Competitor COMP ON COMP.CPT_Code = SYS.CPT_Code AND COMP.Language_Code = '$Language_Code$'" & vbCrLf & _
                                "INNER JOIN Sales_Project SP ON SP.SP_Capkey = S.SP_Capkey AND SP. SP_System = 'capture'" & vbCrLf & _
                                "INNER JOIN Countries CT ON CT.CT_Code = SP.CT_Code AND CT.Language_Code = '$Language_Code$'" & vbCrLf & _
                                "INNER JOIN Cap_County CTY ON CTY.CTY_Code = SP.CTY_Code AND CTY.Language_Code = '$Language_Code$'" & vbCrLf & _
                                "INNER JOIN Cap_ProjectType PT ON PT.PT_Code = SP.PT_Code AND PT.Language_Code =   '$Language_Code$'" & vbCrLf & _
                                "INNER JOIN Cap_ProjectPriority PP ON PP.PP_Code = SP.PP_Code AND PP.Language_code = '$Language_Code$'" & vbCrLf & _
                                "INNER JOIN Cap_NewRenovation NR ON NR.NR_Code = SP.NR_Code AND NR.Language_Code = '$Language_Code$'" & vbCrLf & _
                                "LEFT OUTER JOIN Cap_ProjectDescription PD ON PD.PD_Code = SP.PD_Code AND PD.Language_Code = '$Language_Code$'" & vbCrLf & _
                                "INNER JOIN Cap_SalesStatus SS ON SS.SS_Code = SP.SS_Code AND SS.Language_Code = '$Language_Code$'" & vbCrLf & _
                                "LEFT OUTER JOIN Cap_StratAccount SA ON SA.SA_Code = SP.SA_Code AND SA.Language_Code = '$Language_Code$'" & vbCrLf & _
                                "INNER JOIN Cap_ProjectRep CPR ON CPR.SP_Capkey = SP.SP_Capkey AND CPR.Language_Code = 'E'" & vbCrLf & _
                                "INNER JOIN Cap_RepRole CRR ON CRR.RR_Code = CPR.RR_Code AND CRR.BU_GROUPING = 'X2' AND CRR.Language_Code = 'E'" & vbCrLf & _
                                "INNER JOIN Sales_Reps SR ON SR.SR_Code = CPR.SR_Code AND SR.Language_Code = '$Language_Code$'" & vbCrLf & _
                                "INNER JOIN Cap_SystemClassif CSC ON CSC.SC_Code = SYS.SC_Code AND CSC.Language_code = '$Language_Code$'" & vbCrLf & _
                                "WHERE EE.USER_ID = '$User_ID$' " & vbCrLf & _
                                "ORDER BY EE.Code "
                         
    Call ArmExcel1.LoadConstants(EXptSQL, "cap_ExportExcel_Fields_lst2 'Cap_ExportExcel_Product','ArmExcel','$Language_code$'", EXctFields)
    
    Call ArmExcel1.LoadConstants(EXptSQL, "screen_csts_lst2 'Cap_ExportExcel_Select', 'ArmExcel', '$Language_code$'", EXctControl)
    
    Call ArmExcel1.LoadConstants(EXptStatic, Join(Array("Enter the name of the preselection (only 15 chars) :", _
                                                 "This name is already used, please choose another", _
                                                 "Are you sure to delete this item : "), CH_LDELIMIT), EXctMessages)
    Call ArmExcel1.LoadPreselections

Trace_End:
    #If CapDebug Then
        If Not go_Trace Is Nothing Then go_Trace.WriteTraceProc False, "ExportExcelForm:EExportProduct_Search"
    #End If
    CapMouseOn
    Exit Sub
    
Trace_Err:
    If Not go_Trace Is Nothing Then go_Trace.WriteTraceError Err.Number, Err.Description, "ExportExcelForm:ExportProduct_Search"
    CapMouseOn

End Sub

Public Sub ExportAction_Search()
    #If CapDebug Then
        If Not go_Trace Is Nothing Then go_Trace.WriteTraceProc True, "ExportExcelForm:ExportAction_Search"
    #End If
    CapMouseOff
    
    ArmExcel1.User_ID = ms_UID
    
    ArmExcel1.InsertTempKeyCount = 10
    
    ArmExcel1.CreateTempTableRequest = "CREATE TABLE [#Cap_ExcelExport] ([Code] [int] Identity(1,1),[User_ID] [varchar] (30) NULL ,[CapKey1] [varchar] (20) NULL) ON [PRIMARY]"
    ArmExcel1.DropTempTableRequest = "DROP TABLE [#Cap_ExcelExport]"
    ArmExcel1.InsertTempKeyRequest = "exec Cap_ExcelExport_ins4 '$User_ID$','$0$','$1$','$2$','$3$','$4$', '$5$', '$6$', '$7$', '$8$', '$9$'"
    
    ArmExcel1.MaxPreselections = 10
    
    ArmExcel1.PreSelRequest_Ins = "Cap_ExportExcelPreSel_ins '$User_ID$','ACTION','$PreSelName$','$PreSelFields$'"
    ArmExcel1.PreSelRequest_Del = "Cap_ExportExcelPreSel_del '$User_ID$','ACTION','$PreSelName$'"
    ArmExcel1.PreSelRequest_Sel = "Cap_ExportExcelPreSel_lst '$User_ID$','ACTION'"
    '$Language_Code$'
    ArmExcel1.MultiKeyRequest = "SELECT $SelectFields$ " & vbCrLf & _
                                "FROM #Cap_ExcelExport EE " & vbCrLf & _
                                "INNER JOIN Cap_Action CA ON CA.AC_Code = EE.Capkey1 AND CA.Language_Code = 'E'" & vbCrLf & _
                                "INNER JOIN Cap_ActionType CAT ON CAT.AT_Code = CA.AT_Code AND CAT.Language_Code = '$Language_Code$'" & vbCrLf & _
                                "INNER JOIN Cap_ActionStatus CAS ON CAS.AS_Code = CA.AS_Code AND CAS.Language_Code = '$Language_Code$'" & vbCrLf & _
                                "INNER JOIN Cap_ActionReason CAR ON CAR.AR_Code = CA.AR_Code AND CAR.Language_Code = '$Language_Code$'" & vbCrLf & _
                                "LEFT OUTER JOIN Cap_ActionContact CAC ON CAC. AC_Code = CA.AC_Code" & vbCrLf & _
                                "LEFT OUTER JOIN Cap_Contact CNT ON CNT.CNT_Code = CAC.CNT_Code" & vbCrLf & _
                                "LEFT OUTER JOIN Unit_Of_Measure UOM ON UOM.UM_Code = CA.UM_Code AND UOM.Language_Code = '$Language_Code$'" & vbCrLf & _
                                "INNER JOIN Claim_Customer CC ON CC.CCU_Capkey = CA.CCU_Capkey" & vbCrLf & _
                                "LEFT OUTER JOIN Cap_ActionProject CAP ON CAP.AC_Code = CA.AC_Code" & vbCrLf & _
                                "LEFT OUTER JOIN Sales_Project SP ON SP.SP_Capkey = CAP.SP_Capkey" & vbCrLf & _
                                "LEFT OUTER JOIN Cap_SalesStatus CSS ON CSS.SS_Code = SP.SS_Code AND CSS.Language_Code = '$Language_Code$'" & vbCrLf & _
                                "INNER JOIN Countries C ON C.CT_Code = CC.CT_Code AND C.Language_Code = '$Language_Code$'" & vbCrLf & _
                                "INNER JOIN Cap_CompanyRep CCR ON CCR.CCU_Capkey = CC.CCU_Capkey" & vbCrLf & _
                                "INNER JOIN Cap_RepRole CRR ON CRR.RR_Code = CCR.RR_Code AND CRR.BU_GROUPING LIKE 'X%' AND CRR.Language_Code = '$Language_Code$'" & vbCrLf & _
                                "INNER JOIN Sales_Reps SR ON SR.SR_Code = CCR.SR_Code AND SR.Language_Code = 'E'" & vbCrLf & _
                                "$SpecialJoin1$" & vbCrLf & _
                                "WHERE EE.USER_ID = '$User_ID$' " & vbCrLf & _
                                "ORDER BY EE.Code "
                                
                                
    Dim lr_SampleJoin As Variant
    lr_SampleJoin = Array("ASD.SD_Code®©SD.SD_Desc®©ASD.ASD_Qty®©ASD.HandOrFullFlag®©PE.PE_Desc®©ASD.ASD_Comment", "", "LEFT OUTER JOIN Cap_ActionSampleDoc ASD ON ASD.AC_Code = CA.AC_Code" & vbCrLf & _
                                "LEFT OUTER JOIN Cap_SampleDoc SD ON SD.SD_Code = ASD.SD_Code AND SD.Language_Code = '$Language_Code$'" & vbCrLf & _
                                "LEFT OUTER JOIN Products_Edges PE ON PE.PE_Code = ASD.PE_Code AND PE.Language_Code = '$Language_Code$'" & vbCrLf, "")
    ArmExcel1.SpecialFields = Array(lr_SampleJoin)
                         
    Call ArmExcel1.LoadConstants(EXptSQL, "cap_ExportExcel_Fields_lst2 'Cap_ExportExcel_Action','ArmExcel','$Language_code$'", EXctFields)
    
    Call ArmExcel1.LoadConstants(EXptSQL, "screen_csts_lst2 'Cap_ExportExcel_Select', 'ArmExcel', '$Language_code$'", EXctControl)
    
    Call ArmExcel1.LoadConstants(EXptStatic, Join(Array("#Enter the name of the preselection (only 15 chars) :", _
                                                 "This name is already used, please choose another", _
                                                 "Are you sure to delete this item : "), CH_LDELIMIT), EXctMessages)
    Call ArmExcel1.LoadPreselections

Trace_End:
    #If CapDebug Then
        If Not go_Trace Is Nothing Then go_Trace.WriteTraceProc False, "ExportExcelForm:ExportAction_Search"
    #End If
    CapMouseOn
    Exit Sub
    
Trace_Err:
    If Not go_Trace Is Nothing Then go_Trace.WriteTraceError Err.Number, Err.Description, "ExportExcelForm:ExportAction_Search"
    CapMouseOn

End Sub


' ************************************************************************************
' ********************** ERROR-HANDLING SUPPORT FUNCTIONS ****************************
' ************************************************************************************
#If LIVE = 1 Then
Private Function GetDbError(ByVal lo_Db As Object) As String
#Else
Private Function GetDbError(ByVal lo_Db As ARMSYSCOMLib.ArmDb) As String
#End If
On Error GoTo ErrHandler
    If IsArray(lo_Db.SQLErrorMessages) Then
        Debug.Assert (IsArray(lo_Db.SQLErrorCodes))
        ' Display errors msgBox
        GetDbError = Join(lo_Db.SQLErrorCodes, ",") & vbCrLf & Join(lo_Db.SQLErrorMessages, vbCrLf)
    Else
        ' ExecuteSQL failed but no error message?
        GetDbError = "Unknown error"
    End If
    Exit Function
ErrHandler:
    Call ErrorHandler("GetDbError()")
End Function

' Standard error handler
Private Sub ErrorHandler(ByVal as_Fct As String)
    Call Err.Raise(Err.Number, as_Fct & SEP1 & Err.Source, Err.Description)
End Sub

' display standard error message
Private Sub ErrorMessage(ByVal as_Fct As String)
    If Err.Number = QuietException Then Exit Sub
    Dim ll_oldMP As MousePointerConstants
    
    'save mouse pointer
    ll_oldMP = Screen.MousePointer
    Screen.MousePointer = vbDefault
    
    Dim ls_ErrSource As String
    Dim ls_ErrDescription As String
    ls_ErrSource = as_Fct & SEP1 & Err.Source
    ls_ErrDescription = Err.Description
    
    Call LogMessage(App.ProductName & " exception raised. Err.Number:" & Err.Number & ", Err.Source:" & ls_ErrSource & ", Err.Description " & ls_ErrDescription & ".", "E", False)
    Call MsgBox("Error occured, please contact IT" & vbCrLf & ls_ErrSource & vbCrLf & "Description: " & ls_ErrDescription, , "Error message: " & as_Fct)
    
    'restore mouse pointer
    Screen.MousePointer = ll_oldMP
End Sub


Private Sub LogMessage(ByVal as_logMsg As String, Optional ByVal as_logType As String = "I", Optional ab_throwException As Boolean = True)
On Error GoTo ErrHandler
Const InsertReq As String = "INSERT INTO A_Log (U_code, Z_creation_date, Source , Log_type, Log_Msg ) VALUES ($UCODE$, GETDATE(), '$APP$', '$LOGTYPE$', '$MSG$')"
    Dim ls_Req As String
    Dim ll_Cursor As Long
    
    ls_Req = Replace(InsertReq, "$UCODE$", ms_UID)
    ls_Req = Replace(ls_Req, "$APP$", SQLStr(C_APPNAME & " " & App.Title & " " & App.Major & "." & App.Minor & "." & App.Revision, 50))
    ls_Req = Replace(ls_Req, "$MSG$", SQLStr(as_logMsg, 4000))
    ls_Req = Replace(ls_Req, "$LOGTYPE$", SQLStr(as_logType), 1)
    
    Call ExecuteSQLSafe(mo_ArmDb, ls_Req)
    
    Exit Sub
ErrHandler:
    If ab_throwException Then Call ErrorHandler("LogMessage()")
End Sub


' ************************************************************************************
' **************************** DB-ACCESS FUNCTIONS ***********************************
' ************************************************************************************
#If LIVE = 1 Then
Private Sub ExecuteSQLSafe(ByVal ao_DB As Object, ByVal as_Req As String)
#Else
Private Sub ExecuteSQLSafe(ByVal ao_DB As ARMSYSCOMLib.ArmDb, ByVal as_Req As String)
#End If

    If Not ao_DB.ExecuteSQL(as_Req) Then
        Call Err.Raise(CompFncFailed, "ExecuteSQLSafe", "SQL Error: " & GetDbError(ao_DB))
    End If

End Sub


#If LIVE = 1 Then
Private Function OpenSQLSafe(ByVal ao_DB As Object, ByVal as_Req As String) As Long
#Else
Private Function OpenSQLSafe(ByVal ao_DB As ARMSYSCOMLib.ArmDb, ByVal as_Req As String) As Long
#End If

    OpenSQLSafe = ao_DB.OpenSQL(as_Req)
    
    If OpenSQLSafe = 0 Then Call Err.Raise(CompFncFailed, "OpenSQLSafe", "SQL Error: " & GetDbError(ao_DB))

End Function


Private Function SQLStr(ByVal as_str As String, Optional ByVal al_MaxLen As Long = 8000) As String
    SQLStr = Replace(Left(as_str, IIf(Len(as_str) <= al_MaxLen, Len(as_str), al_MaxLen)), "'", "''")
End Function

