This Code reads all files in a folder, and can also filter other formats based on the extension.
'With the file name, open the file, get the sheet name of each file, and write it to the desired place.
Sub macro1 ()
Dim mydialog as filedialog, ofile as object, strname as string, N as integer
Dim FSO as object, myfolder as object, myfiles as object, dim FN $
Set mydialog = application. filedialog (msofiledialogfolderpicker)
N = 1
With mydialog
If. Show & lt; & gt;-1 then exit sub
Set FSO = Createobject ("scripting. FileSystemObject") '. This is the folder selection. Click the one where you want to store the file.
Set myfolder = FSO. getfolder (. initialfilename)
Set myfiles = myfolder. Files
For each ofile in myfiles
Strname = ucase (ofile. Name)
Strname = VBA. Right (strname, 3)
If strname = "xls" or strname = "xls" then "this is the extension Selection
'Next, you can write the statement to open the file, read the data, and then write the data, as follows:
Fn = myfolder & "/" & ofile. Name
Workbooks. Open filename: = FN
Worksheets (1). select' assume that you read the data of sheet1
Range _ = range ("A2: F50") 'indicates the region where data is required. Modify the region by yourself.
Windows (external External table data is automatically imported to. xls "). activate' This is the file name of the new table. Modify it by yourself.
Worksheets (N). select' select the number of sheet files to open. If no Add code is available, add
Range ("A2: F5") = range _ 'Write Data
Workbooks (2). Close
N = n + 1
End if
Next
End
End sub