1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
| Sub import()
'imports from the
Dim LastRow As Long
Dim LastColumn As Long
Dim FName As Variant
'Query for the file to open
FName = Application.GetOpenFilename()
If FName = False Then
MsgBox "You didn't choose a file"
Else
'MsgBox FName
End If
'Add a new sheet and import the csv data
Sheets.Add
ActiveSheet.Name = "cases-dump"
Range("A1").Select
With ActiveSheet.QueryTables.Add(Connection:= _
"TEXT;" & FName, _
Destination:=Range("$A$1"))
.Name = "cases-dump"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.TextFilePromptOnRefresh = False
.TextFilePlatform = 437
.TextFileStartRow = 1
.TextFileParseType = xlDelimited
.TextFileTextQualifier = xlTextQualifierDoubleQuote
.TextFileConsecutiveDelimiter = False
.TextFileTabDelimiter = True
.TextFileSemicolonDelimiter = False
.TextFileCommaDelimiter = True
.TextFileSpaceDelimiter = False
.TextFileColumnDataTypes = Array(1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, _
1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1)
.TextFileTrailingMinusNumbers = True
.Refresh BackgroundQuery:=False
End With
'Find the last Filled row of the range
If WorksheetFunction.CountA(Cells) > 0 Then
'Search for any entry, by searching backwards by Rows.
LastRow = Cells.Find(What:="*", After:=[A1], SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
MsgBox "Importing: " & LastRow & " Operations"
End If
Range("A:D,F:F,G:G,H:I,K:M,O:P,V:AI,AO:AZ").Select
Selection.delete Shift:=xlToLeft
Columns("A:A").Select
Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
Columns("C:C").Select
Selection.Cut
Columns("A:A").Select
ActiveSheet.Paste
Columns("B:B").EntireColumn.AutoFit
Columns("C:C").Select
Selection.delete Shift:=xlToLeft
Selection.Cut
Columns("N:N").Select
ActiveSheet.Paste
Columns("C:C").Select
Selection.delete Shift:=xlToLeft
Range("N1").Select
ActiveCell.FormulaR1C1 = "rvu"
Range("N2").Select
ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-6],rvu!R1C[-13]:R7240C[-12],2)"
Range("N2").Select
Selection.AutoFill Destination:=Range("N2:N" & LastRow), Type:=xlFillDefault
Columns("N:N").Select
Selection.Copy
Columns("O:O").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Columns("N:N").Select
Application.CutCopyMode = False
Selection.delete Shift:=xlToLeft
Selection.Replace What:="#N/A", Replacement:="0", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Range("A6").Select
LastRow = Cells.Find(What:="*", After:=[A1], SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
LastColumn = Cells.Find(What:="*", After:=[A1], SearchOrder:=xlByColumns, SearchDirection:=xlPrevious).Column
Sheets.Add.Name = "pivot"
ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
"cases-dump!R1C1:R" & LastRow & "C" & LastColumn, Version:=xlPivotTableVersion12).CreatePivotTable _
TableDestination:="pivot!R3C1", TableName:="PivotTable1", DefaultVersion _
:=xlPivotTableVersion12
Sheets("pivot").Select
Cells(3, 1).Select
With ActiveSheet.PivotTables("PivotTable1").PivotFields("Procedure Date")
.Orientation = xlRowField
.Position = 1
End With
ActiveSheet.PivotTables("PivotTable1").AddDataField ActiveSheet.PivotTables( _
"PivotTable1").PivotFields("rvu"), "Sum of rvu", xlSum
Range("A6").Select
Selection.Group Start:=True, End:=True, Periods:=Array(False, False, False, _
False, True, False, True)
With ActiveSheet.PivotTables("PivotTable1").PivotFields("Years")
.Orientation = xlColumnField
.Position = 1
End With
End Sub |