Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Open and copy all workbook sheets in a folder to a master file | Excel Discussion (Misc queries) | |||
When I open a file in excel, I cannot open any of the sheets | Excel Worksheet Functions | |||
Search open sheets in workbook and insert into open sheet | Excel Discussion (Misc queries) | |||
Open file and print sheets based on data in cell | Excel Programming | |||
Workbook and sheets have disappeared but file can open | Excel Discussion (Misc queries) |