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
| %let work = %sysfunc(pathname(WORK));
%let vbs = &work.\loadsheets.vbs;
filename vbs "&vbs";
data _null_;
file vbs;
input;
put _infile_;
cards4;
path = WScript.Arguments(0)
pattern = WScript.Arguments(1)
outfile = WScript.Arguments(2)
' convert pattern (presumed to be DOSish) to regex pattern
regex = Replace (pattern, ".", "\.")
regex = Replace (regex, "*", ".*")
regex = Replace (regex, "?", ".")
regex = "^" & regex & "$"
' scan the files in the path, keeping track of which ones
' match the pattern in an array named source
Set fs = CreateObject("Scripting.FileSystemObject")
Set folder = fs.GetFolder (path)
Set files = folder.Files
Set rx = New RegExp
rx.Pattern = regex
rx.IgnoreCase = true
Dim source()
count = -1
For Each f in files
if rx.Test (f.Name) then
count = count + 1
ReDim preserve source(count)
source(count) = f.Name
end if
Next
if count = -1 then WScript.Quit
' might want to sort source(), but vbs has no sort function!
' so, forget about it
' open excel and make it work for us
' read in each source (html) into a new sheet,
' copy the whole thing and
' paste it into a new sheet in the workbook
Set xl = CreateObject("Excel.Application")
xl.Workbooks.Close
Set book = xl.Workbooks.Add
For i = lbound(source) to ubound(source)
Set sheet = book.Sheets.Add(,book.Sheets(i+1))
sheet.Name = Left (source(i), Len(source(i))-13)
Set parse = xl.Workbooks.Open (path &"\"& source(i))
parse.Sheets(1).UsedRange.Copy
sheet.Paste
xl.CutCopyMode = false ' Clear clipboard so big clipboard dialog will not appear when closing parse
parse.Close
sheet.Range("A1:A1").Select
Next
book.Sheets(1).Visible = false
book.Sheets(1).Activate
book.SaveAs (outfile)
xl.Quit
;;;;
run;
filename vbs;
options noxwait xsync xmin;
%let vbs = loadsheets.vbs;
x cd "&work";
x "&vbs ""H:\12_TDB\tdb_RFA\tx_equipement_cars\rpt"" ""eqpm_cars_eer*&jour..xls"" ""H:\12_TDB\tdb_RFA\tx_equipement_cars\rpt\eqpm_cars_eer_&jour..xls"""; |
Grouper des documents EXCEL
Répondre avec citation
Envoyé par marion.bouilly
Partager