Folder there are hundreds of excel, each with different number of sheet, how to get all the sheet name, form it is best to excel + sheet, I now use is
To obtain all excel the name of the first (a) very quickly, and then loop all excel respectively carry out the following code
Dim XlApp As New Excel. Application
Dim XLWorkBook As New Excel. The Workbook
The Set XLWorkBook=XlApp. Workbooks. Open (App) Path & amp; "\ 1. XLS")
For I=1 To XLWorkBook. Worksheets. Count
List1. AddItem XLWorkBook. Sheets (I). The Name
Next I
XlApp. Quit
This code is actually still open the excel, first take the sheet name again after closing, problems come out, hundreds of excel to perform two or three minutes, this can't be sure, do you have any faster method, can take to the folder name all all excel sheet,
thank you

CodePudding user response:

If it is me, what I did,

CodePudding user response:

But Excel. Application objects created only once,
Then is kept open and close the file,

CodePudding user response:

Try to use the database efficiency way of reading may be better...

CodePudding user response:

Use the method of external database should be much quicker,

In the first place in a connection object cn will Excel file as an external database open:

Cn. The Open ". The Provider=Microsoft Jet. The OLEDB. 4.0; Data Source="& amp; App. The Path & amp; "\ 1. XLS; Extended Properties=8.0 "" Excel; HDR=Yes; IMEX=1 ", "
"
Then, in the open recordset object Schema record:

Set the rs=cnn1. OpenSchema (adSchemaTables)

The recordset TABLE_NAME fields of each record is a name of the table, however, to use TABLE_TYPE field filtering system tables, to print a detailed look at to know,

CodePudding user response:

XLWorkBook. Close

Don't quit

CodePudding user response:

Thank you for your help

CodePudding user response:

Still can't solve

CodePudding user response:

Give it a try:

 Dim XlApp As New Excel. Application 
Dim XLWorkBook As New Excel. The Workbook 
Dim fn as String 
Dim n As Long 
XlApp. Visible=False 
For n=1 to 100 
Fn=App. Path & amp; "" + CStr (n) + ". XLS "
The Set XLWorkBook=XlApp. Workbooks. Open (fn) 
For each in XLWorkBook. Worksheet in XLWorkBook. Sheets 
The debug. Print fn;" + "; XLWorkBook. Sheet. Name 
Next 
The Set XLWorkBook=Nothing 
XlApp. Close 
The next n 

CodePudding user response:

For each in XLWorkBook. Worksheet in XLWorkBook. Sheets
Should be changed to
For each XLWorkBook. Worksheet in XLWorkBook. Sheets

CodePudding user response:

Upstairs method is also open to close again, certainly not soon

CodePudding user response:

references to the tenth floor usbusb2007 response:

upstairs method is also open to close again, certainly not soon

XlApp. Visible=False 'closed Excel according to accelerate
For each is faster than the for,

Are you sure?

Don't superstitious books, examination questions, teachers, reply;
CPU to superstitions, compiler, debugger, run results,
And please combine "the blind man touched the sun" and "boat out to sea must carry only a compass," try to understand,
Any theory, authority, according to legend, the truth, and the standard, explain, imagination, knowledge... Is not in my eyes!

Some people say that a set of a set of, you believe what he said or he do?
In fact all of the world through the ages are strictly said do a set of a, isn't it?

Don't write even himself can't predict the results of the code!

CodePudding user response:

references to the tenth floor usbusb2007 response:

upstairs method is also open to close again, certainly not soon

The Debug. Print than List1. AddItem fast

CodePudding user response:

The key is I want to print it out, the results not only do I have to use this results in the code below, only the results first on the list

CodePudding user response:

Code is not slow in the for or for each, but slow on opening and closing of excel, as long as there is the action of opening and closing, all quick

CodePudding user response:

On the 14th floor usbusb2007

reference response:

code is not slow in the for or for each, but slow on the opening and closing of excel, as long as there is the action of opening and closing, can fast

Quick off work, had no time to say more.
The last time I had a scan to remove EXCEL macro viruses, http://download.csdn.net/detail/wallescai/4349826
You open and have a look, should be opened and closed to each object excelapp best after a certain number of excel file to delete the object to a again.
Should be involved in the temporary cache the file and excel itself, and some things. I didn't get, just like with.

When the results of the test is when the app object continuous operation after dozens of excel file (even close excel file every time), response will be slower and slower, until slowly.
So the countermeasures is to open a few times to establish object.
Microsoft is not such a good, the computer is slow to restart bai, ha ha

CodePudding user response:

Customer request such implementation, everybody to help

CodePudding user response:

 Option Explicit 

Private Sub Command1_Click () 
Dim cn As ADODB library. The Connection, the rs As ADODB library. You, strFile As String 

Set the cn=New ADODB. Connection 

StrFile=Dir (" c: \ test \ *. XLS ") 
The Do While strFile & gt; "
"
The Debug. Print strFile & amp; ":" 
Cn. The Open ". The Provider=Microsoft Jet. The OLEDB. 4.0; The Data Source=c: \ test \ "& amp; StrFile & amp; "; Extended Properties=8.0 "" Excel; HDR=Yes; IMEX=1 ", "
"
Set the rs=cn. OpenSchema (adSchemaTables) 

EOF 
 Do Until the rs.If the rs! Table_Type="TABLE" Then Debug. Print vbTab & amp; Rs! Table_Name 
Rs. MoveNext 
Loop 

Cn. Close 
StrFile=Dir () 
Loop 
Set the rs=Nothing 
The Set cn=Nothing 
End Sub 

The

 new Microsoft Excel worksheet. XLS: 
Sheet1 $
Sheet2 $
Sheet3 $
Test. XLS: 
Worksheet 1 $
Work table 2 $
Work table 3 $

CodePudding user response:

reference 13 floor usbusb2007 reply:

the key is I want to print it out, the results not only do I have to use this results in the code below, only the results on the first list

You don't put in an array of strings?

 Const MAXN As Long=1000 
Dim strarray (0 TO MAXN) as String 

CodePudding user response:

nullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnull