User:Tonesbones14/sandbox

/==/ setup Sub setup_ Dim st_dt As String Dim acct_open As String Dim acct_end As String Application.ScreenUpdating = False Application.DisplayAlerts = False st_dt = Range("F2").Value acct_open = Range("F3").Value acct_end = Range("F4").Value Range("F6").Select Selection.Copy Range("F7").Select Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False Range("F7").Select With Selection .HorizontalAlignment = xlGeneral .VerticalAlignment = xlBottom .WrapText = False .Orientation = 0 .AddIndent = False .IndentLevel = 0 .ShrinkToFit = True .ReadingOrder = xlContext .MergeCells = False End With Dim ToReplace As String Dim Replacement As String Dim iteration As Long Dim myCell As Range Set myCell = Range("F7") iteration = 1 ToReplace = "#month_start_dt#" Replacement = st_dt myCell.Value = Replace(Expression:=myCell.Value, Find:=ToReplace, Replace:=Replacement, Count:=iteration) ToReplace = "#acct_open_dt_start#" Replacement = acct_open myCell.Value = Replace(Expression:=myCell.Value, Find:=ToReplace, Replace:=Replacement, Count:=iteration) iteration = 2 ToReplace = "#acct_open_dt_end#" Replacement = acct_end myCell.Value = Replace(Expression:=myCell.Value, Find:=ToReplace, Replace:=Replacement, Count:=iteration) Range("F7").Select Selection.Copy Range("F10").Select Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False Application.ScreenUpdating = True Application.DisplayAlerts = True End Sub

Sub Create_Cust_List_Query Dim M, qName, qDesc As String Dim qry As WorkbookQuery Dim currentSheet As Worksheet Dim sname, swap As String Dim acct_list As String Dim i, j As Integer Dim edit_sql As String Application.ScreenUpdating = False Application.DisplayAlerts = False

'Fetch Acct_Num sname = "Raw_Acquisition_MTD"

Sheets(sname).Select

Range("A1").Select Selection.End(xlDown).Select j = ActiveCell.Row i = 1

Range("A2").Select acct_list = "'" & ActiveCell.Value & "','" While i < j   ActiveCell.Offset(1, 0).Select acct_list = acct_list & ActiveCell.Value & "','" i = i + 1 Wend 'remove last comma acct_list = Left(acct_list, Len(acct_list) - 5) acct_list = "In(" & acct_list & ")" sname = "Cust_List" swap = "'#acct_num#'" Sheets(sname).Select edit_sql = Range("F7").Value edit_sql = Replace(edit_sql, swap, acct_list) Range("F10").Value = edit_sql M = edit_sql ' We load the query defintions from the Menu worksheet sheet_name = "Cust_List" Sheets(sheet_name).Select qName = Range("D10").Text qDesc = Range("E10").Text If DoesQueryExist(qName) Then ' This query already exists We will delete it first Delete_Cust_List_Query ' In case we have worksheet that was created by this macro for the new query, let's delete it       CleanSheet (qName) End If   ' The new interface to create a new Power Query query. It gets as an input the query name, M formula and description Set qry = ThisWorkbook.Queries.Add(qName, M, qDesc) shouldLoadToDataModel = Range("G10") shouldLoadToWorksheet = Range("H10") ' We get from the first worksheets all the data in order to know which query to delete, including its worksheet, connection and Data Model is needed If shouldLoadToWorksheet Then ' We add a new worksheet with the same name as the Power Query query Set currentSheet = Sheets.Add(After:=ActiveSheet) currentSheet.Name = qName If Not shouldLoadToDataModel Then ' Let's load to worksheet only LoadToWorksheetOnly qry, currentSheet Else ' Let's load to worksheet and Data Model LoadToWorksheetAndModel qry, currentSheet End If   ElseIf shouldLoadToDataModel Then ' No need to load to worksheet, only Data Model LoadToDataModel qry End If   Application.ScreenUpdating = True Application.DisplayAlerts = True 'ThisWorkbook.RefreshAll MsgBox "Power Query updated and refreshed" End Sub

