-
Notifications
You must be signed in to change notification settings - Fork 1
/
CleanSlips_4.7.ahk
190 lines (153 loc) · 5.62 KB
/
CleanSlips_4.7.ahk
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
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
;Start;
FileDelete SLIPS.DOC
;Set vars
version = 4.7
;Check for TEMPLATE.DOC
IfNotExist, TEMPLATE.DOC
{
msgbox Cannot find TEMPLATE.DOC
exit
}
;Get input file
FileSelectFile, xlsFile_original,,C:\Users\%A_UserName%\Downloads\, CleanSlips %version% - Select File, *.xls*
;Check for input file or cancel to exit
If xlsFile_original =
{
exit
}
;Copy XLS to script folder
xlsFile_copy = %A_ScriptDir%\LendingRequestReport_copy.xls
FileCopy, %xlsFile_original%, %xlsFile_copy%, 1
;Open XLS file ----------------------------------------------------------------
Progress, zh0 fs12, Tidying up fields...,,CleanSlips %version%
xl := ComObjCreate("Excel.Application")
xl.Visible := False
book := xl.Workbooks.Open(xlsFile_copy)
ws := book.Worksheets(1)
rows := ws.UsedRange.Rows.Count
;Parse XLS fields
loop, %rows%
{
;Set original columns
title_col = A%A_Index%
avail_col = G%A_Index%
ship_col = I%A_Index%
;Create new columns
norm_call_number_col = S%A_Index%
avail_original_col = T%A_Index% ;Preserve original availability field for testing
comments_col = U%A_Index%
;Set headers for new columns
if A_Index = 1
{
ws.Range(norm_call_number_col).Value := "Normalized Call Number"
ws.Range(avail_original_col).Value := "Availability_Original"
ws.Range(comments_col).Value := "Comments"
continue
}
;Title
title := ws.Range(title_col).Value
title := SubStr(title, 1, 40) ;Shortens title to a max length of 40 chars
;Shipping note
shippingNote := ws.Range(ship_col).Value
RegExMatch(shippingNote, "\|\|(.*)\|\|.*\|\|", sn) ;Pulls out requestor's name from shippingNote
shippingNote := sn1
;Comments
comments := ws.Range(ship_col).Value
RegExMatch(comments, "(.*)\|\|.*\|\|.*\|\|.*", cm) ;Pulls out requestor's name from shippingNote
comment := cm1
;Availability
full_availability :=
availability_array :=
normalized_call_number_full :=
availability := ws.Range(avail_col).Value
availability_array := StrSplit(availability, "||")
for index, element in availability_array
{
;Skip if on loan
if element contains Resource Sharing Long Loan,Resource Sharing Short Loan
{
continue
}
;Library, location, and call number
RegExMatch(element, "(.*?),(.*?)\.(.*).*(\(\d{1,3} copy,\d{1,3} available\))", e)
library := e1
location := e2
call_number := e3
inventory := e4
;Normalize call number and add to ongoing string
normalized_call_number := normalize(call_number)
normalized_call_number_full := normalized_call_number_full . " | " . location . " | " . normalized_call_number
;Add availability note to ongoing string
full_availability := full_availability . "[" . location . " - " . call_number "] "
full_availability := RegExReplace(full_availability, " ]", "] ") ;Removes extra spaces around brackets
}
;Make final changes to XLS sheet
ws.Range(title_col).Value := title
ws.Range(avail_col).Value := full_availability
ws.Range(ship_col).Value := shippingNote
ws.Range(norm_call_number_col).Value := normalized_call_number_full
ws.Range(avail_original_col).Value := availability
ws.Range(comments_col).Value := comment
}
;Sort spreadsheet by normalized call number
xlAscending := 1
xlYes := 1
ws.UsedRange.Sort(Key1 := xl.Range("S2"), Order1 := xlAscending,,,,,, Header := xlYes)
;Save and quit XLS file
book.Save()
book.Close
xl.Quit
;Open DOC file ----------------------------------------------------------------
Progress, zh0 fs12, Performing MailMerge...,,CleanSlips %version%
template = %A_ScriptDir%\TEMPLATE.DOC
saveFile = %A_ScriptDir%\SLIPS.DOC
wrd := ComObjCreate("Word.Application")
wrd.Visible := False
;Perform mail merge
doc := wrd.Documents.Open(template)
doc.MailMerge.OpenDataSource(xlsFile_copy,,,,,,,,,,,,,"SELECT * FROM [Sheet0$]")
doc.MailMerge.Execute
;Save and quit DOC file
wrd.ActiveDocument.SaveAs(saveFile)
wrd.DisplayAlerts := False
doc.Close
wrd.Quit
;Delete spreadsheets
FileDelete %xlsFile_original%
FileDelete %xlsFile_copy%
;Finish
Progress, zh0 fs12, Sending to Word...,,CleanSlips %version%
IfNotExist, SLIPS.DOC
{
msgbox Cannot find SLIPS.DOC
exit
}
run winword.exe SLIPS.DOC
; FUNCTIONS -------------------------------------------------------------------
;Takes a string and pads it with a number of trailing characters
add_trailing(str, count, pad)
{
loop, %count%
{
str := str . pad
}
return str
}
;Takes a call number string and normalizes for alpha numeric sorting
;*Credit to Bill Dueber at http://robotlibrarian.billdueber.com)
normalize(call_number)
{
;Normalize call number (*Credit to Bill Dueber at http://robotlibrarian.billdueber.com)
RegExMatch(call_number, "x)^\s*([A-Z]{1,3})\s*(\d+(?:\s*\.\s*\d+)?)?\s*(?:\.?\s*([A-Z]+)\s*(\d+)?)?(?:\.? \s*([A-Z]+)\s*(\d+)?)?\s*(.*?)\s*$", m)
alpha := Format("{:-3s}", m1)
num := Format("{:09.4f}", m2)
c1alpha := Format("{:-2s}", m3)
c1num := add_trailing(m4, 4 - StrLen(m4), 0)
c1num := Format("{:4s}", c1num)
c2alpha := Format("{:-2s}", m5)
c2num := add_trailing(m6, 4 - StrLen(m6), 0)
c2num := Format("{:4s}", c2num)
extra := Format(" {:s}", m7)
normalized_call_number = %alpha%%num%%c1alpha%%c1num%%c2alpha%%c2num%%extra%
return normalized_call_number
}