Churn_Create_SF_Data_by_Cus

Create_SF_Data_by_Cus.vb

Sub Create_SF_Data_by_Cus()
    Dim cn, objRes
    Dim connectionString As String
    Dim icount As Integer
    
    On Error GoTo ErrHandler
    Set cn = CreateObject("ADODB.connection")
    connectionString = "Provider=Microsoft.ACE.oleDB.12.0; Data source = " & ThisWorkbook.FullName _
    & "; Extended Properties = ""Excel 12.0 Macro; HDR = NO, IMEX =1"""
    
    With cn
        .connectionString = connectionString
        .Open
    End With
    
    Set objRes = cn.Execute("SELECT [Account Name], [Account Owner], [Type1] as [Field20], SUM([Value (Most Recent)]) as `Sum of Value (Most Recent)`, [Period]" _
        & "FROM [W2$] " _
        & "Where [Amount] <>0 " _
        & "GROUP BY [Account Name], [Account Owner],[Type1], [Period]" _
        & "ORDER BY [Account Name] ASC, [Period] ASC")
 
        
    Sheets("Salesforce data by Customer_New").Select
    [a2].CopyFromRecordset objRes
    [a1].Select
    For icount = 0 To objRes.Fields.Count - 1
        ActiveSheet.Cells(1, 1 + icount).Value = _
        objRes.Fields(icount).Name
    Next icount

    cn.Close
    Set cn = Nothing
    Set objRes = Nothing
ErrHandler:
    If Err.Number <> 0 Then
        MsgBox Err.Number & ": " & Err.Description
    End If

End Sub
'''''''''''''''''''''''''''''''''''''
Sub create_W1andW2()
Dim cn, objRes, objRes2
Dim strQuery As String

On Error GoTo ErrHandler

vbaOptimize True

Set cn = CreateObject("ADODB.connection")
With cn
    .connectionString = E_connectionString()
    .Open
End With

strQuery = " SELECT [Reed Ref2],[Close Date],[Type],[Ad Agency End Client],[Ad Agency End Client],[AA End Client A/C owner], ([Total Price]),([Total Price]),([Total Price]) ,[Contract Start Date],[Contract End Date],[Account: Last Activity],[AA End Client],[Industry],[Period],[Reed Ref2],[AA End Client A/C owner],[Fill zero last purchase],[Original Value (Most Recent)],[Account Owner origin]" _
    & "from  [Data$]" _
    & " where [Reed Ref2] IN (SELECT [Reed Ref2] from [Data$] where  [Reed Ref2] <> 'n/a'  AND [Reed Ref2] is NOT NULL) Order By [Ad Agency End Client] "
    
'strQuery = "Select * from [Data$]"
'strQuery = "Select * from [Data$] UNION ALL " & strQuery

Set objRes = cn.Execute(strQuery)

Sheets("W1").Select
[a2].CopyFromRecordset objRes

strQuery = "Select `Reed Ref`, `Close Date`, Type1, `Account Name`, `Ad Agency End Client`, `Account Owner`, [Total Price], [Total Price] as T_Price, [Total Price], `Contract Start Date`, `Contract End Date`, `Account: Last Activity`, Type as `Field20`, [Industry],[Period],[Reed Ref2],[AA End Client A/C owner],[Fill zero last purchase],[Original Value (Most Recent)],[Account Owner origin] from [Data$] Union ALL select * from [W1$] Order by [Account Name] Desc"
Set objRes = cn.Execute(strQuery)
Sheets("W2").Select
[a2].CopyFromRecordset objRes


cn.Close
Set cn = Nothing
Set objRes = Nothing
ErrHandler:
If Err.Number <> 0 Then
    MsgBox "Err No. " & Err.Number & " : " & Err.Description
End If
End Sub