Option Explicit
Sub Add_Data_From_Raw()
'declare local variable
Dim cn As ADODB.Connection, strQuery As String, rst As ADODB.Recordset, strConn As String
Dim source1 As String
'stop screen updating
Application.ScreenUpdating = False
'Defines the path of the database from data will be collected
source1 = ThisWorkbook.Path & "\" & "Raw_Dump.xls" 'you may change it
'check file exist or not!!!
If Len(Dir(source1)) = 0 Then
MsgBox "Dump Not found", vbCritical
Exit Sub
End If
'clear & resize the table1 to add new data
Range("Table1").Clear
ActiveSheet.ListObjects("Table1").Resize Range("$A$1:$K$2")
'here we will do a sql query to get data from the closed file
'however same could also be process with excel workbook open method
'Making a connection string
strConn = "Provider=Microsoft.ACE.OLEDB.12.0;" & _
"Data Source=" & source1 & ";" & _
"Extended Properties=""Excel 12.0;HDR=Yes"";"
'set a sql connection
Set cn = New ADODB.Connection
'open sql connection
cn.Open strConn
'create an SQL statement as per data needs to be extracted
'based on sheet name & column header on source file
strQuery = "SELECT [Year Desc] , [Quarter Desc], [Management Cluster Name], [Cuic], [Popline Id], [Popline Desc]," & _
"[Lvl 3 HW Top Box/SW Function Desc], [Account ID], [Customer/Inter/Intra] FROM [Dump$A:Q];"
'set a recorder to get data
Set rst = New ADODB.Recordset
'open recorder
'recordset.Open Source, ActiveConnection, CursorType, LockType, Options
rst.Open strQuery, cn, adOpenStatic, adLockReadOnly, adCmdText
'paste data to A2
ActiveSheet.Cells(2, 1).CopyFromRecordset rst
'close the recorder
rst.Close
'free memory
Set rst = Nothing
'close the connection
cn.Close
'free memory
Set cn = Nothing
'format the data on current sheet
With Range("A1").CurrentRegion
.Borders.LineStyle = xlContinuous
.Borders.ColorIndex = xlAutomatic
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlCenter
.Font.Name = "Calibri"
.Font.FontStyle = "Regular"
.Font.Size = 11
Cells.EntireColumn.AutoFit
End With
'apply the formulas
Range("Table1[Period]") = "=IFERROR(LOOKUP(RIGHT(B2)*1,{1,2,3,4},{""Jan"",""April"",""July"",""Oct""})&A2,"""")"
Range("Table1[Super Summary]") = _
"=INDEX('Product Hierarchy'!B$1:B$1137,MATCH('Sample data'!$E2,'Product Hierarchy'!$A$1:$A$1137,0))"
Range("Table1[Core Summary]") = _
"=INDEX('Product Hierarchy'!C$1:C$1137,MATCH('Sample data'!$E2,'Product Hierarchy'!$A$1:$A$1137,0))"
'change formulas to values
Range("Table1[[Period]:[Core Summary]]").Value = Range("Table1[[Period]:[Core Summary]]").Value
'delete last blank row
Range("Table1").Rows(Range("Table1").Rows.Count).Delete
'chnage month format in period col
Range("Table1[Period]").NumberFormat = "[$-409]mmm/yy;@"
'adjust the columns to get it fit
Columns.AutoFit
'This will refresh all pivot so need adjust the same need
'ActiveWorkbook.RefreshAll
'start screen updating
Application.ScreenUpdating = True
End Sub