In VBA, how do I open all the Excel files in a folder?

Source: Internet
Author: User

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

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.