EXCEL20 16 test, I hope I can help you. It is to obtain data in batches according to the workbook name in the first row, and automatically fill in the name of the corresponding worksheet in the second row (each workbook contains only one table). Please pay attention to data backup before running the code! !

Subtest ()

Dim cnn as an object

Dim strPath, strTable, strSQL, z as strings, ss$, shtName as strings.

Fuzzy x%, i%

Set cnn = CreateObject("ADODB。 Connect ")

X = [xfd 1]。 end (xltolft)。 "column" obtains the column number corresponding to the rightmost cell in the first row, and calculates the number of workbooks to be obtained.

Line ("2:25"). Clear the old data in the data store.

For i = 1 to x

z = Dir(" D:\ DATA \ DATA " & amp； "\*.xlsx”)

Establish a connection and extract data.

Ss = cell (1, i). Value and value. ".xlsx "

Do While Z & lt& gt""

If Z = ss, then

strPath = " D:\ DATA \ DATA " & amp； " \ " & ampZ

cnn。 Open "provider=Microsoft。 ACE . oledb . 12.0； Extended attribute =' Excel12.0; Hdr = no; imex = 1 '； Data source = "& ampstrPath

Set rst = cnn. Open architecture (20)

Shtname = rst.fields ("TABLE _ name ")。 Gets the name of the table type record table.

strTable = "[" & amp； shtName & amp" A 1:A 18]"

strSQL = " select f 1 from " & amp； Get the data in the first column of the table.

Cell (3, I). Cnn's CopyFromRecordset Execute (strSQL)

Cells (2, I) = left (shtname, len (shtname)- 1)' Delete the $ sign in the table name.

cnn。 close

If ... it will be over.

Z = direction

ring

Next, I

Set cnn = None.

End joint