Sub Delete_Cust_List_Query Dim M As String, qName As String, qDesc As String Dim qry As WorkbookQuery Dim answer As VbMsgBoxResult Dim LoadToDataModel As Boolean Dim loadToWorksheet As Boolean Dim currentSheet As Worksheet Dim sheet_name As String Application.ScreenUpdating = False Application.DisplayAlerts = False ' We load the query defintions from the Menu worksheet sheet_name = "Cust_List" Sheets(sheet_name).Select qName = Range("D10").Text qDesc = Range("E10").Text M = Range("F10").Text shouldLoadToDataModel = Range("G10") shouldLoadToWorksheet = Range("H10") ' We get from the first worksheets all the data in order to know which query to delete, including its worksheet, connection and Data Model is needed If shouldLoadToDataModel Or shouldLoadToWorksheet Then Dim con As WorkbookConnection Dim conString As String For Each con In ThisWorkbook.Connections If Not con.InModel Then ' This is not a Data Model conenction. We created this connection without the "Power Query - " prefix, to determine if we should delete it, let's check the connection string If Not IsNull(con.OLEDBConnection) Then ' This is a OLEDB Connection. Good chance it is our connection. Let's check the connection string conString = con.OLEDBConnection.Connection Dim prefix As String prefix = "Provider=Microsoft.Mashup.OleDb.1;" If (Left(conString, Len(prefix)) = prefix) And (0 < InStr(conString, "Location=" & qName)) Then ' This is our connection ' It starts with "Provider=Microsoft.Mashup.OleDb.1;" and contains "Location=" with our query name. This is our connection. con.Delete End If               End If            ElseIf (InStr(1, con.Name, "Query - " & qName)) Then ' We created this connection with "Power Query - " prefix, so we can this connection con.Delete End If       Next End If   If shouldLoadToWorksheet Then CleanSheet (qName) End If   If DoesQueryExist(qName) Then ' Deleting the query Set qry = ThisWorkbook.Queries(qName) qry.Delete End If   Application.ScreenUpdating = True Application.DisplayAlerts = True End Sub

let Source = Sql.Database("EDWDB-APS.PROD.CAN,17001", "EDW_PROD", [Query="SELECT DISTINCT in   Source
 * 1) (lf)t2.ACCOUNT_KEY,
 * 2) (lf)t2.ACCT_NUM,
 * 3) (lf)t2.ACCT_STATUS_KEY,
 * 4) (lf)t4.CREDIT_SCORE,
 * 5) (lf)t5.ACCEPTED_CREDIT_LIMIT_AMT,
 * 6) (lf)t1.ACCT_OPEN_DT,
 * 7) (lf)t1.ACCT_CLOSE_DT
 * 8) (lf)FROM
 * 9) (lf)EDW_VIEWS.F_CRDT_CARD_KEY_MEAS_DAILY as t1,
 * 10) (lf)EDW_VIEWS.F_ACCT_PRFL as t2
 * 11) (lf)LEFT JOIN EDW_VIEWS.D_CRDT_CARD_APPLICATION as t3 ON t2.ACCT_NUM = t3.ACCOUNT_NUM
 * 12) (lf)LEFT JOIN EDW_VIEWS.F_CRDT_CARD_APPLICANT_PRFL as t4 ON t3.CRDT_CARD_APPLICATION_KEY = t4.CRDT_CARD_APPLICATION_KEY
 * 13) (lf)LEFT JOIN EDW_VIEWS.F_CRDT_CARD_APPLICATION_SNP as t5 ON t4.CRDT_CARD_APPLICATION_KEY = t5.CRDT_CARD_APPLICATION_KEY
 * 14) (lf)WHERE
 * 15) (lf)t1.ACCOUNT_KEY = t2.ACCOUNT_KEY
 * 16) (lf)AND t1.CUSTOMER_KEY = t2.CUSTOMER_KEY
 * 17) (lf)AND t1.ACCT_CLOSE_DT IS NULL
 * 18) (lf)AND t1.ACCT_OPEN_DK <> t1.ACCT_CLOSE_DK
 * 19) (lf)AND t1.SNAPSHOT_DT >= '10/1/2019'
 * 20) (lf)AND t1.ACCT_OPEN_DT >= '10/1/2019'
 * 21) (lf)"])