VBA how to open all workbooks in one folder 20

sub opens ().

dim mypath$, myfile$, ak as workbook

false 'Freeze the screen to prevent the screen from shaking.

mypath = "c:\a\" 'Enter your path here, which is the folder where you store your workbook.

myfile = dir(mypath & "*.xlsx") 'Search for *. in the specified path in orderxlsx file.

do while myfile <>"" 'Loops when there are files in the specified path.

if myfile <>then

set ak = & myfile) 'Open the file that meets the requirements.

end if

Insert here the ** you want to work on.

Here you can choose whether to save the parameter or not, or you can delete this line ** and close the file manually.

myfile = dir 'Find the next *xlsx file.

loop = true 'Unfreeze the screen, which is usually used in pairs.

end sub

dim xlbook as

dim xlsheet as

falseif msgbox("The data sheet that needs to be manipulated is: excel2003 format, please select: Yes!

chr(13) &"" & chr(13) &"The data table that needs to be manipulated is: excel2007 format, please select: no! , vbyesno, "Arctic Fox Tips!! ") = vbyes then

s = "\*.xls"

ss = 4

elses = "\*.xlsx"

ss = 5:

end if

f = dir( &s)

do while f > " "

if f <>then

set xlbook = & "\" & f) 'Open an existing excel workbook file.

for each sh in 'Go through the worksheet.

with sh

Own**.

end with

windows('Go back to the open workbook.

nextwindows(f).close (true)'Close the open workbook and save it.

f = dir

true

Finally, this effect doesn't seem to be clear, n ** in the same format, "copied to" a worksheet with the same format? How to reproduce? Is it still in the same format after copying?

It should be explained how to transfer (summarize) data from n source tables to summary tables:

The first is sequential appending: after copying each source table, paste the values into different rows of the summary table in the order of origin and as they are (keep the format and values, remove the formula). For example:

The source table 1 has 1 data a, the source table 2 has 1 data b, and the summary table is the two data a and b (this method does not require the summary table to have a format, the summary table is actually an overlay table with increasing records).

The second is positioning and summing: that is, after copying, each source table will accumulate values or other operations to the corresponding cells of the summary table. For example:

The source table 1 has 1 data 5, the source table 2 has 1 data 3, the summed summary table is 1 data 8, and the counting summary table is 1 data 2. (In this way, the summary table must be consistent with the source formula, but the summary rules for each cell must be defined).

The most commonly used positioning addition is always the accumulation of cell values, also called summing, followed by mean, maximum, minimum, median, count, etc., etc., for character cells, there are also merges, counts, and ignores.

Which do you want?

Method 1: When recording macros, select Record in "Personal Macro Worksheet", and the macros recorded in this way can be used in any excel sheet opened on the local computer. The disadvantage is that every time you open VB in the future, there will be a personal macro module opened, and it can't be deleted, unless you reinstall Excel, even if you open the personal macro module, it's just a little unaccustomed to it, and it doesn't affect the use.

Method 2: On the VB page, select the module with macros on the left and export.

After opening the Excel document that needs to use these macros, go to the VB interface and import the macros.