Get sheets name without file open
 

Hello,

Could I can get information about sheets name in files at one folder without
open all this files.
I need list:
file1.xls sheets1
file1.xls sheets2
file1.xls sheets3
file2.xls sheets1
file2.xls sheets2
....
with open files it is no problem, but it took lot of time (about 50 files).
Could I make it faster?

Regards,
Bartosz

Get sheets name without file open
 

See if this runs quicker

Sub test()

Folder = "c:\temp\"
FName = Dir(Folder & "*.xls")
RowCount = 1
Do While FName < ""
Set obj = GetObject(Folder & FName)
For Each Sht In Sheets
Range("A" & RowCount) = FName
Range("B" & RowCount) = Sht.Name
RowCount = RowCount + 1
Next Sht
Set obj = Nothing
FName = Dir()
Loop


End Sub

"Bartosz" wrote:

Hello,

Could I can get information about sheets name in files at one folder without
open all this files.
I need list:
file1.xls sheets1
file1.xls sheets2
file1.xls sheets3
file2.xls sheets1
file2.xls sheets2
...
with open files it is no problem, but it took lot of time (about 50 files).
Could I make it faster?

Regards,
Bartosz

Get sheets name without file open
 

I was looking a the wrong workbooks. Make this change

from
For Each Sht In Sheets
to
For Each Sht In obj.Sheets

"Joel" wrote:

See if this runs quicker

Sub test()

Folder = "c:\temp\"
FName = Dir(Folder & "*.xls")
RowCount = 1
Do While FName < ""
Set obj = GetObject(Folder & FName)
For Each Sht In Sheets
Range("A" & RowCount) = FName
Range("B" & RowCount) = Sht.Name
RowCount = RowCount + 1
Next Sht
Set obj = Nothing
FName = Dir()
Loop


End Sub

"Bartosz" wrote:

Hello,

Could I can get information about sheets name in files at one folder without
open all this files.
I need list:
file1.xls sheets1
file1.xls sheets2
file1.xls sheets3
file2.xls sheets1
file2.xls sheets2
...
with open files it is no problem, but it took lot of time (about 50 files).
Could I make it faster?

Regards,
Bartosz

Get sheets name without file open
 

I was looking a the wrong workbooks. Make this change

from
For Each Sht In Sheets
to
For Each Sht In obj.Sheets

"Joel" wrote:

See if this runs quicker

Sub test()

Folder = "c:\temp\"
FName = Dir(Folder & "*.xls")
RowCount = 1
Do While FName < ""
Set obj = GetObject(Folder & FName)
For Each Sht In Sheets
Range("A" & RowCount) = FName
Range("B" & RowCount) = Sht.Name
RowCount = RowCount + 1
Next Sht
Set obj = Nothing
FName = Dir()
Loop


End Sub

"Bartosz" wrote:

Hello,

Could I can get information about sheets name in files at one folder without
open all this files.
I need list:
file1.xls sheets1
file1.xls sheets2
file1.xls sheets3
file2.xls sheets1
file2.xls sheets2
...
with open files it is no problem, but it took lot of time (about 50 files).
Could I make it faster?

Regards,
Bartosz

Get sheets name without file open
 

Thanks,
but it still open files (with hiden option) so it takes many time to make it
with many files. And on end of loop this file must be closed (I can writhe
this myself).
But becouse it probably only this way to get this data I use it in my macro.
Thanks again,
Bartosz

"Joel" wrote:

I was looking a the wrong workbooks. Make this change

from
For Each Sht In Sheets
to
For Each Sht In obj.Sheets

"Joel" wrote:

See if this runs quicker

Sub test()

Folder = "c:\temp\"
FName = Dir(Folder & "*.xls")
RowCount = 1
Do While FName < ""
Set obj = GetObject(Folder & FName)
For Each Sht In Sheets
Range("A" & RowCount) = FName
Range("B" & RowCount) = Sht.Name
RowCount = RowCount + 1
Next Sht
Set obj = Nothing
FName = Dir()
Loop


End Sub

Get sheets name without file open
 

I'm not sure if it really opens the workbook. The code launches an invisible
excel application. By not makeing the workbook visible saves time because
the sceen doesn't need to be updated. I could of used the ADO method to
access the workbook but that would of required some SQL which i think would
of been slower than the methods I proposed.

What also may be quicker is to launch an excell application using
CreateObject and then open the workbook using the application. Make the
CreateObject invisible. It takes time with Getobject to create the
application.

"Bartosz" wrote:

Thanks,
but it still open files (with hiden option) so it takes many time to make it
with many files. And on end of loop this file must be closed (I can writhe
this myself).
But becouse it probably only this way to get this data I use it in my macro.
Thanks again,
Bartosz

"Joel" wrote:

I was looking a the wrong workbooks. Make this change

from
For Each Sht In Sheets
to
For Each Sht In obj.Sheets

"Joel" wrote:

See if this runs quicker

Sub test()

Folder = "c:\temp\"
FName = Dir(Folder & "*.xls")
RowCount = 1
Do While FName < ""
Set obj = GetObject(Folder & FName)
For Each Sht In Sheets
Range("A" & RowCount) = FName
Range("B" & RowCount) = Sht.Name
RowCount = RowCount + 1
Next Sht
Set obj = Nothing
FName = Dir()
Loop


End Sub

Get sheets name without file open
 

