Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Code looping through files
No disabled items found.
Opened them manually as late as this evening. I've opened them without issue at least 15 times today. Bob "Tom Ogilvy" wrote in message ... That eliminates using the DIR command as a possible source of the problem. Since the problem is in xl2003, then perhaps: In excel I would go to Tools=About Microsoft Excel, then look in Disabled Items button at the bottom. If any of your files are listed there, then I would remove them from being disabled (select the appropriate files one at a time in the list and hit the Enable button for each). You said you had opened them manually, but that may have been sometime in the past. -- Regards, Tom Ogilvy "rjr" wrote in message ... Tom Thank you, I ran it and it ran to the first msgbox and then displayed the first file name. WHen I selected ok it errored out again with the 1004 error and the debug highlighted this area of code Set wb = Workbooks.Open(list(i)) Any help?? BOb "Tom Ogilvy" wrote in message ... Try it like this Sub AllFolderFiles() Dim wb As Workbook Dim TheFile As String Dim MyPath As String Dim list() As String Dim i As Long ReDim list(1 To 1) MyPath = "C:\Temp" ChDrive MyPath ChDir MyPath TheFile = Dir("*.xls") Do While TheFile < "" list(UBound(list)) = MyPath & "\" & TheFile ReDim Preserve list(1 To UBound(list) + 1) TheFile = Dir Loop ReDim Preserve list(1 To UBound(list) - 1) For i = 1 To UBound(list) Set wb = Workbooks.Open(list(i)) MsgBox wb.FullName wb.Close Next i End Sub -- Regards, Tom Ogilvy "rjr" wrote in message .. . Gary my apologies, I used your code as a standalone and the first loop worked fine and opened the first file, but when it did the loop errored out as did Otto's, on the second round. This is the same thing that happened with Otto's code and does it consistentantly with my desktop and my laptop. I'm sorry for not getting back with you on this. Can you think of a reason your 2003 would work and mine wouldn't? Bob Reynolds "Gary Keramidas" <GKeramidasATmsn.com wrote in message ... this works for me Sub AllFolderFiles() Dim wb As Workbook Dim TheFile As String Dim MyPath As String MyPath = "C:\Temp" TheFile = Dir(MyPath & "\" & "*.xls") Do While TheFile < "" Set wb = Workbooks.Open(MyPath & "\" & TheFile) MsgBox wb.FullName wb.Close TheFile = Dir() Loop End Sub -- Gary "rjr" wrote in message .. . Hello again to all, I wish it were that simple. Nope I'm windows XP Media Center Edition with all current updates and a valid updated Office Professional product. As a side note I've also placed it on my Dell D520 laptop with Windoes XP Professional and Office 2003 with the exact same results. Since it works for everyone else it obviously has to be something with my computers, but WHAT? My dell laptop is relatively clean without a lot of additional programs and the results are the same. That's why I think the problem lies with my machine's setup... Bob R "Chip Pearson" wrote in message ... Otto, This is out of the blue, but perhaps the user having the problem is using a Macintosh? If this proves to be the case, the code will fail on the line indicated because Macs don't use the '\' character as a path separator. Instead, they use, I think, a ':' character. Instead of hard coding the '\', use Application.PathSeparator. E.g., Set wb = Workbooks.Open(MyPath & Application.PathSeparator & TheFile) The code you posted works fine for me in Excel 2007 for Windows. -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting, LLC www.cpearson.com (email address is on the web site) "Otto Moehrbach" wrote in message ... Tom Thanks for your help as well as Dave and Chip. Here's what the OP and I just did to establish a hard starting point. He set all the code I had written for him aside. Instead, he used the macro from: http://www.contextures.com/xlfaqMac.html#LoopBooks with NO CHANGES of any kind. No other macro or macro call was used. He established a path in his computer to match that used in the macro and placed his test files in that folder. He ran the macro. The error message is: Run-time error '1004' Method 'Open' of object 'Workbook' failed. The highlighted code line is the "Set wb = ............" The full macro is: Sub AllFolderFiles() Dim wb As Workbook Dim TheFile As String Dim MyPath As String MyPath = "C:\Temp" ChDir MyPath TheFile = Dir("*.xls") Do While TheFile < "" Set wb = Workbooks.Open(MyPath & "\" & TheFile) MsgBox wb.FullName wb.Close TheFile = Dir Loop End SubWe will try any suggestion you and the others can come up with. Thanks for your time. Otto"Tom Ogilvy" wrote in message ... Since you don't show the code, have him send you the file that fails to open and test the code with that file. Perhaps the file is damaged or not a file Excel will open. -- Regards, Tom Ogilvy "Otto Moehrbach" wrote in message ... Excel XP & Win XP I'm helping an OP with a project. The code loops through all the files in a folder, opens each, does things, closes the file and opens the next file, etc. The code is placed in the Personal.xls file. It works fine for me. It fails on opening the first file for him (will not open the file). An error is produced saying that opening the file failed. He has Excel 2003. He sent me his Personal.xls file. I changed the name and put it in my XLSTART folder. It works fine with my path. I created his path on my computer and that works fine too. I checked the VBE - Tools - References. The only thing I have checked that he doesn't is "Microsoft Forms 2.0 Object Library". He doesn't have anything like that to check. Does anyone have any ideas of what I could do? Thanks for your time. Otto |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Code looping through files
does it work if you only put 2 files in the folder? does it work if you create 2
new dummy files and put them in the folder? another folder with a different name? -- Gary "rjr" wrote in message . .. No disabled items found. Opened them manually as late as this evening. I've opened them without issue at least 15 times today. Bob "Tom Ogilvy" wrote in message ... That eliminates using the DIR command as a possible source of the problem. Since the problem is in xl2003, then perhaps: In excel I would go to Tools=About Microsoft Excel, then look in Disabled Items button at the bottom. If any of your files are listed there, then I would remove them from being disabled (select the appropriate files one at a time in the list and hit the Enable button for each). You said you had opened them manually, but that may have been sometime in the past. -- Regards, Tom Ogilvy "rjr" wrote in message ... Tom Thank you, I ran it and it ran to the first msgbox and then displayed the first file name. WHen I selected ok it errored out again with the 1004 error and the debug highlighted this area of code Set wb = Workbooks.Open(list(i)) Any help?? BOb "Tom Ogilvy" wrote in message ... Try it like this Sub AllFolderFiles() Dim wb As Workbook Dim TheFile As String Dim MyPath As String Dim list() As String Dim i As Long ReDim list(1 To 1) MyPath = "C:\Temp" ChDrive MyPath ChDir MyPath TheFile = Dir("*.xls") Do While TheFile < "" list(UBound(list)) = MyPath & "\" & TheFile ReDim Preserve list(1 To UBound(list) + 1) TheFile = Dir Loop ReDim Preserve list(1 To UBound(list) - 1) For i = 1 To UBound(list) Set wb = Workbooks.Open(list(i)) MsgBox wb.FullName wb.Close Next i End Sub -- Regards, Tom Ogilvy "rjr" wrote in message .. . Gary my apologies, I used your code as a standalone and the first loop worked fine and opened the first file, but when it did the loop errored out as did Otto's, on the second round. This is the same thing that happened with Otto's code and does it consistentantly with my desktop and my laptop. I'm sorry for not getting back with you on this. Can you think of a reason your 2003 would work and mine wouldn't? Bob Reynolds "Gary Keramidas" <GKeramidasATmsn.com wrote in message ... this works for me Sub AllFolderFiles() Dim wb As Workbook Dim TheFile As String Dim MyPath As String MyPath = "C:\Temp" TheFile = Dir(MyPath & "\" & "*.xls") Do While TheFile < "" Set wb = Workbooks.Open(MyPath & "\" & TheFile) MsgBox wb.FullName wb.Close TheFile = Dir() Loop End Sub -- Gary "rjr" wrote in message .. . Hello again to all, I wish it were that simple. Nope I'm windows XP Media Center Edition with all current updates and a valid updated Office Professional product. As a side note I've also placed it on my Dell D520 laptop with Windoes XP Professional and Office 2003 with the exact same results. Since it works for everyone else it obviously has to be something with my computers, but WHAT? My dell laptop is relatively clean without a lot of additional programs and the results are the same. That's why I think the problem lies with my machine's setup... Bob R "Chip Pearson" wrote in message ... Otto, This is out of the blue, but perhaps the user having the problem is using a Macintosh? If this proves to be the case, the code will fail on the line indicated because Macs don't use the '\' character as a path separator. Instead, they use, I think, a ':' character. Instead of hard coding the '\', use Application.PathSeparator. E.g., Set wb = Workbooks.Open(MyPath & Application.PathSeparator & TheFile) The code you posted works fine for me in Excel 2007 for Windows. -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting, LLC www.cpearson.com (email address is on the web site) "Otto Moehrbach" wrote in message ... Tom Thanks for your help as well as Dave and Chip. Here's what the OP and I just did to establish a hard starting point. He set all the code I had written for him aside. Instead, he used the macro from: http://www.contextures.com/xlfaqMac.html#LoopBooks with NO CHANGES of any kind. No other macro or macro call was used. He established a path in his computer to match that used in the macro and placed his test files in that folder. He ran the macro. The error message is: Run-time error '1004' Method 'Open' of object 'Workbook' failed. The highlighted code line is the "Set wb = ............" The full macro is: Sub AllFolderFiles() Dim wb As Workbook Dim TheFile As String Dim MyPath As String MyPath = "C:\Temp" ChDir MyPath TheFile = Dir("*.xls") Do While TheFile < "" Set wb = Workbooks.Open(MyPath & "\" & TheFile) MsgBox wb.FullName wb.Close TheFile = Dir Loop End SubWe will try any suggestion you and the others can come up with. Thanks for your time. Otto"Tom Ogilvy" wrote in message ... Since you don't show the code, have him send you the file that fails to open and test the code with that file. Perhaps the file is damaged or not a file Excel will open. -- Regards, Tom Ogilvy "Otto Moehrbach" wrote in message ... Excel XP & Win XP I'm helping an OP with a project. The code loops through all the files in a folder, opens each, does things, closes the file and opens the next file, etc. The code is placed in the Personal.xls file. It works fine for me. It fails on opening the first file for him (will not open the file). An error is produced saying that opening the file failed. He has Excel 2003. He sent me his Personal.xls file. I changed the name and put it in my XLSTART folder. It works fine with my path. I created his path on my computer and that works fine too. I checked the VBE - Tools - References. The only thing I have checked that he doesn't is "Microsoft Forms 2.0 Object Library". He doesn't have anything like that to check. Does anyone have any ideas of what I could do? Thanks for your time. Otto |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Code looping through files
New files, new data and same result.. Thanks
I really need this to work since I'm using 2003 and so's my wife. Bob "Gary Keramidas" <GKeramidasATmsn.com wrote in message ... does it work if you only put 2 files in the folder? does it work if you create 2 new dummy files and put them in the folder? another folder with a different name? -- Gary "rjr" wrote in message . .. No disabled items found. Opened them manually as late as this evening. I've opened them without issue at least 15 times today. Bob "Tom Ogilvy" wrote in message ... That eliminates using the DIR command as a possible source of the problem. Since the problem is in xl2003, then perhaps: In excel I would go to Tools=About Microsoft Excel, then look in Disabled Items button at the bottom. If any of your files are listed there, then I would remove them from being disabled (select the appropriate files one at a time in the list and hit the Enable button for each). You said you had opened them manually, but that may have been sometime in the past. -- Regards, Tom Ogilvy "rjr" wrote in message ... Tom Thank you, I ran it and it ran to the first msgbox and then displayed the first file name. WHen I selected ok it errored out again with the 1004 error and the debug highlighted this area of code Set wb = Workbooks.Open(list(i)) Any help?? BOb "Tom Ogilvy" wrote in message ... Try it like this Sub AllFolderFiles() Dim wb As Workbook Dim TheFile As String Dim MyPath As String Dim list() As String Dim i As Long ReDim list(1 To 1) MyPath = "C:\Temp" ChDrive MyPath ChDir MyPath TheFile = Dir("*.xls") Do While TheFile < "" list(UBound(list)) = MyPath & "\" & TheFile ReDim Preserve list(1 To UBound(list) + 1) TheFile = Dir Loop ReDim Preserve list(1 To UBound(list) - 1) For i = 1 To UBound(list) Set wb = Workbooks.Open(list(i)) MsgBox wb.FullName wb.Close Next i End Sub -- Regards, Tom Ogilvy "rjr" wrote in message .. . Gary my apologies, I used your code as a standalone and the first loop worked fine and opened the first file, but when it did the loop errored out as did Otto's, on the second round. This is the same thing that happened with Otto's code and does it consistentantly with my desktop and my laptop. I'm sorry for not getting back with you on this. Can you think of a reason your 2003 would work and mine wouldn't? Bob Reynolds "Gary Keramidas" <GKeramidasATmsn.com wrote in message ... this works for me Sub AllFolderFiles() Dim wb As Workbook Dim TheFile As String Dim MyPath As String MyPath = "C:\Temp" TheFile = Dir(MyPath & "\" & "*.xls") Do While TheFile < "" Set wb = Workbooks.Open(MyPath & "\" & TheFile) MsgBox wb.FullName wb.Close TheFile = Dir() Loop End Sub -- Gary "rjr" wrote in message .. . Hello again to all, I wish it were that simple. Nope I'm windows XP Media Center Edition with all current updates and a valid updated Office Professional product. As a side note I've also placed it on my Dell D520 laptop with Windoes XP Professional and Office 2003 with the exact same results. Since it works for everyone else it obviously has to be something with my computers, but WHAT? My dell laptop is relatively clean without a lot of additional programs and the results are the same. That's why I think the problem lies with my machine's setup... Bob R "Chip Pearson" wrote in message ... Otto, This is out of the blue, but perhaps the user having the problem is using a Macintosh? If this proves to be the case, the code will fail on the line indicated because Macs don't use the '\' character as a path separator. Instead, they use, I think, a ':' character. Instead of hard coding the '\', use Application.PathSeparator. E.g., Set wb = Workbooks.Open(MyPath & Application.PathSeparator & TheFile) The code you posted works fine for me in Excel 2007 for Windows. -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting, LLC www.cpearson.com (email address is on the web site) "Otto Moehrbach" wrote in message ... Tom Thanks for your help as well as Dave and Chip. Here's what the OP and I just did to establish a hard starting point. He set all the code I had written for him aside. Instead, he used the macro from: http://www.contextures.com/xlfaqMac.html#LoopBooks with NO CHANGES of any kind. No other macro or macro call was used. He established a path in his computer to match that used in the macro and placed his test files in that folder. He ran the macro. The error message is: Run-time error '1004' Method 'Open' of object 'Workbook' failed. The highlighted code line is the "Set wb = ............" The full macro is: Sub AllFolderFiles() Dim wb As Workbook Dim TheFile As String Dim MyPath As String MyPath = "C:\Temp" ChDir MyPath TheFile = Dir("*.xls") Do While TheFile < "" Set wb = Workbooks.Open(MyPath & "\" & TheFile) MsgBox wb.FullName wb.Close TheFile = Dir Loop End SubWe will try any suggestion you and the others can come up with. Thanks for your time. Otto"Tom Ogilvy" wrote in message ... Since you don't show the code, have him send you the file that fails to open and test the code with that file. Perhaps the file is damaged or not a file Excel will open. -- Regards, Tom Ogilvy "Otto Moehrbach" wrote in message ... Excel XP & Win XP I'm helping an OP with a project. The code loops through all the files in a folder, opens each, does things, closes the file and opens the next file, etc. The code is placed in the Personal.xls file. It works fine for me. It fails on opening the first file for him (will not open the file). An error is produced saying that opening the file failed. He has Excel 2003. He sent me his Personal.xls file. I changed the name and put it in my XLSTART folder. It works fine with my path. I created his path on my computer and that works fine too. I checked the VBE - Tools - References. The only thing I have checked that he doesn't is "Microsoft Forms 2.0 Object Library". He doesn't have anything like that to check. Does anyone have any ideas of what I could do? Thanks for your time. Otto |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Code looping through files
That is the issue.
It works for everyone else. Version of Excel shouldn't make any difference and doesn't for me. Anyway, you can try this one which uses another approach: Sub AllFiles() Dim sFol As String Dim fso As Object, fl As Object Dim fld As Object Dim wb As Workbook sFol = "C:\temp" Set fso = CreateObject("Scripting.FileSystemObject") Set fld = fso.GetFolder(sFol) For Each fl In fld.Files If fl.Name Like "*.xls" Then Set wb = Workbooks.Open(fl.Path) MsgBox wb.FullName wb.Close End If Next End Sub -- Regards, Tom Ogilvy "rjr" wrote in message . .. New files, new data and same result.. Thanks I really need this to work since I'm using 2003 and so's my wife. Bob "Gary Keramidas" <GKeramidasATmsn.com wrote in message ... does it work if you only put 2 files in the folder? does it work if you create 2 new dummy files and put them in the folder? another folder with a different name? -- Gary "rjr" wrote in message . .. No disabled items found. Opened them manually as late as this evening. I've opened them without issue at least 15 times today. Bob "Tom Ogilvy" wrote in message ... That eliminates using the DIR command as a possible source of the problem. Since the problem is in xl2003, then perhaps: In excel I would go to Tools=About Microsoft Excel, then look in Disabled Items button at the bottom. If any of your files are listed there, then I would remove them from being disabled (select the appropriate files one at a time in the list and hit the Enable button for each). You said you had opened them manually, but that may have been sometime in the past. -- Regards, Tom Ogilvy "rjr" wrote in message ... Tom Thank you, I ran it and it ran to the first msgbox and then displayed the first file name. WHen I selected ok it errored out again with the 1004 error and the debug highlighted this area of code Set wb = Workbooks.Open(list(i)) Any help?? BOb "Tom Ogilvy" wrote in message ... Try it like this Sub AllFolderFiles() Dim wb As Workbook Dim TheFile As String Dim MyPath As String Dim list() As String Dim i As Long ReDim list(1 To 1) MyPath = "C:\Temp" ChDrive MyPath ChDir MyPath TheFile = Dir("*.xls") Do While TheFile < "" list(UBound(list)) = MyPath & "\" & TheFile ReDim Preserve list(1 To UBound(list) + 1) TheFile = Dir Loop ReDim Preserve list(1 To UBound(list) - 1) For i = 1 To UBound(list) Set wb = Workbooks.Open(list(i)) MsgBox wb.FullName wb.Close Next i End Sub -- Regards, Tom Ogilvy "rjr" wrote in message .. . Gary my apologies, I used your code as a standalone and the first loop worked fine and opened the first file, but when it did the loop errored out as did Otto's, on the second round. This is the same thing that happened with Otto's code and does it consistentantly with my desktop and my laptop. I'm sorry for not getting back with you on this. Can you think of a reason your 2003 would work and mine wouldn't? Bob Reynolds "Gary Keramidas" <GKeramidasATmsn.com wrote in message ... this works for me Sub AllFolderFiles() Dim wb As Workbook Dim TheFile As String Dim MyPath As String MyPath = "C:\Temp" TheFile = Dir(MyPath & "\" & "*.xls") Do While TheFile < "" Set wb = Workbooks.Open(MyPath & "\" & TheFile) MsgBox wb.FullName wb.Close TheFile = Dir() Loop End Sub -- Gary "rjr" wrote in message .. . Hello again to all, I wish it were that simple. Nope I'm windows XP Media Center Edition with all current updates and a valid updated Office Professional product. As a side note I've also placed it on my Dell D520 laptop with Windoes XP Professional and Office 2003 with the exact same results. Since it works for everyone else it obviously has to be something with my computers, but WHAT? My dell laptop is relatively clean without a lot of additional programs and the results are the same. That's why I think the problem lies with my machine's setup... Bob R "Chip Pearson" wrote in message ... Otto, This is out of the blue, but perhaps the user having the problem is using a Macintosh? If this proves to be the case, the code will fail on the line indicated because Macs don't use the '\' character as a path separator. Instead, they use, I think, a ':' character. Instead of hard coding the '\', use Application.PathSeparator. E.g., Set wb = Workbooks.Open(MyPath & Application.PathSeparator & TheFile) The code you posted works fine for me in Excel 2007 for Windows. -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting, LLC www.cpearson.com (email address is on the web site) "Otto Moehrbach" wrote in message ... Tom Thanks for your help as well as Dave and Chip. Here's what the OP and I just did to establish a hard starting point. He set all the code I had written for him aside. Instead, he used the macro from: http://www.contextures.com/xlfaqMac.html#LoopBooks with NO CHANGES of any kind. No other macro or macro call was used. He established a path in his computer to match that used in the macro and placed his test files in that folder. He ran the macro. The error message is: Run-time error '1004' Method 'Open' of object 'Workbook' failed. The highlighted code line is the "Set wb = ............" The full macro is: Sub AllFolderFiles() Dim wb As Workbook Dim TheFile As String Dim MyPath As String MyPath = "C:\Temp" ChDir MyPath TheFile = Dir("*.xls") Do While TheFile < "" Set wb = Workbooks.Open(MyPath & "\" & TheFile) MsgBox wb.FullName wb.Close TheFile = Dir Loop End SubWe will try any suggestion you and the others can come up with. Thanks for your time. Otto"Tom Ogilvy" wrote in message ... Since you don't show the code, have him send you the file that fails to open and test the code with that file. Perhaps the file is damaged or not a file Excel will open. -- Regards, Tom Ogilvy "Otto Moehrbach" wrote in message ... Excel XP & Win XP I'm helping an OP with a project. The code loops through all the files in a folder, opens each, does things, closes the file and opens the next file, etc. The code is placed in the Personal.xls file. It works fine for me. It fails on opening the first file for him (will not open the file). An error is produced saying that opening the file failed. He has Excel 2003. He sent me his Personal.xls file. I changed the name and put it in my XLSTART folder. It works fine with my path. I created his path on my computer and that works fine too. I checked the VBE - Tools - References. The only thing I have checked that he doesn't is "Microsoft Forms 2.0 Object Library". He doesn't have anything like that to check. Does anyone have any ideas of what I could do? Thanks for your time. Otto |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Code looping through files
Same error, first file opens ok and then once the loop starts on the second
one the error with the offending line at Set wb = Workbooks.Open(fl.Path) Bob "Tom Ogilvy" wrote in message ... That is the issue. It works for everyone else. Version of Excel shouldn't make any difference and doesn't for me. Anyway, you can try this one which uses another approach: Sub AllFiles() Dim sFol As String Dim fso As Object, fl As Object Dim fld As Object Dim wb As Workbook sFol = "C:\temp" Set fso = CreateObject("Scripting.FileSystemObject") Set fld = fso.GetFolder(sFol) For Each fl In fld.Files If fl.Name Like "*.xls" Then Set wb = Workbooks.Open(fl.Path) MsgBox wb.FullName wb.Close End If Next End Sub -- Regards, Tom Ogilvy "rjr" wrote in message . .. New files, new data and same result.. Thanks I really need this to work since I'm using 2003 and so's my wife. Bob "Gary Keramidas" <GKeramidasATmsn.com wrote in message ... does it work if you only put 2 files in the folder? does it work if you create 2 new dummy files and put them in the folder? another folder with a different name? -- Gary "rjr" wrote in message . .. No disabled items found. Opened them manually as late as this evening. I've opened them without issue at least 15 times today. Bob "Tom Ogilvy" wrote in message ... That eliminates using the DIR command as a possible source of the problem. Since the problem is in xl2003, then perhaps: In excel I would go to Tools=About Microsoft Excel, then look in Disabled Items button at the bottom. If any of your files are listed there, then I would remove them from being disabled (select the appropriate files one at a time in the list and hit the Enable button for each). You said you had opened them manually, but that may have been sometime in the past. -- Regards, Tom Ogilvy "rjr" wrote in message ... Tom Thank you, I ran it and it ran to the first msgbox and then displayed the first file name. WHen I selected ok it errored out again with the 1004 error and the debug highlighted this area of code Set wb = Workbooks.Open(list(i)) Any help?? BOb "Tom Ogilvy" wrote in message ... Try it like this Sub AllFolderFiles() Dim wb As Workbook Dim TheFile As String Dim MyPath As String Dim list() As String Dim i As Long ReDim list(1 To 1) MyPath = "C:\Temp" ChDrive MyPath ChDir MyPath TheFile = Dir("*.xls") Do While TheFile < "" list(UBound(list)) = MyPath & "\" & TheFile ReDim Preserve list(1 To UBound(list) + 1) TheFile = Dir Loop ReDim Preserve list(1 To UBound(list) - 1) For i = 1 To UBound(list) Set wb = Workbooks.Open(list(i)) MsgBox wb.FullName wb.Close Next i End Sub -- Regards, Tom Ogilvy "rjr" wrote in message .. . Gary my apologies, I used your code as a standalone and the first loop worked fine and opened the first file, but when it did the loop errored out as did Otto's, on the second round. This is the same thing that happened with Otto's code and does it consistentantly with my desktop and my laptop. I'm sorry for not getting back with you on this. Can you think of a reason your 2003 would work and mine wouldn't? Bob Reynolds "Gary Keramidas" <GKeramidasATmsn.com wrote in message ... this works for me Sub AllFolderFiles() Dim wb As Workbook Dim TheFile As String Dim MyPath As String MyPath = "C:\Temp" TheFile = Dir(MyPath & "\" & "*.xls") Do While TheFile < "" Set wb = Workbooks.Open(MyPath & "\" & TheFile) MsgBox wb.FullName wb.Close TheFile = Dir() Loop End Sub -- Gary "rjr" wrote in message .. . Hello again to all, I wish it were that simple. Nope I'm windows XP Media Center Edition with all current updates and a valid updated Office Professional product. As a side note I've also placed it on my Dell D520 laptop with Windoes XP Professional and Office 2003 with the exact same results. Since it works for everyone else it obviously has to be something with my computers, but WHAT? My dell laptop is relatively clean without a lot of additional programs and the results are the same. That's why I think the problem lies with my machine's setup... Bob R "Chip Pearson" wrote in message ... Otto, This is out of the blue, but perhaps the user having the problem is using a Macintosh? If this proves to be the case, the code will fail on the line indicated because Macs don't use the '\' character as a path separator. Instead, they use, I think, a ':' character. Instead of hard coding the '\', use Application.PathSeparator. E.g., Set wb = Workbooks.Open(MyPath & Application.PathSeparator & TheFile) The code you posted works fine for me in Excel 2007 for Windows. -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting, LLC www.cpearson.com (email address is on the web site) "Otto Moehrbach" wrote in message ... Tom Thanks for your help as well as Dave and Chip. Here's what the OP and I just did to establish a hard starting point. He set all the code I had written for him aside. Instead, he used the macro from: http://www.contextures.com/xlfaqMac.html#LoopBooks with NO CHANGES of any kind. No other macro or macro call was used. He established a path in his computer to match that used in the macro and placed his test files in that folder. He ran the macro. The error message is: Run-time error '1004' Method 'Open' of object 'Workbook' failed. The highlighted code line is the "Set wb = ............" The full macro is: Sub AllFolderFiles() Dim wb As Workbook Dim TheFile As String Dim MyPath As String MyPath = "C:\Temp" ChDir MyPath TheFile = Dir("*.xls") Do While TheFile < "" Set wb = Workbooks.Open(MyPath & "\" & TheFile) MsgBox wb.FullName wb.Close TheFile = Dir Loop End SubWe will try any suggestion you and the others can come up with. Thanks for your time. Otto"Tom Ogilvy" wrote in message ... Since you don't show the code, have him send you the file that fails to open and test the code with that file. Perhaps the file is damaged or not a file Excel will open. -- Regards, Tom Ogilvy "Otto Moehrbach" wrote in message ... Excel XP & Win XP I'm helping an OP with a project. The code loops through all the files in a folder, opens each, does things, closes the file and opens the next file, etc. The code is placed in the Personal.xls file. It works fine for me. It fails on opening the first file for him (will not open the file). An error is produced saying that opening the file failed. He has Excel 2003. He sent me his Personal.xls file. I changed the name and put it in my XLSTART folder. It works fine with my path. I created his path on my computer and that works fine too. I checked the VBE - Tools - References. The only thing I have checked that he doesn't is "Microsoft Forms 2.0 Object Library". He doesn't have anything like that to check. Does anyone have any ideas of what I could do? Thanks for your time. Otto |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Code looping through files
are you using xp? create new user and try to run it under the new user. -- Gary "rjr" wrote in message .. . Same error, first file opens ok and then once the loop starts on the second one the error with the offending line at Set wb = Workbooks.Open(fl.Path) Bob "Tom Ogilvy" wrote in message ... That is the issue. It works for everyone else. Version of Excel shouldn't make any difference and doesn't for me. Anyway, you can try this one which uses another approach: Sub AllFiles() Dim sFol As String Dim fso As Object, fl As Object Dim fld As Object Dim wb As Workbook sFol = "C:\temp" Set fso = CreateObject("Scripting.FileSystemObject") Set fld = fso.GetFolder(sFol) For Each fl In fld.Files If fl.Name Like "*.xls" Then Set wb = Workbooks.Open(fl.Path) MsgBox wb.FullName wb.Close End If Next End Sub -- Regards, Tom Ogilvy "rjr" wrote in message . .. New files, new data and same result.. Thanks I really need this to work since I'm using 2003 and so's my wife. Bob "Gary Keramidas" <GKeramidasATmsn.com wrote in message ... does it work if you only put 2 files in the folder? does it work if you create 2 new dummy files and put them in the folder? another folder with a different name? -- Gary "rjr" wrote in message . .. No disabled items found. Opened them manually as late as this evening. I've opened them without issue at least 15 times today. Bob "Tom Ogilvy" wrote in message ... That eliminates using the DIR command as a possible source of the problem. Since the problem is in xl2003, then perhaps: In excel I would go to Tools=About Microsoft Excel, then look in Disabled Items button at the bottom. If any of your files are listed there, then I would remove them from being disabled (select the appropriate files one at a time in the list and hit the Enable button for each). You said you had opened them manually, but that may have been sometime in the past. -- Regards, Tom Ogilvy "rjr" wrote in message ... Tom Thank you, I ran it and it ran to the first msgbox and then displayed the first file name. WHen I selected ok it errored out again with the 1004 error and the debug highlighted this area of code Set wb = Workbooks.Open(list(i)) Any help?? BOb "Tom Ogilvy" wrote in message ... Try it like this Sub AllFolderFiles() Dim wb As Workbook Dim TheFile As String Dim MyPath As String Dim list() As String Dim i As Long ReDim list(1 To 1) MyPath = "C:\Temp" ChDrive MyPath ChDir MyPath TheFile = Dir("*.xls") Do While TheFile < "" list(UBound(list)) = MyPath & "\" & TheFile ReDim Preserve list(1 To UBound(list) + 1) TheFile = Dir Loop ReDim Preserve list(1 To UBound(list) - 1) For i = 1 To UBound(list) Set wb = Workbooks.Open(list(i)) MsgBox wb.FullName wb.Close Next i End Sub -- Regards, Tom Ogilvy "rjr" wrote in message .. . Gary my apologies, I used your code as a standalone and the first loop worked fine and opened the first file, but when it did the loop errored out as did Otto's, on the second round. This is the same thing that happened with Otto's code and does it consistentantly with my desktop and my laptop. I'm sorry for not getting back with you on this. Can you think of a reason your 2003 would work and mine wouldn't? Bob Reynolds "Gary Keramidas" <GKeramidasATmsn.com wrote in message ... this works for me Sub AllFolderFiles() Dim wb As Workbook Dim TheFile As String Dim MyPath As String MyPath = "C:\Temp" TheFile = Dir(MyPath & "\" & "*.xls") Do While TheFile < "" Set wb = Workbooks.Open(MyPath & "\" & TheFile) MsgBox wb.FullName wb.Close TheFile = Dir() Loop End Sub -- Gary "rjr" wrote in message .. . Hello again to all, I wish it were that simple. Nope I'm windows XP Media Center Edition with all current updates and a valid updated Office Professional product. As a side note I've also placed it on my Dell D520 laptop with Windoes XP Professional and Office 2003 with the exact same results. Since it works for everyone else it obviously has to be something with my computers, but WHAT? My dell laptop is relatively clean without a lot of additional programs and the results are the same. That's why I think the problem lies with my machine's setup... Bob R "Chip Pearson" wrote in message ... Otto, This is out of the blue, but perhaps the user having the problem is using a Macintosh? If this proves to be the case, the code will fail on the line indicated because Macs don't use the '\' character as a path separator. Instead, they use, I think, a ':' character. Instead of hard coding the '\', use Application.PathSeparator. E.g., Set wb = Workbooks.Open(MyPath & Application.PathSeparator & TheFile) The code you posted works fine for me in Excel 2007 for Windows. -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting, LLC www.cpearson.com (email address is on the web site) "Otto Moehrbach" wrote in message ... Tom Thanks for your help as well as Dave and Chip. Here's what the OP and I just did to establish a hard starting point. He set all the code I had written for him aside. Instead, he used the macro from: http://www.contextures.com/xlfaqMac.html#LoopBooks with NO CHANGES of any kind. No other macro or macro call was used. He established a path in his computer to match that used in the macro and placed his test files in that folder. He ran the macro. The error message is: Run-time error '1004' Method 'Open' of object 'Workbook' failed. The highlighted code line is the "Set wb = ............" The full macro is: Sub AllFolderFiles() Dim wb As Workbook Dim TheFile As String Dim MyPath As String MyPath = "C:\Temp" ChDir MyPath TheFile = Dir("*.xls") Do While TheFile < "" Set wb = Workbooks.Open(MyPath & "\" & TheFile) MsgBox wb.FullName wb.Close TheFile = Dir Loop End SubWe will try any suggestion you and the others can come up with. Thanks for your time. Otto"Tom Ogilvy" wrote in message ... Since you don't show the code, have him send you the file that fails to open and test the code with that file. Perhaps the file is damaged or not a file Excel will open. -- Regards, Tom Ogilvy "Otto Moehrbach" wrote in message ... Excel XP & Win XP I'm helping an OP with a project. The code loops through all the files in a folder, opens each, does things, closes the file and opens the next file, etc. The code is placed in the Personal.xls file. It works fine for me. It fails on opening the first file for him (will not open the file). An error is produced saying that opening the file failed. He has Excel 2003. He sent me his Personal.xls file. I changed the name and put it in my XLSTART folder. It works fine with my path. I created his path on my computer and that works fine too. I checked the VBE - Tools - References. The only thing I have checked that he doesn't is "Microsoft Forms 2.0 Object Library". He doesn't have anything like that to check. Does anyone have any ideas of what I could do? Thanks for your time. Otto |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Code looping through files
Is the code in a General/Standard module (The results of Insert=Module in
the VBE)? rather than in a sheet module or the Thisworkbook module. If not, try it in a General/Standard module. Also, this is slightly modified. Sub AllFiles() Dim sFol As String Dim fso As Object, fl As Object Dim fld As Object Dim wb As Workbook sFol = "C:\temp" Set fso = CreateObject("Scripting.FileSystemObject") Set fld = fso.GetFolder(sFol) For Each fl In fld.Files if lcase(fl.Path) < lcase(thisworkbook.Fullname) then If fl.Name Like "*.xls" Then Set wb = Workbooks.Open(fl.Path) MsgBox wb.FullName wb.Close End If End if Next End Sub -- Regards, Tom Ogilvy "rjr" wrote in message .. . Same error, first file opens ok and then once the loop starts on the second one the error with the offending line at Set wb = Workbooks.Open(fl.Path) Bob "Tom Ogilvy" wrote in message ... That is the issue. It works for everyone else. Version of Excel shouldn't make any difference and doesn't for me. Anyway, you can try this one which uses another approach: Sub AllFiles() Dim sFol As String Dim fso As Object, fl As Object Dim fld As Object Dim wb As Workbook sFol = "C:\temp" Set fso = CreateObject("Scripting.FileSystemObject") Set fld = fso.GetFolder(sFol) For Each fl In fld.Files If fl.Name Like "*.xls" Then Set wb = Workbooks.Open(fl.Path) MsgBox wb.FullName wb.Close End If Next End Sub -- Regards, Tom Ogilvy "rjr" wrote in message . .. New files, new data and same result.. Thanks I really need this to work since I'm using 2003 and so's my wife. Bob "Gary Keramidas" <GKeramidasATmsn.com wrote in message ... does it work if you only put 2 files in the folder? does it work if you create 2 new dummy files and put them in the folder? another folder with a different name? -- Gary "rjr" wrote in message . .. No disabled items found. Opened them manually as late as this evening. I've opened them without issue at least 15 times today. Bob "Tom Ogilvy" wrote in message ... That eliminates using the DIR command as a possible source of the problem. Since the problem is in xl2003, then perhaps: In excel I would go to Tools=About Microsoft Excel, then look in Disabled Items button at the bottom. If any of your files are listed there, then I would remove them from being disabled (select the appropriate files one at a time in the list and hit the Enable button for each). You said you had opened them manually, but that may have been sometime in the past. -- Regards, Tom Ogilvy "rjr" wrote in message ... Tom Thank you, I ran it and it ran to the first msgbox and then displayed the first file name. WHen I selected ok it errored out again with the 1004 error and the debug highlighted this area of code Set wb = Workbooks.Open(list(i)) Any help?? BOb "Tom Ogilvy" wrote in message ... Try it like this Sub AllFolderFiles() Dim wb As Workbook Dim TheFile As String Dim MyPath As String Dim list() As String Dim i As Long ReDim list(1 To 1) MyPath = "C:\Temp" ChDrive MyPath ChDir MyPath TheFile = Dir("*.xls") Do While TheFile < "" list(UBound(list)) = MyPath & "\" & TheFile ReDim Preserve list(1 To UBound(list) + 1) TheFile = Dir Loop ReDim Preserve list(1 To UBound(list) - 1) For i = 1 To UBound(list) Set wb = Workbooks.Open(list(i)) MsgBox wb.FullName wb.Close Next i End Sub -- Regards, Tom Ogilvy "rjr" wrote in message .. . Gary my apologies, I used your code as a standalone and the first loop worked fine and opened the first file, but when it did the loop errored out as did Otto's, on the second round. This is the same thing that happened with Otto's code and does it consistentantly with my desktop and my laptop. I'm sorry for not getting back with you on this. Can you think of a reason your 2003 would work and mine wouldn't? Bob Reynolds "Gary Keramidas" <GKeramidasATmsn.com wrote in message ... this works for me Sub AllFolderFiles() Dim wb As Workbook Dim TheFile As String Dim MyPath As String MyPath = "C:\Temp" TheFile = Dir(MyPath & "\" & "*.xls") Do While TheFile < "" Set wb = Workbooks.Open(MyPath & "\" & TheFile) MsgBox wb.FullName wb.Close TheFile = Dir() Loop End Sub -- Gary "rjr" wrote in message .. . Hello again to all, I wish it were that simple. Nope I'm windows XP Media Center Edition with all current updates and a valid updated Office Professional product. As a side note I've also placed it on my Dell D520 laptop with Windoes XP Professional and Office 2003 with the exact same results. Since it works for everyone else it obviously has to be something with my computers, but WHAT? My dell laptop is relatively clean without a lot of additional programs and the results are the same. That's why I think the problem lies with my machine's setup... Bob R "Chip Pearson" wrote in message ... Otto, This is out of the blue, but perhaps the user having the problem is using a Macintosh? If this proves to be the case, the code will fail on the line indicated because Macs don't use the '\' character as a path separator. Instead, they use, I think, a ':' character. Instead of hard coding the '\', use Application.PathSeparator. E.g., Set wb = Workbooks.Open(MyPath & Application.PathSeparator & TheFile) The code you posted works fine for me in Excel 2007 for Windows. -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting, LLC www.cpearson.com (email address is on the web site) "Otto Moehrbach" wrote in message ... Tom Thanks for your help as well as Dave and Chip. Here's what the OP and I just did to establish a hard starting point. He set all the code I had written for him aside. Instead, he used the macro from: http://www.contextures.com/xlfaqMac.html#LoopBooks with NO CHANGES of any kind. No other macro or macro call was used. He established a path in his computer to match that used in the macro and placed his test files in that folder. He ran the macro. The error message is: Run-time error '1004' Method 'Open' of object 'Workbook' failed. The highlighted code line is the "Set wb = ............" The full macro is: Sub AllFolderFiles() Dim wb As Workbook Dim TheFile As String Dim MyPath As String MyPath = "C:\Temp" ChDir MyPath TheFile = Dir("*.xls") Do While TheFile < "" Set wb = Workbooks.Open(MyPath & "\" & TheFile) MsgBox wb.FullName wb.Close TheFile = Dir Loop End SubWe will try any suggestion you and the others can come up with. Thanks for your time. Otto"Tom Ogilvy" wrote in message ... Since you don't show the code, have him send you the file that fails to open and test the code with that file. Perhaps the file is damaged or not a file Excel will open. -- Regards, Tom Ogilvy "Otto Moehrbach" wrote in message ... Excel XP & Win XP I'm helping an OP with a project. The code loops through all the files in a folder, opens each, does things, closes the file and opens the next file, etc. The code is placed in the Personal.xls file. It works fine for me. It fails on opening the first file for him (will not open the file). An error is produced saying that opening the file failed. He has Excel 2003. He sent me his Personal.xls file. I changed the name and put it in my XLSTART folder. It works fine with my path. I created his path on my computer and that works fine too. I checked the VBE - Tools - References. The only thing I have checked that he doesn't is "Microsoft Forms 2.0 Object Library". He doesn't have anything like that to check. Does anyone have any ideas of what I could do? Thanks for your time. Otto |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Code looping through files
Here is another to try
Sub AllFiles() Dim sFol As String Dim fso As Object, fl As Object Dim fld As Object Dim wb As Workbook sFol = "C:\temp" Set fso = CreateObject("Scripting.FileSystemObject") Set fld = fso.GetFolder(sFol) For Each fl In fld.Files if lcase(fl.Path) < lcase(thisworkbook.Fullname) then If fl.Name Like "*.xls" Then On Error Resume Next Set wb = Workbooks.Open(fl.Path) if err.number = 0 then MsgBox wb.FullName wb.Close else msgbox "Not processed: " & fl.Path err.Clear end if On Error goto 0 End If End if Next End Sub Does it error on a single file? If so, anything special about that file? -- Regards, Tom Ogilvy "Tom Ogilvy" wrote in message ... Is the code in a General/Standard module (The results of Insert=Module in the VBE)? rather than in a sheet module or the Thisworkbook module. If not, try it in a General/Standard module. Also, this is slightly modified. Sub AllFiles() Dim sFol As String Dim fso As Object, fl As Object Dim fld As Object Dim wb As Workbook sFol = "C:\temp" Set fso = CreateObject("Scripting.FileSystemObject") Set fld = fso.GetFolder(sFol) For Each fl In fld.Files if lcase(fl.Path) < lcase(thisworkbook.Fullname) then If fl.Name Like "*.xls" Then Set wb = Workbooks.Open(fl.Path) MsgBox wb.FullName wb.Close End If End if Next End Sub -- Regards, Tom Ogilvy "rjr" wrote in message .. . Same error, first file opens ok and then once the loop starts on the second one the error with the offending line at Set wb = Workbooks.Open(fl.Path) Bob "Tom Ogilvy" wrote in message ... That is the issue. It works for everyone else. Version of Excel shouldn't make any difference and doesn't for me. Anyway, you can try this one which uses another approach: Sub AllFiles() Dim sFol As String Dim fso As Object, fl As Object Dim fld As Object Dim wb As Workbook sFol = "C:\temp" Set fso = CreateObject("Scripting.FileSystemObject") Set fld = fso.GetFolder(sFol) For Each fl In fld.Files If fl.Name Like "*.xls" Then Set wb = Workbooks.Open(fl.Path) MsgBox wb.FullName wb.Close End If Next End Sub -- Regards, Tom Ogilvy "rjr" wrote in message . .. New files, new data and same result.. Thanks I really need this to work since I'm using 2003 and so's my wife. Bob "Gary Keramidas" <GKeramidasATmsn.com wrote in message ... does it work if you only put 2 files in the folder? does it work if you create 2 new dummy files and put them in the folder? another folder with a different name? -- Gary "rjr" wrote in message . .. No disabled items found. Opened them manually as late as this evening. I've opened them without issue at least 15 times today. Bob "Tom Ogilvy" wrote in message ... That eliminates using the DIR command as a possible source of the problem. Since the problem is in xl2003, then perhaps: In excel I would go to Tools=About Microsoft Excel, then look in Disabled Items button at the bottom. If any of your files are listed there, then I would remove them from being disabled (select the appropriate files one at a time in the list and hit the Enable button for each). You said you had opened them manually, but that may have been sometime in the past. -- Regards, Tom Ogilvy "rjr" wrote in message ... Tom Thank you, I ran it and it ran to the first msgbox and then displayed the first file name. WHen I selected ok it errored out again with the 1004 error and the debug highlighted this area of code Set wb = Workbooks.Open(list(i)) Any help?? BOb "Tom Ogilvy" wrote in message ... Try it like this Sub AllFolderFiles() Dim wb As Workbook Dim TheFile As String Dim MyPath As String Dim list() As String Dim i As Long ReDim list(1 To 1) MyPath = "C:\Temp" ChDrive MyPath ChDir MyPath TheFile = Dir("*.xls") Do While TheFile < "" list(UBound(list)) = MyPath & "\" & TheFile ReDim Preserve list(1 To UBound(list) + 1) TheFile = Dir Loop ReDim Preserve list(1 To UBound(list) - 1) For i = 1 To UBound(list) Set wb = Workbooks.Open(list(i)) MsgBox wb.FullName wb.Close Next i End Sub -- Regards, Tom Ogilvy "rjr" wrote in message .. . Gary my apologies, I used your code as a standalone and the first loop worked fine and opened the first file, but when it did the loop errored out as did Otto's, on the second round. This is the same thing that happened with Otto's code and does it consistentantly with my desktop and my laptop. I'm sorry for not getting back with you on this. Can you think of a reason your 2003 would work and mine wouldn't? Bob Reynolds "Gary Keramidas" <GKeramidasATmsn.com wrote in message ... this works for me Sub AllFolderFiles() Dim wb As Workbook Dim TheFile As String Dim MyPath As String MyPath = "C:\Temp" TheFile = Dir(MyPath & "\" & "*.xls") Do While TheFile < "" Set wb = Workbooks.Open(MyPath & "\" & TheFile) MsgBox wb.FullName wb.Close TheFile = Dir() Loop End Sub -- Gary "rjr" wrote in message .. . Hello again to all, I wish it were that simple. Nope I'm windows XP Media Center Edition with all current updates and a valid updated Office Professional product. As a side note I've also placed it on my Dell D520 laptop with Windoes XP Professional and Office 2003 with the exact same results. Since it works for everyone else it obviously has to be something with my computers, but WHAT? My dell laptop is relatively clean without a lot of additional programs and the results are the same. That's why I think the problem lies with my machine's setup... Bob R "Chip Pearson" wrote in message ... Otto, This is out of the blue, but perhaps the user having the problem is using a Macintosh? If this proves to be the case, the code will fail on the line indicated because Macs don't use the '\' character as a path separator. Instead, they use, I think, a ':' character. Instead of hard coding the '\', use Application.PathSeparator. E.g., Set wb = Workbooks.Open(MyPath & Application.PathSeparator & TheFile) The code you posted works fine for me in Excel 2007 for Windows. -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting, LLC www.cpearson.com (email address is on the web site) "Otto Moehrbach" wrote in message ... Tom Thanks for your help as well as Dave and Chip. Here's what the OP and I just did to establish a hard starting point. He set all the code I had written for him aside. Instead, he used the macro from: http://www.contextures.com/xlfaqMac.html#LoopBooks with NO CHANGES of any kind. No other macro or macro call was used. He established a path in his computer to match that used in the macro and placed his test files in that folder. He ran the macro. The error message is: Run-time error '1004' Method 'Open' of object 'Workbook' failed. The highlighted code line is the "Set wb = ............" The full macro is: Sub AllFolderFiles() Dim wb As Workbook Dim TheFile As String Dim MyPath As String MyPath = "C:\Temp" ChDir MyPath TheFile = Dir("*.xls") Do While TheFile < "" Set wb = Workbooks.Open(MyPath & "\" & TheFile) MsgBox wb.FullName wb.Close TheFile = Dir Loop End SubWe will try any suggestion you and the others can come up with. Thanks for your time. Otto"Tom Ogilvy" wrote in message ... Since you don't show the code, have him send you the file that fails to open and test the code with that file. Perhaps the file is damaged or not a file Excel will open. -- Regards, Tom Ogilvy "Otto Moehrbach" wrote in message ... Excel XP & Win XP I'm helping an OP with a project. The code loops through all the files in a folder, opens each, does things, closes the file and opens the next file, etc. The code is placed in the Personal.xls file. It works fine for me. It fails on opening the first file for him (will not open the file). An error is produced saying that opening the file failed. He has Excel 2003. He sent me his Personal.xls file. I changed the name and put it in my XLSTART folder. It works fine with my path. I created his path on my computer and that works fine too. I checked the VBE - Tools - References. The only thing I have checked that he doesn't is "Microsoft Forms 2.0 Object Library". He doesn't have anything like that to check. Does anyone have any ideas of what I could do? Thanks for your time. Otto |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Code looping through files
This is located in a Personal.xls file and not affixed to a particular
worksheet.. I'm pasting the code now and will see how it works. Thanks BOB "Tom Ogilvy" wrote in message ... Is the code in a General/Standard module (The results of Insert=Module in the VBE)? rather than in a sheet module or the Thisworkbook module. If not, try it in a General/Standard module. Also, this is slightly modified. Sub AllFiles() Dim sFol As String Dim fso As Object, fl As Object Dim fld As Object Dim wb As Workbook sFol = "C:\temp" Set fso = CreateObject("Scripting.FileSystemObject") Set fld = fso.GetFolder(sFol) For Each fl In fld.Files if lcase(fl.Path) < lcase(thisworkbook.Fullname) then If fl.Name Like "*.xls" Then Set wb = Workbooks.Open(fl.Path) MsgBox wb.FullName wb.Close End If End if Next End Sub -- Regards, Tom Ogilvy "rjr" wrote in message .. . Same error, first file opens ok and then once the loop starts on the second one the error with the offending line at Set wb = Workbooks.Open(fl.Path) Bob "Tom Ogilvy" wrote in message ... That is the issue. It works for everyone else. Version of Excel shouldn't make any difference and doesn't for me. Anyway, you can try this one which uses another approach: Sub AllFiles() Dim sFol As String Dim fso As Object, fl As Object Dim fld As Object Dim wb As Workbook sFol = "C:\temp" Set fso = CreateObject("Scripting.FileSystemObject") Set fld = fso.GetFolder(sFol) For Each fl In fld.Files If fl.Name Like "*.xls" Then Set wb = Workbooks.Open(fl.Path) MsgBox wb.FullName wb.Close End If Next End Sub -- Regards, Tom Ogilvy "rjr" wrote in message . .. New files, new data and same result.. Thanks I really need this to work since I'm using 2003 and so's my wife. Bob "Gary Keramidas" <GKeramidasATmsn.com wrote in message ... does it work if you only put 2 files in the folder? does it work if you create 2 new dummy files and put them in the folder? another folder with a different name? -- Gary "rjr" wrote in message . .. No disabled items found. Opened them manually as late as this evening. I've opened them without issue at least 15 times today. Bob "Tom Ogilvy" wrote in message ... That eliminates using the DIR command as a possible source of the problem. Since the problem is in xl2003, then perhaps: In excel I would go to Tools=About Microsoft Excel, then look in Disabled Items button at the bottom. If any of your files are listed there, then I would remove them from being disabled (select the appropriate files one at a time in the list and hit the Enable button for each). You said you had opened them manually, but that may have been sometime in the past. -- Regards, Tom Ogilvy "rjr" wrote in message ... Tom Thank you, I ran it and it ran to the first msgbox and then displayed the first file name. WHen I selected ok it errored out again with the 1004 error and the debug highlighted this area of code Set wb = Workbooks.Open(list(i)) Any help?? BOb "Tom Ogilvy" wrote in message ... Try it like this Sub AllFolderFiles() Dim wb As Workbook Dim TheFile As String Dim MyPath As String Dim list() As String Dim i As Long ReDim list(1 To 1) MyPath = "C:\Temp" ChDrive MyPath ChDir MyPath TheFile = Dir("*.xls") Do While TheFile < "" list(UBound(list)) = MyPath & "\" & TheFile ReDim Preserve list(1 To UBound(list) + 1) TheFile = Dir Loop ReDim Preserve list(1 To UBound(list) - 1) For i = 1 To UBound(list) Set wb = Workbooks.Open(list(i)) MsgBox wb.FullName wb.Close Next i End Sub -- Regards, Tom Ogilvy "rjr" wrote in message .. . Gary my apologies, I used your code as a standalone and the first loop worked fine and opened the first file, but when it did the loop errored out as did Otto's, on the second round. This is the same thing that happened with Otto's code and does it consistentantly with my desktop and my laptop. I'm sorry for not getting back with you on this. Can you think of a reason your 2003 would work and mine wouldn't? Bob Reynolds "Gary Keramidas" <GKeramidasATmsn.com wrote in message ... this works for me Sub AllFolderFiles() Dim wb As Workbook Dim TheFile As String Dim MyPath As String MyPath = "C:\Temp" TheFile = Dir(MyPath & "\" & "*.xls") Do While TheFile < "" Set wb = Workbooks.Open(MyPath & "\" & TheFile) MsgBox wb.FullName wb.Close TheFile = Dir() Loop End Sub -- Gary "rjr" wrote in message .. . Hello again to all, I wish it were that simple. Nope I'm windows XP Media Center Edition with all current updates and a valid updated Office Professional product. As a side note I've also placed it on my Dell D520 laptop with Windoes XP Professional and Office 2003 with the exact same results. Since it works for everyone else it obviously has to be something with my computers, but WHAT? My dell laptop is relatively clean without a lot of additional programs and the results are the same. That's why I think the problem lies with my machine's setup... Bob R "Chip Pearson" wrote in message ... Otto, This is out of the blue, but perhaps the user having the problem is using a Macintosh? If this proves to be the case, the code will fail on the line indicated because Macs don't use the '\' character as a path separator. Instead, they use, I think, a ':' character. Instead of hard coding the '\', use Application.PathSeparator. E.g., Set wb = Workbooks.Open(MyPath & Application.PathSeparator & TheFile) The code you posted works fine for me in Excel 2007 for Windows. -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting, LLC www.cpearson.com (email address is on the web site) "Otto Moehrbach" wrote in message ... Tom Thanks for your help as well as Dave and Chip. Here's what the OP and I just did to establish a hard starting point. He set all the code I had written for him aside. Instead, he used the macro from: http://www.contextures.com/xlfaqMac.html#LoopBooks with NO CHANGES of any kind. No other macro or macro call was used. He established a path in his computer to match that used in the macro and placed his test files in that folder. He ran the macro. The error message is: Run-time error '1004' Method 'Open' of object 'Workbook' failed. The highlighted code line is the "Set wb = ............" The full macro is: Sub AllFolderFiles() Dim wb As Workbook Dim TheFile As String Dim MyPath As String MyPath = "C:\Temp" ChDir MyPath TheFile = Dir("*.xls") Do While TheFile < "" Set wb = Workbooks.Open(MyPath & "\" & TheFile) MsgBox wb.FullName wb.Close TheFile = Dir Loop End SubWe will try any suggestion you and the others can come up with. Thanks for your time. Otto"Tom Ogilvy" wrote in message ... Since you don't show the code, have him send you the file that fails to open and test the code with that file. Perhaps the file is damaged or not a file Excel will open. -- Regards, Tom Ogilvy "Otto Moehrbach" wrote in message ... Excel XP & Win XP I'm helping an OP with a project. The code loops through all the files in a folder, opens each, does things, closes the file and opens the next file, etc. The code is placed in the Personal.xls file. It works fine for me. It fails on opening the first file for him (will not open the file). An error is produced saying that opening the file failed. He has Excel 2003. He sent me his Personal.xls file. I changed the name and put it in my XLSTART folder. It works fine with my path. I created his path on my computer and that works fine too. I checked the VBE - Tools - References. The only thing I have checked that he doesn't is "Microsoft Forms 2.0 Object Library". He doesn't have anything like that to check. Does anyone have any ideas of what I could do? Thanks for your time. Otto |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
Code looping through files
Tom that code gave me the same error on the second file with the
Set wb = Workbooks.Open(fl.Path) as the problem area. I'm interested this code is in personal.xls and is listed under the general tab. Is there someplace else that I could put it that it might work?? Also in reply to Gary, yes I'm running XP under "owner" which is me, and administrator privleges. Are you saying to try it opening it with say "Guest" and then try it? Thanks to all BOB "Tom Ogilvy" wrote in message ... Is the code in a General/Standard module (The results of Insert=Module in the VBE)? rather than in a sheet module or the Thisworkbook module. If not, try it in a General/Standard module. Also, this is slightly modified. Sub AllFiles() Dim sFol As String Dim fso As Object, fl As Object Dim fld As Object Dim wb As Workbook sFol = "C:\temp" Set fso = CreateObject("Scripting.FileSystemObject") Set fld = fso.GetFolder(sFol) For Each fl In fld.Files if lcase(fl.Path) < lcase(thisworkbook.Fullname) then If fl.Name Like "*.xls" Then Set wb = Workbooks.Open(fl.Path) MsgBox wb.FullName wb.Close End If End if Next End Sub -- Regards, Tom Ogilvy "rjr" wrote in message .. . Same error, first file opens ok and then once the loop starts on the second one the error with the offending line at Set wb = Workbooks.Open(fl.Path) Bob "Tom Ogilvy" wrote in message ... That is the issue. It works for everyone else. Version of Excel shouldn't make any difference and doesn't for me. Anyway, you can try this one which uses another approach: Sub AllFiles() Dim sFol As String Dim fso As Object, fl As Object Dim fld As Object Dim wb As Workbook sFol = "C:\temp" Set fso = CreateObject("Scripting.FileSystemObject") Set fld = fso.GetFolder(sFol) For Each fl In fld.Files If fl.Name Like "*.xls" Then Set wb = Workbooks.Open(fl.Path) MsgBox wb.FullName wb.Close End If Next End Sub -- Regards, Tom Ogilvy "rjr" wrote in message . .. New files, new data and same result.. Thanks I really need this to work since I'm using 2003 and so's my wife. Bob "Gary Keramidas" <GKeramidasATmsn.com wrote in message ... does it work if you only put 2 files in the folder? does it work if you create 2 new dummy files and put them in the folder? another folder with a different name? -- Gary "rjr" wrote in message . .. No disabled items found. Opened them manually as late as this evening. I've opened them without issue at least 15 times today. Bob "Tom Ogilvy" wrote in message ... That eliminates using the DIR command as a possible source of the problem. Since the problem is in xl2003, then perhaps: In excel I would go to Tools=About Microsoft Excel, then look in Disabled Items button at the bottom. If any of your files are listed there, then I would remove them from being disabled (select the appropriate files one at a time in the list and hit the Enable button for each). You said you had opened them manually, but that may have been sometime in the past. -- Regards, Tom Ogilvy "rjr" wrote in message ... Tom Thank you, I ran it and it ran to the first msgbox and then displayed the first file name. WHen I selected ok it errored out again with the 1004 error and the debug highlighted this area of code Set wb = Workbooks.Open(list(i)) Any help?? BOb "Tom Ogilvy" wrote in message ... Try it like this Sub AllFolderFiles() Dim wb As Workbook Dim TheFile As String Dim MyPath As String Dim list() As String Dim i As Long ReDim list(1 To 1) MyPath = "C:\Temp" ChDrive MyPath ChDir MyPath TheFile = Dir("*.xls") Do While TheFile < "" list(UBound(list)) = MyPath & "\" & TheFile ReDim Preserve list(1 To UBound(list) + 1) TheFile = Dir Loop ReDim Preserve list(1 To UBound(list) - 1) For i = 1 To UBound(list) Set wb = Workbooks.Open(list(i)) MsgBox wb.FullName wb.Close Next i End Sub -- Regards, Tom Ogilvy "rjr" wrote in message .. . Gary my apologies, I used your code as a standalone and the first loop worked fine and opened the first file, but when it did the loop errored out as did Otto's, on the second round. This is the same thing that happened with Otto's code and does it consistentantly with my desktop and my laptop. I'm sorry for not getting back with you on this. Can you think of a reason your 2003 would work and mine wouldn't? Bob Reynolds "Gary Keramidas" <GKeramidasATmsn.com wrote in message ... this works for me Sub AllFolderFiles() Dim wb As Workbook Dim TheFile As String Dim MyPath As String MyPath = "C:\Temp" TheFile = Dir(MyPath & "\" & "*.xls") Do While TheFile < "" Set wb = Workbooks.Open(MyPath & "\" & TheFile) MsgBox wb.FullName wb.Close TheFile = Dir() Loop End Sub -- Gary "rjr" wrote in message .. . Hello again to all, I wish it were that simple. Nope I'm windows XP Media Center Edition with all current updates and a valid updated Office Professional product. As a side note I've also placed it on my Dell D520 laptop with Windoes XP Professional and Office 2003 with the exact same results. Since it works for everyone else it obviously has to be something with my computers, but WHAT? My dell laptop is relatively clean without a lot of additional programs and the results are the same. That's why I think the problem lies with my machine's setup... Bob R "Chip Pearson" wrote in message ... Otto, This is out of the blue, but perhaps the user having the problem is using a Macintosh? If this proves to be the case, the code will fail on the line indicated because Macs don't use the '\' character as a path separator. Instead, they use, I think, a ':' character. Instead of hard coding the '\', use Application.PathSeparator. E.g., Set wb = Workbooks.Open(MyPath & Application.PathSeparator & TheFile) The code you posted works fine for me in Excel 2007 for Windows. -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting, LLC www.cpearson.com (email address is on the web site) "Otto Moehrbach" wrote in message ... Tom Thanks for your help as well as Dave and Chip. Here's what the OP and I just did to establish a hard starting point. He set all the code I had written for him aside. Instead, he used the macro from: http://www.contextures.com/xlfaqMac.html#LoopBooks with NO CHANGES of any kind. No other macro or macro call was used. He established a path in his computer to match that used in the macro and placed his test files in that folder. He ran the macro. The error message is: Run-time error '1004' Method 'Open' of object 'Workbook' failed. The highlighted code line is the "Set wb = ............" The full macro is: Sub AllFolderFiles() Dim wb As Workbook Dim TheFile As String Dim MyPath As String MyPath = "C:\Temp" ChDir MyPath TheFile = Dir("*.xls") Do While TheFile < "" Set wb = Workbooks.Open(MyPath & "\" & TheFile) MsgBox wb.FullName wb.Close TheFile = Dir Loop End SubWe will try any suggestion you and the others can come up with. Thanks for your time. Otto"Tom Ogilvy" wrote in message ... Since you don't show the code, have him send you the file that fails to open and test the code with that file. Perhaps the file is damaged or not a file Excel will open. -- Regards, Tom Ogilvy "Otto Moehrbach" wrote in message ... Excel XP & Win XP I'm helping an OP with a project. The code loops through all the files in a folder, opens each, does things, closes the file and opens the next file, etc. The code is placed in the Personal.xls file. It works fine for me. It fails on opening the first file for him (will not open the file). An error is produced saying that opening the file failed. He has Excel 2003. He sent me his Personal.xls file. I changed the name and put it in my XLSTART folder. It works fine with my path. I created his path on my computer and that works fine too. I checked the VBE - Tools - References. The only thing I have checked that he doesn't is "Microsoft Forms 2.0 Object Library". He doesn't have anything like that to check. Does anyone have any ideas of what I could do? Thanks for your time. Otto |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Looping thru Files In FolderSub | Excel Programming | |||
Looping through excel files to add to a new workbook | Excel Programming | |||
Looping through files in a folder | Excel Programming | |||
Looping thru files extracting data | Excel Programming | |||
Looping thru files | Excel Programming |