Said a single example
Summary:
How will the directory of all of the specified workbook chapter ii data summary to the total in the table
CodePudding user response:
Dim WKST As Worksheet, WKBK As Workbook, sumA As Currency, sumB As Currency
The Set of fso=CreateObject (" Scripting. Filesystemobject ")
For Each file In fso. Getfolder (" D: "). The Files' to find the Files In the specified directory (does not contain subdirectories)
If LCase (Right (file. Shortname, 3))="XLS Or _
"Mid (LCase (StrReverse (file. Shortname)), 2, 3)="SLX" Then
WKBK=Application. Workbooks. Open (the file Path) '. If the file name is XLS or. XLS? At the end of the
WKST=WKBK. Sheets (2) the 'open a workbook, on the second sheet of column c
WKST. Cells (1, 3). The Formula="=sumif (A: A," "" "A, B, B) "' by Formula sumif for" A "and" B "
WKST. Cells (2, 3). The Formula="=sumif (A: A," "b", "b: b)" 'conditional sum (assuming the data in the AB two columns)
SumA=sumA + WKST. Cells (1, 3) 'formula result accumulation in the summary data
SumB=sumB + WKST. Cells (2, 3)
WKBK. Close
End the If
Next
The Debug. Print sumA, sumB
CodePudding user response:
Do you want to use VBA to realize?In fact, it not be so hard to find,
1. The folder:
Simple and crude, the method of using Inputbox () function, let the customer input to deal with the directory,
If you want to do advanced, humanization, then can use the API to achieve folder selection dialog box;
You can also use the Shell object selection dialog box; More directly, can use the Application. The FileDialog to implement choice,
In the VBA environment, recommended Application. FileDialog implementation,
2. The file:
Have in the previous step to choose the "directory", after the Dir $() function to enumerate all the XLSX files in the specified directory (or the.xls files),
Simple point is enumerated a processing, all first enumeration, of course, to an array of strings, and then processing in order to also go,
Open the workbook, with Workbooks. The Open () function interface,
3. The so-called "second form" :
If is sure "table object name" Sheet2, so can write when "operation work table" death to Sheet2,
Not sure, then must be "work table name" (refers to open the document after see the text on the "table label") is a unified,
Such as called "Sheet2", or "xx data record" and so on, depends on your specific situation,
This case, you can use the corresponding workbook object. The Sheets (2), the Sheets (" Sheet2 "), or the Sheets (" xx data record "),
To select a way to "determine the work table objects",
4. Read the cell data
There are many ways, such as use objSheet. Range (" C6 "). The Value can be read specified job C6 cell values in the table;
Can also use objSheet. Cells (6, 3). The Value in the table to read the assigned work C6 cell values,
In the Range (), Cells () parameters can be used in a variable's value, so you can easily use "cycle" to deal with all the cell,
5. The "results"
In the above "4." read out data, to "statistical" stored in the array,
Again in statistics after all of the files, "write" a specified (or "new") in the worksheet,
In the above said in "4." is "read", actually came to operate: the "assignment", is to write data,