I do not know how this code lunches aplication/file, but I get massage with
question about update links during line:
Set obj = GetObject(Folder & FName)
is made,
and in Excel window when I go to [window] [unhide] (I translated menu
names from Polish, so I am not sure about those names in English) I see those
files on list with file hided.

Regards,
Bartosz

"Joel" wrote:

I'm not sure if it really opens the workbook. The code launches an invisible
excel application. By not makeing the workbook visible saves time because
the sceen doesn't need to be updated. I could of used the ADO method to
access the workbook but that would of required some SQL which i think would
of been slower than the methods I proposed.

What also may be quicker is to launch an excell application using
CreateObject and then open the workbook using the application. Make the
CreateObject invisible. It takes time with Getobject to create the
application.

"Bartosz" wrote:

Thanks,
but it still open files (with hiden option) so it takes many time to make it
with many files. And on end of loop this file must be closed (I can writhe
this myself).
But becouse it probably only this way to get this data I use it in my macro.
Thanks again,
Bartosz

"Joel" wrote:

I was looking a the wrong workbooks. Make this change

from
For Each Sht In Sheets
to
For Each Sht In obj.Sheets

"Joel" wrote:

See if this runs quicker

Sub test()

Folder = "c:\temp\"
FName = Dir(Folder & "*.xls")
RowCount = 1
Do While FName < ""
Set obj = GetObject(Folder & FName)
For Each Sht In Sheets
Range("A" & RowCount) = FName
Range("B" & RowCount) = Sht.Name
RowCount = RowCount + 1
Next Sht
Set obj = Nothing
FName = Dir()
Loop


End Sub

Get sheets name without file open
 

Disable alerts.

FName = "c:\temp\book2.xls"
Application.DisplayAlerts = False
Set obj = GetObject(FName)
Application.DisplayAlerts = True


"Bartosz" wrote:

I do not know how this code lunches aplication/file, but I get massage with
question about update links during line:
Set obj = GetObject(Folder & FName)
is made,
and in Excel window when I go to [window] [unhide] (I translated menu
names from Polish, so I am not sure about those names in English) I see those
files on list with file hided.

Regards,
Bartosz

"Joel" wrote:

I'm not sure if it really opens the workbook. The code launches an invisible
excel application. By not makeing the workbook visible saves time because
the sceen doesn't need to be updated. I could of used the ADO method to
access the workbook but that would of required some SQL which i think would
of been slower than the methods I proposed.

What also may be quicker is to launch an excell application using
CreateObject and then open the workbook using the application. Make the
CreateObject invisible. It takes time with Getobject to create the
application.

"Bartosz" wrote:

Thanks,
but it still open files (with hiden option) so it takes many time to make it
with many files. And on end of loop this file must be closed (I can writhe
this myself).
But becouse it probably only this way to get this data I use it in my macro.
Thanks again,
Bartosz

"Joel" wrote:

I was looking a the wrong workbooks. Make this change

from
For Each Sht In Sheets
to
For Each Sht In obj.Sheets

"Joel" wrote:

See if this runs quicker

Sub test()

Folder = "c:\temp\"
FName = Dir(Folder & "*.xls")
RowCount = 1
Do While FName < ""
Set obj = GetObject(Folder & FName)
For Each Sht In Sheets
Range("A" & RowCount) = FName
Range("B" & RowCount) = Sht.Name
RowCount = RowCount + 1
Next Sht
Set obj = Nothing
FName = Dir()
Loop


End Sub

Get sheets name without file open
 

GetObject opens the workbook. It will open an instance of Excel only if
Excel is not running. The workbook is never visible, so the user does not
directly see the workbook, but it does take measurable time for each
workbook.

This technique may be faster, because ADO doesn't have to "open" the
workbook to read its structu

http://www.mrexcel.com/forum/showpos...71&postcount=4

- Jon
-------
Advanced Excel Conference - June 17-18 2009 - Charting and Programming
http://peltiertech.com/Training/2009...00906ACNJ.html

Jon Peltier, Peltier Technical Services, Inc.
http://PeltierTech.com/WordPress/
_______


"Joel" wrote in message
...
I'm not sure if it really opens the workbook. The code launches an
invisible
excel application. By not makeing the workbook visible saves time because
the sceen doesn't need to be updated. I could of used the ADO method to
access the workbook but that would of required some SQL which i think
would
of been slower than the methods I proposed.

What also may be quicker is to launch an excell application using
CreateObject and then open the workbook using the application. Make the
CreateObject invisible. It takes time with Getobject to create the
application.

"Bartosz" wrote:

Thanks,
but it still open files (with hiden option) so it takes many time to make
it
with many files. And on end of loop this file must be closed (I can
writhe
this myself).
But becouse it probably only this way to get this data I use it in my
macro.
Thanks again,
Bartosz

"Joel" wrote:

I was looking a the wrong workbooks. Make this change

from
For Each Sht In Sheets
to
For Each Sht In obj.Sheets

"Joel" wrote:

See if this runs quicker

Sub test()

Folder = "c:\temp\"
FName = Dir(Folder & "*.xls")
RowCount = 1
Do While FName < ""
Set obj = GetObject(Folder & FName)
For Each Sht In Sheets
Range("A" & RowCount) = FName
Range("B" & RowCount) = Sht.Name
RowCount = RowCount + 1
Next Sht
Set obj = Nothing
FName = Dir()
Loop


End Sub