Home |
Search |
Today's Posts |
#41
Posted to microsoft.public.excel.programming
|
|||
|
|||
Code looping through files
Sorry, that would be
Help=About Microsoft Excel Typo. -- Regards, Tom Ogilvy "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 |
#42
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 |
#43
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 |
#44
Posted to microsoft.public.excel.programming
|
|||
|
|||
Code looping through files
How about posting the exact code that you're using?
rjr wrote: Got the runtime error 1004 method open of object workbooks failed with the debug showing Set wb = Workbooks.Open(list(i)) 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 . .. Dave, my apologies for not getting back sooner, kinda forgot where I'm at . Yes I tried the subroutine you provided Option Explicit Sub Testme01() Dim myName As String Dim wb As Workbook myName = "C:\Documents and Settings\Owner\Desktop\KathleenFolder\asf.xls" Set wb = Workbooks.Open(myName) End Sub and it worked fine. I changed the my name to c:\temp which is where we we're now working out of. It seems the problem occurs when the code loops and it won't open the second file as it did the first. I am able to open all the files in that folder individually, but not through the code. I also tried Gary's little routine that worked on 2003 and it stopped at the same location as Otto's code did. Is that a setting that might be creating problems for me inside of 2003? Thanks again Bob Reynolds "Dave Peterson" wrote in message ... First, did you try that little subroutine? Otto posted this code as the basis for the code you're using. 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 Sub This could cause trouble if the logged drive isn't the C: drive. (Changing the folder won't change the drive.) Does adding another line help? Sub AllFolderFiles() Dim wb As Workbook Dim TheFile As String Dim MyPath As String MyPath = "C:\Temp" ChDir MyPath ChDrive myPath '<--- Added TheFile = Dir("*.xls") Do While TheFile < "" Set wb = Workbooks.Open(MyPath & "\" & TheFile) MsgBox wb.FullName wb.Close TheFile = Dir Loop End Sub rjr wrote: Latest Update. Otto and I worked and worked and discovered he is using Excel 2002 Professional and I am using Excel 2003 Professional with Windows XP Media Edition 2005. I can't get it to work on my computer at all, so I thought I'd try my laptop which is new and has Excel 2003 Professional, along with Windows XP Professional also. Then I decided to try my wife's laptop. She is running Windows XP Home and Office 2000 Professional and consequently Excel 2000 Professional. Lo and Behold the code ran correctly and did exactly what Otto swore it would do and did on his computer. Now my question is does anyone know of a reason this should be possible or is there an issue with 2003 that needs to be turned on to make them work.? I really need to make this work on an Excel 2003 program so I'm kinda back where I was..... Any advice would be greatly appreciated and I would also like to thank everyone that helped in gaining resolution with this problem, especially on New Year's Eve. Bob Reynolds "Dave Peterson" wrote in message ... I wanted you to try the separate procedure as a test--not a replacement for the original procedure. And I can't think of any other reasons--and I wouldn't guess that reinstalling Office (or windows!) would help--but I've been wrong lots of times. rjr wrote: "Dave Peterson" wrote in message ... I wouldn't guess so--but that's just a guess. I'm still guessing that it's something simple--like a locked file. *** none of the files are locked or protected. If you create a separate procedure in a test module: *****No because what we're trying to do is go down the directory of .xls files and open, do the procedure and then close -- move to next one and redo the procedure until all files (approx 22) have had the code run on them and it's completed. I will add that if I take this portion (where the error occurs) of the code out of it, the code runs perfectly on each file operated independently but it requires me to run it on each one individually. Option Explicit sub Testme01() dim myName as string dim wb as workbook myName = "C:\Documents and Settings\Owner\Desktop\KathleenFolder\asf.xls" set wb = workbooks.open(myname) End sub === I don't think that this will help... But if you move that asf.xls file to a different location, does the original procedure work ok? I can't imagine what can be happening where you can open this file manually, but have problems when you open it via code. It will open by single code, like I said, but once we try it through the loop he hangs up right there. Otto has it working fine (With the same files) on his computer and I even transferred all my miles and code to another laptop I have running windows xp pro and office 2003 with the same exact results I get. That's why I was thinking it might be a setting on my computer...Again, if it works on other computers that would tend to isolate mine as the cause. Is there any reasons that you can think of..... Lastly do you think removing and reinstalling office would help. I can't see where it would since all features are installed, but I don't know enough to make a really educated guess.... Thanks Bob Reynolds rjr wrote: C:\Documents and Settings\Owner\Desktop\KathleenFolder\asf.xls This is the location of where the file is and the asf.xls is the file that was the first one to be opened. Would a new installation of Office Professional help, do you think??? Bob "Dave Peterson" wrote in message ... I don't have a guess, but I am curious about what the msgbox showed. What was the filename (or path and filename)? rjr wrote: I put Tom's in and the msgbox returned the complete directory that we're using as the directory and the proper file name. It still errored out on the Set wb line... I'm thinking that it has to do with some setting I've got on my computer that's keeping this from running. Otto had run it two different ways and also used my file and his ran perfectly, but I've continued getting the error. Bob "Tom Ogilvy" wrote in message ... Here is another guess - Change the code to: Do While TheFile < "" If TheFile < "Daily Error report MASTER.xls" Then MsgBox ThePath & "\" & TheFile Set wb = Workbooks.Open(ThePath & "\" & TheFile) make sure you actually need to append the "\" and don't actually have a filename result like: C:\\Report1.xls In windows NT type Operating systems, this will still work, but in Windows 9x, it will error. -- Regards, Tom Ogilvy "rjr" wrote in message . .. Run Time Error 1004 Method OPEN of object.workbooks failed. This is in the Microsoft vb alert window. the Error place in the code is at Do While TheFile < "" If TheFile < "Daily Error report MASTER.xls" Then MsgBox TheFile Set wb = Workbooks.Open(ThePath & "\" & TheFile) << error line. I've sent Otto the files while he was working on the code. The code works perfectly for him at his computer. I can open ALL files from file open or double clicking on the file. "Dave Peterson" wrote in message ... What's the error that is displayed? Any chance that Otto has access to that folder and you don't? Can you open the file just via File|Open? rjr wrote: Hi, I've got the problem that Otto is helping me with. I've done a complete detect and repair and as Chip has suggested I've looked in the References List box and I do have Microsoft Forms 2.0 Object Library installed and checked. I also have the FM20.dll installed and visible. I also attempted to include a user form and it was installed without a hitch, absolutely no problem. I did send the file to Otto and he can't recreate the problem. The code performs as it should on his machine and completes. I still get the error and the code stops, as Otto has said. Is there anything that anyone can think of that I can do that he and I haven't. If uninstalling and reinstalling Excel would work I could do that also. Thanks Bob Reynolds "Chip Pearson" wrote in message ... Do you know the exact wording of the error message? Is it coming from Excel or from VBA? he doesn't is "Microsoft Forms 2.0 Object Library". He doesn't have anything like that to check. That's a big clue. Does he really not have the Forms library listed in the References list box? Even if it not checked, it should be present in the list. MSFORMS is part of the basic Excel installation, not optional in any way. If "Microsoft Forms 2.0 Object Library" doesn't appear at all in the References list, the basic installation of Excel is bad. The first thing I'd advise to your user is to unregister and re-register Excel. Close Excel (and all other Office programs), go to the Windows Start menu, choose Run and enter RegSvr32 "Excel.exe" /unregserver The repeat the process with RegSvr32 "Excel.exe" /regserver You may have to use the full file path of Excel instead of just "Excel.exe". In the Immediate window of VBA, enter the following and press Enter. ?Application.Path & "\Excel.exe" This will display the full pathname of Excel.exe. Use that filename in place of "Excel.exe" in the RegSvr32 commands. Now try to open the workbook. In VBA, ensure that "Microsoft Forms 2.0 Object Library" appears in the list of references. Try adding a UserForm to the project. Doing so will automatically add a reference to MSFORMS. If this fails, and you (by which I mean your user) get an error message when trying to add a UserForm, try to add the reference to MSFORMS manually. In the References dialog, click "Browse" and navigate to the file C:\WINDOWS\system32\FM20.DLL If you find the file, click OK or Open. If you don't find the file, then you'll probably want to run "Detect And Repair" from the Help menu. If this doesn't fix the problem, you'll likely need to uninstall and reinstall Office. A missing MSFORMS reference item and/or a missing FM20.DLL file indicates that something serious has gone on. -- 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 ... 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 -- Dave Peterson -- Dave Peterson -- Dave Peterson -- Dave Peterson -- Dave Peterson -- Dave Peterson |
#45
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 |
#46
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 |
#47
Posted to microsoft.public.excel.programming
|
|||
|
|||
Code looping through files
Here goes: it needs a few .xls files in the C:\Temp directory and let'er run
Option Explicit Dim c As Long Dim RngB As Range Dim i As Range Dim wb As Workbook Dim CancelA As Boolean Sub ProcessData() Dim wb As Workbook Dim TheFile As String Dim ThePath As String ThePath = "C:\Temp" Application.ScreenUpdating = False ChDir ThePath TheFile = Dir("*.xls") Do While TheFile < "" If TheFile < "Daily Error report MASTER.xls" Then 'MsgBox TheFile Set wb = Workbooks.Open(ThePath & "\" & TheFile) Call AAAProcessData ActiveWorkbook.Save ActiveWorkbook.Saved = True wb.Close End If TheFile = Dir Loop Workbooks.Open Filename:=ThePath & "\" & "Daily Error report MASTER.xls" Application.ScreenUpdating = True End Sub Sub AAAProcessData() CancelA = False Call DelColsSort Call DelRows Call Summarize If CancelA = True Then Exit Sub Call CleanUp End Sub Sub DelColsSort() Range("A:A,B:B,D:D,E:E,G:G,H:H,I:I").Delete [F1].Value = "RC Code" [G1].Value = "Aging" [H1].Value = "Count" [F1:H1].HorizontalAlignment = xlCenter End Sub Sub DelRows() Set RngB = Range("B2", Range("B" & Rows.Count).End(xlUp)) RngB.Offset(, -1).Resize(, 2).Sort Key1:=Range("B2"), Order1:=xlAscending, Header:=xlNo, _ OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _ DataOption1:=xlSortNormal For c = RngB.Count To 1 Step -1 If Left(RngB(c), 6) < "C0B90D" And _ Left(RngB(c), 6) < "C0B90E" And _ Left(RngB(c), 6) < "C0B90F" And _ Left(RngB(c), 6) < "C0B90G" Then RngB(c).EntireRow.Delete End If Next c End Sub Sub Summarize() Dim FirstCell As Range Dim LastCell As Range Dim Dest As Range Call SetupFinal If IsEmpty(Range("B2").Value) Then CancelA = True Exit Sub End If Set FirstCell = [B2] Do Set LastCell = Nothing For c = 1 To 1000 If Left(FirstCell.Offset(c), 6) < Left(FirstCell, 6) Then Set LastCell = FirstCell.Offset(c - 1) Exit For End If Next c Set Dest = Range("F2:F5").Find(What:=Left(FirstCell.Value, 6), LookAt:=xlWhole) Dest.Offset(, 1).Value = Application.Max(Range(FirstCell, LastCell).Offset(, -1)) Dest.Offset(, 2).Value = Range(FirstCell, LastCell).Count Set FirstCell = LastCell.Offset(1) Loop Until IsEmpty(FirstCell.Value) End Sub Sub SetupFinal() [F2].Value = "C0B90D" [F3].Value = "C0B90E" [F4].Value = "C0B90F" [F5].Value = "C0B90G" [F6].Value = "GTotal" For Each i In Range("G2:H5") i.Value = i.Value * 1 Next i End Sub Sub CleanUp() Columns("F:H").Columns.AutoFit [F2:H6].HorizontalAlignment = xlCenter [F6].Value = "GTotal" [G6].Value = Application.Max(Range("G2:G5")) [H6].Value = Application.Sum(Range("H2:H5")) [F6:H6].Font.Bold = True End Sub Thanks "Dave Peterson" wrote in message ... How about posting the exact code that you're using? rjr wrote: Got the runtime error 1004 method open of object workbooks failed with the debug showing Set wb = Workbooks.Open(list(i)) 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 . .. Dave, my apologies for not getting back sooner, kinda forgot where I'm at . Yes I tried the subroutine you provided Option Explicit Sub Testme01() Dim myName As String Dim wb As Workbook myName = "C:\Documents and Settings\Owner\Desktop\KathleenFolder\asf.xls" Set wb = Workbooks.Open(myName) End Sub and it worked fine. I changed the my name to c:\temp which is where we we're now working out of. It seems the problem occurs when the code loops and it won't open the second file as it did the first. I am able to open all the files in that folder individually, but not through the code. I also tried Gary's little routine that worked on 2003 and it stopped at the same location as Otto's code did. Is that a setting that might be creating problems for me inside of 2003? Thanks again Bob Reynolds "Dave Peterson" wrote in message ... First, did you try that little subroutine? Otto posted this code as the basis for the code you're using. 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 Sub This could cause trouble if the logged drive isn't the C: drive. (Changing the folder won't change the drive.) Does adding another line help? Sub AllFolderFiles() Dim wb As Workbook Dim TheFile As String Dim MyPath As String MyPath = "C:\Temp" ChDir MyPath ChDrive myPath '<--- Added TheFile = Dir("*.xls") Do While TheFile < "" Set wb = Workbooks.Open(MyPath & "\" & TheFile) MsgBox wb.FullName wb.Close TheFile = Dir Loop End Sub rjr wrote: Latest Update. Otto and I worked and worked and discovered he is using Excel 2002 Professional and I am using Excel 2003 Professional with Windows XP Media Edition 2005. I can't get it to work on my computer at all, so I thought I'd try my laptop which is new and has Excel 2003 Professional, along with Windows XP Professional also. Then I decided to try my wife's laptop. She is running Windows XP Home and Office 2000 Professional and consequently Excel 2000 Professional. Lo and Behold the code ran correctly and did exactly what Otto swore it would do and did on his computer. Now my question is does anyone know of a reason this should be possible or is there an issue with 2003 that needs to be turned on to make them work.? I really need to make this work on an Excel 2003 program so I'm kinda back where I was..... Any advice would be greatly appreciated and I would also like to thank everyone that helped in gaining resolution with this problem, especially on New Year's Eve. Bob Reynolds "Dave Peterson" wrote in message ... I wanted you to try the separate procedure as a test--not a replacement for the original procedure. And I can't think of any other reasons--and I wouldn't guess that reinstalling Office (or windows!) would help--but I've been wrong lots of times. rjr wrote: "Dave Peterson" wrote in message ... I wouldn't guess so--but that's just a guess. I'm still guessing that it's something simple--like a locked file. *** none of the files are locked or protected. If you create a separate procedure in a test module: *****No because what we're trying to do is go down the directory of .xls files and open, do the procedure and then close -- move to next one and redo the procedure until all files (approx 22) have had the code run on them and it's completed. I will add that if I take this portion (where the error occurs) of the code out of it, the code runs perfectly on each file operated independently but it requires me to run it on each one individually. Option Explicit sub Testme01() dim myName as string dim wb as workbook myName = "C:\Documents and Settings\Owner\Desktop\KathleenFolder\asf.xls" set wb = workbooks.open(myname) End sub === I don't think that this will help... But if you move that asf.xls file to a different location, does the original procedure work ok? I can't imagine what can be happening where you can open this file manually, but have problems when you open it via code. It will open by single code, like I said, but once we try it through the loop he hangs up right there. Otto has it working fine (With the same files) on his computer and I even transferred all my miles and code to another laptop I have running windows xp pro and office 2003 with the same exact results I get. That's why I was thinking it might be a setting on my computer...Again, if it works on other computers that would tend to isolate mine as the cause. Is there any reasons that you can think of..... Lastly do you think removing and reinstalling office would help. I can't see where it would since all features are installed, but I don't know enough to make a really educated guess.... Thanks Bob Reynolds rjr wrote: C:\Documents and Settings\Owner\Desktop\KathleenFolder\asf.xls This is the location of where the file is and the asf.xls is the file that was the first one to be opened. Would a new installation of Office Professional help, do you think??? Bob "Dave Peterson" wrote in message ... I don't have a guess, but I am curious about what the msgbox showed. What was the filename (or path and filename)? rjr wrote: I put Tom's in and the msgbox returned the complete directory that we're using as the directory and the proper file name. It still errored out on the Set wb line... I'm thinking that it has to do with some setting I've got on my computer that's keeping this from running. Otto had run it two different ways and also used my file and his ran perfectly, but I've continued getting the error. Bob "Tom Ogilvy" wrote in message ... Here is another guess - Change the code to: Do While TheFile < "" If TheFile < "Daily Error report MASTER.xls" Then MsgBox ThePath & "\" & TheFile Set wb = Workbooks.Open(ThePath & "\" & TheFile) make sure you actually need to append the "\" and don't actually have a filename result like: C:\\Report1.xls In windows NT type Operating systems, this will still work, but in Windows 9x, it will error. -- Regards, Tom Ogilvy "rjr" wrote in message . .. Run Time Error 1004 Method OPEN of object.workbooks failed. This is in the Microsoft vb alert window. the Error place in the code is at Do While TheFile < "" If TheFile < "Daily Error report MASTER.xls" Then MsgBox TheFile Set wb = Workbooks.Open(ThePath & "\" & TheFile) << error line. I've sent Otto the files while he was working on the code. The code works perfectly for him at his computer. I can open ALL files from file open or double clicking on the file. "Dave Peterson" wrote in message ... What's the error that is displayed? Any chance that Otto has access to that folder and you don't? Can you open the file just via File|Open? rjr wrote: Hi, I've got the problem that Otto is helping me with. I've done a complete detect and repair and as Chip has suggested I've looked in the References List box and I do have Microsoft Forms 2.0 Object Library installed and checked. I also have the FM20.dll installed and visible. I also attempted to include a user form and it was installed without a hitch, absolutely no problem. I did send the file to Otto and he can't recreate the problem. The code performs as it should on his machine and completes. I still get the error and the code stops, as Otto has said. Is there anything that anyone can think of that I can do that he and I haven't. If uninstalling and reinstalling Excel would work I could do that also. Thanks Bob Reynolds "Chip Pearson" wrote in message ... Do you know the exact wording of the error message? Is it coming from Excel or from VBA? he doesn't is "Microsoft Forms 2.0 Object Library". He doesn't have anything like that to check. That's a big clue. Does he really not have the Forms library listed in the References list box? Even if it not checked, it should be present in the list. MSFORMS is part of the basic Excel installation, not optional in any way. If "Microsoft Forms 2.0 Object Library" doesn't appear at all in the References list, the basic installation of Excel is bad. The first thing I'd advise to your user is to unregister and re-register Excel. Close Excel (and all other Office programs), go to the Windows Start menu, choose Run and enter RegSvr32 "Excel.exe" /unregserver The repeat the process with RegSvr32 "Excel.exe" /regserver You may have to use the full file path of Excel instead of just "Excel.exe". In the Immediate window of VBA, enter the following and press Enter. ?Application.Path & "\Excel.exe" This will display the full pathname of Excel.exe. Use that filename in place of "Excel.exe" in the RegSvr32 commands. Now try to open the workbook. In VBA, ensure that "Microsoft Forms 2.0 Object Library" appears in the list of references. Try adding a UserForm to the project. Doing so will automatically add a reference to MSFORMS. If this fails, and you (by which I mean your user) get an error message when trying to add a UserForm, try to add the reference to MSFORMS manually. In the References dialog, click "Browse" and navigate to the file C:\WINDOWS\system32\FM20.DLL If you find the file, click OK or Open. If you don't find the file, then you'll probably want to run "Detect And Repair" from the Help menu. If this doesn't fix the problem, you'll likely need to uninstall and reinstall Office. A missing MSFORMS reference item and/or a missing FM20.DLL file indicates that something serious has gone on. -- 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 ... 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 -- Dave Peterson -- Dave Peterson -- Dave Peterson -- Dave Peterson -- Dave Peterson -- Dave Peterson |
#48
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 |
#49
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 |
#50
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 |
#51
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 |
#52
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 |
#53
Posted to microsoft.public.excel.programming
|
|||
|
|||
Code looping through files
just create a new user account and run it there. but try it in a regular code module instead of the personal.xls first you can delete the user account after you've tested it. -- Gary "rjr" wrote in message .. . 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 |
#54
Posted to microsoft.public.excel.programming
|
|||
|
|||
Code looping through files
None of these files could be in the xl2007 file format? I believe you can
install a compatibility addin that allows xl2003 to open these files, but I suspect that the Workbooks.Open method in VBA would not be able to do it (can't say for sure). Just another guess. That wouldn't be consistent with success in xl2000 or xl2002 which you claimed (on or the other), but maybe that was with different files. -- Regards, Tom Ogilvy "rjr" wrote in message .. . 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 |
#55
Posted to microsoft.public.excel.programming
|
|||
|
|||
Code looping through files
Tom, That appears to be working and doing the loop thing. I ran it twice
completely through.. Now I just need to put it in the code OTTO gave me so all the other things are carried out. Since I'm way outa my league any chance you could take what I sent and replace what needs to be done with this. Then it won't be making errors again. Thanks so much BOB "Tom Ogilvy" wrote in message ... 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 |
#56
Posted to microsoft.public.excel.programming
|
|||
|
|||
Code looping through files
All files were opened in excel while on the mainframe and saved to the locat
harddrive as an .xls file. Don't have any access to 2007 anything.... When the choice came up it was the 97-2002..... format we saved it with. Thanks BOB "Tom Ogilvy" wrote in message ... None of these files could be in the xl2007 file format? I believe you can install a compatibility addin that allows xl2003 to open these files, but I suspect that the Workbooks.Open method in VBA would not be able to do it (can't say for sure). Just another guess. That wouldn't be consistent with success in xl2000 or xl2002 which you claimed (on or the other), but maybe that was with different files. -- Regards, Tom Ogilvy "rjr" wrote in message .. . 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 |
#57
Posted to microsoft.public.excel.programming
|
|||
|
|||
Code looping through files
Option Explicit
Dim c As Long Dim RngB As Range Dim i As Range Dim wb As Workbook Dim CancelA As Boolean Sub ProcessData() Dim wb As Workbook Dim TheFile As String Dim ThePath As String ThePath = "C:\Temp" Application.ScreenUpdating = False ChDir ThePath TheFile = Dir("*.xls") Do While TheFile < "" If lcase(TheFile) < lcase("Daily Error report MASTER.xls") Then 'MsgBox TheFile On Error Resume Next Set wb = Workbooks.Open(ThePath & "\" & TheFile) if err.number = 0 then Call AAAProcessData wb.Close SaveChanges:=True else msgbox "Not processed: " & TheFile err.clear end if On Error goto 0 End If TheFile = Dir Loop Workbooks.Open Filename:=ThePath & "\" _ & "Daily Error report MASTER.xls" Application.ScreenUpdating = True End Sub -- Regards, Tom Ogilvy "rjr" wrote in message .. . Tom, That appears to be working and doing the loop thing. I ran it twice completely through.. Now I just need to put it in the code OTTO gave me so all the other things are carried out. Since I'm way outa my league any chance you could take what I sent and replace what needs to be done with this. Then it won't be making errors again. Thanks so much BOB "Tom Ogilvy" wrote in message ... 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 |
#58
Posted to microsoft.public.excel.programming
|
|||
|
|||
Code looping through files
Tom Thanks so much, I will try tomorrow during the day and let you know
thanks to all Bob "Tom Ogilvy" wrote in message ... Option Explicit Dim c As Long Dim RngB As Range Dim i As Range Dim wb As Workbook Dim CancelA As Boolean Sub ProcessData() Dim wb As Workbook Dim TheFile As String Dim ThePath As String ThePath = "C:\Temp" Application.ScreenUpdating = False ChDir ThePath TheFile = Dir("*.xls") Do While TheFile < "" If lcase(TheFile) < lcase("Daily Error report MASTER.xls") Then 'MsgBox TheFile On Error Resume Next Set wb = Workbooks.Open(ThePath & "\" & TheFile) if err.number = 0 then Call AAAProcessData wb.Close SaveChanges:=True else msgbox "Not processed: " & TheFile err.clear end if On Error goto 0 End If TheFile = Dir Loop Workbooks.Open Filename:=ThePath & "\" _ & "Daily Error report MASTER.xls" Application.ScreenUpdating = True End Sub -- Regards, Tom Ogilvy "rjr" wrote in message .. . Tom, That appears to be working and doing the loop thing. I ran it twice completely through.. Now I just need to put it in the code OTTO gave me so all the other things are carried out. Since I'm way outa my league any chance you could take what I sent and replace what needs to be done with this. Then it won't be making errors again. Thanks so much BOB "Tom Ogilvy" wrote in message ... 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 |
#59
Posted to microsoft.public.excel.programming
|
|||
|
|||
Code looping through files
Now when I run the code I get the message box and every file says
notprocessed and the files haven't changed. I no longer have a error to show what part of the code is the problem. Any suggestions now. Thanks BOB Reynolds "Tom Ogilvy" wrote in message ... Option Explicit Dim c As Long Dim RngB As Range Dim i As Range Dim wb As Workbook Dim CancelA As Boolean Sub ProcessData() Dim wb As Workbook Dim TheFile As String Dim ThePath As String ThePath = "C:\Temp" Application.ScreenUpdating = False ChDir ThePath TheFile = Dir("*.xls") Do While TheFile < "" If lcase(TheFile) < lcase("Daily Error report MASTER.xls") Then 'MsgBox TheFile On Error Resume Next Set wb = Workbooks.Open(ThePath & "\" & TheFile) if err.number = 0 then Call AAAProcessData wb.Close SaveChanges:=True else msgbox "Not processed: " & TheFile err.clear end if On Error goto 0 End If TheFile = Dir Loop Workbooks.Open Filename:=ThePath & "\" _ & "Daily Error report MASTER.xls" Application.ScreenUpdating = True End Sub -- Regards, Tom Ogilvy "rjr" wrote in message .. . Tom, That appears to be working and doing the loop thing. I ran it twice completely through.. Now I just need to put it in the code OTTO gave me so all the other things are carried out. Since I'm way outa my league any chance you could take what I sent and replace what needs to be done with this. Then it won't be making errors again. Thanks so much BOB "Tom Ogilvy" wrote in message ... 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 |
#60
Posted to microsoft.public.excel.programming
|
|||
|
|||
Code looping through files new line
Started new here, the others are getting pretty deep.
OK, Have tried using this with several different issues this morning. Here's the outcome. I used the F8 to step through it and see what was happening and also just ran the code and let her rip. I got several messages regarding the files weren't processed, using the full code. and I got a couple there were processed. then using the test code Tom wanted me to last night that worked, I got the first two didn't work, but the rest did as they were supposed to, opened the file and made the specific changes and closed the fiel. The first two files weren't opening as they should and I then opened them both manually and they opened and looked fine, and then I closed them out. They are the first two in alphabetical order. I then changed the order and was able to get two of the 22 to open. The others seemed to hang on et wb = Workbooks.Open(fl.Path) and then went to the error. I've got to go to work today and will try new files, but it would seem to me that if one or more works properly then something in the file would be the problem. Unfortunately all files were saved the same way and all were exported to excel in csv format, the files were opened and then saved as in xls format. Is there a need to put a slight pause into the code that when it attempts to start opening the file it would pause and make sure the file had enough time to open, or am I asking a stupid thing??? Thanks again Bob Reynolds 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 |
#61
Posted to microsoft.public.excel.programming
|
|||
|
|||
Code looping through files new line
Hello again,
Otto and I have talking this morning and here are some clarifications about what we have discovered. first the source of my files are from a mainframe which saves as a csv file and I open it in MS Excel. Once opened I save it as an Excel .xls file and download it to my computer. All the files that we've been dealing with are of this nature. Otto has asked me to document the downloading process for these files and post them here in case we have a issue with the excel files. I will repost after this evening to see if I can document the process and recreate the issues. If I cant' recreate then it will have to be in the initial files that were saved in excel. If I do recreate the problem I'll return and ask for more advice. I tryly appreciate all of you for sticking by and trying to help me out. Thanks Bob Reynolds Next Otto asked me to clean out the folder and then create new .xls files (blank) and save them in the directory. The names were Book (1) (2) etc... I have six of those files placed in the folder and we discovered there was no problems with the opening and closing of the files. Otto gave me Debra's maco from her werbsite and all seems well at this time. "rjr" wrote in message .. . Started new here, the others are getting pretty deep. OK, Have tried using this with several different issues this morning. Here's the outcome. I used the F8 to step through it and see what was happening and also just ran the code and let her rip. I got several messages regarding the files weren't processed, using the full code. and I got a couple there were processed. then using the test code Tom wanted me to last night that worked, I got the first two didn't work, but the rest did as they were supposed to, opened the file and made the specific changes and closed the fiel. The first two files weren't opening as they should and I then opened them both manually and they opened and looked fine, and then I closed them out. They are the first two in alphabetical order. I then changed the order and was able to get two of the 22 to open. The others seemed to hang on et wb = Workbooks.Open(fl.Path) and then went to the error. I've got to go to work today and will try new files, but it would seem to me that if one or more works properly then something in the file would be the problem. Unfortunately all files were saved the same way and all were exported to excel in csv format, the files were opened and then saved as in xls format. Is there a need to put a slight pause into the code that when it attempts to start opening the file it would pause and make sure the file had enough time to open, or am I asking a stupid thing??? Thanks again Bob Reynolds 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 |
#62
Posted to microsoft.public.excel.programming
|
|||
|
|||
Code looping through files
It ran ok for me.
But I would still add the check for lcase() and the chdrive, too. And since you're opening, changing and saving the files, I would use Tom's suggestion of getting a list of names first, then process the list. rjr wrote: Here goes: it needs a few .xls files in the C:\Temp directory and let'er run Option Explicit Dim c As Long Dim RngB As Range Dim i As Range Dim wb As Workbook Dim CancelA As Boolean Sub ProcessData() Dim wb As Workbook Dim TheFile As String Dim ThePath As String ThePath = "C:\Temp" Application.ScreenUpdating = False ChDir ThePath TheFile = Dir("*.xls") Do While TheFile < "" If TheFile < "Daily Error report MASTER.xls" Then 'MsgBox TheFile Set wb = Workbooks.Open(ThePath & "\" & TheFile) Call AAAProcessData ActiveWorkbook.Save ActiveWorkbook.Saved = True wb.Close End If TheFile = Dir Loop Workbooks.Open Filename:=ThePath & "\" & "Daily Error report MASTER.xls" Application.ScreenUpdating = True End Sub Sub AAAProcessData() CancelA = False Call DelColsSort Call DelRows Call Summarize If CancelA = True Then Exit Sub Call CleanUp End Sub Sub DelColsSort() Range("A:A,B:B,D:D,E:E,G:G,H:H,I:I").Delete [F1].Value = "RC Code" [G1].Value = "Aging" [H1].Value = "Count" [F1:H1].HorizontalAlignment = xlCenter End Sub Sub DelRows() Set RngB = Range("B2", Range("B" & Rows.Count).End(xlUp)) RngB.Offset(, -1).Resize(, 2).Sort Key1:=Range("B2"), Order1:=xlAscending, Header:=xlNo, _ OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _ DataOption1:=xlSortNormal For c = RngB.Count To 1 Step -1 If Left(RngB(c), 6) < "C0B90D" And _ Left(RngB(c), 6) < "C0B90E" And _ Left(RngB(c), 6) < "C0B90F" And _ Left(RngB(c), 6) < "C0B90G" Then RngB(c).EntireRow.Delete End If Next c End Sub Sub Summarize() Dim FirstCell As Range Dim LastCell As Range Dim Dest As Range Call SetupFinal If IsEmpty(Range("B2").Value) Then CancelA = True Exit Sub End If Set FirstCell = [B2] Do Set LastCell = Nothing For c = 1 To 1000 If Left(FirstCell.Offset(c), 6) < Left(FirstCell, 6) Then Set LastCell = FirstCell.Offset(c - 1) Exit For End If Next c Set Dest = Range("F2:F5").Find(What:=Left(FirstCell.Value, 6), LookAt:=xlWhole) Dest.Offset(, 1).Value = Application.Max(Range(FirstCell, LastCell).Offset(, -1)) Dest.Offset(, 2).Value = Range(FirstCell, LastCell).Count Set FirstCell = LastCell.Offset(1) Loop Until IsEmpty(FirstCell.Value) End Sub Sub SetupFinal() [F2].Value = "C0B90D" [F3].Value = "C0B90E" [F4].Value = "C0B90F" [F5].Value = "C0B90G" [F6].Value = "GTotal" For Each i In Range("G2:H5") i.Value = i.Value * 1 Next i End Sub Sub CleanUp() Columns("F:H").Columns.AutoFit [F2:H6].HorizontalAlignment = xlCenter [F6].Value = "GTotal" [G6].Value = Application.Max(Range("G2:G5")) [H6].Value = Application.Sum(Range("H2:H5")) [F6:H6].Font.Bold = True End Sub Thanks "Dave Peterson" wrote in message ... How about posting the exact code that you're using? rjr wrote: Got the runtime error 1004 method open of object workbooks failed with the debug showing Set wb = Workbooks.Open(list(i)) 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 . .. Dave, my apologies for not getting back sooner, kinda forgot where I'm at . Yes I tried the subroutine you provided Option Explicit Sub Testme01() Dim myName As String Dim wb As Workbook myName = "C:\Documents and Settings\Owner\Desktop\KathleenFolder\asf.xls" Set wb = Workbooks.Open(myName) End Sub and it worked fine. I changed the my name to c:\temp which is where we we're now working out of. It seems the problem occurs when the code loops and it won't open the second file as it did the first. I am able to open all the files in that folder individually, but not through the code. I also tried Gary's little routine that worked on 2003 and it stopped at the same location as Otto's code did. Is that a setting that might be creating problems for me inside of 2003? Thanks again Bob Reynolds "Dave Peterson" wrote in message ... First, did you try that little subroutine? Otto posted this code as the basis for the code you're using. 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 Sub This could cause trouble if the logged drive isn't the C: drive. (Changing the folder won't change the drive.) Does adding another line help? Sub AllFolderFiles() Dim wb As Workbook Dim TheFile As String Dim MyPath As String MyPath = "C:\Temp" ChDir MyPath ChDrive myPath '<--- Added TheFile = Dir("*.xls") Do While TheFile < "" Set wb = Workbooks.Open(MyPath & "\" & TheFile) MsgBox wb.FullName wb.Close TheFile = Dir Loop End Sub rjr wrote: Latest Update. Otto and I worked and worked and discovered he is using Excel 2002 Professional and I am using Excel 2003 Professional with Windows XP Media Edition 2005. I can't get it to work on my computer at all, so I thought I'd try my laptop which is new and has Excel 2003 Professional, along with Windows XP Professional also. Then I decided to try my wife's laptop. She is running Windows XP Home and Office 2000 Professional and consequently Excel 2000 Professional. Lo and Behold the code ran correctly and did exactly what Otto swore it would do and did on his computer. Now my question is does anyone know of a reason this should be possible or is there an issue with 2003 that needs to be turned on to make them work.? I really need to make this work on an Excel 2003 program so I'm kinda back where I was..... Any advice would be greatly appreciated and I would also like to thank everyone that helped in gaining resolution with this problem, especially on New Year's Eve. Bob Reynolds "Dave Peterson" wrote in message ... I wanted you to try the separate procedure as a test--not a replacement for the original procedure. And I can't think of any other reasons--and I wouldn't guess that reinstalling Office (or windows!) would help--but I've been wrong lots of times. rjr wrote: "Dave Peterson" wrote in message ... I wouldn't guess so--but that's just a guess. I'm still guessing that it's something simple--like a locked file. *** none of the files are locked or protected. If you create a separate procedure in a test module: *****No because what we're trying to do is go down the directory of .xls files and open, do the procedure and then close -- move to next one and redo the procedure until all files (approx 22) have had the code run on them and it's completed. I will add that if I take this portion (where the error occurs) of the code out of it, the code runs perfectly on each file operated independently but it requires me to run it on each one individually. Option Explicit sub Testme01() dim myName as string dim wb as workbook myName = "C:\Documents and Settings\Owner\Desktop\KathleenFolder\asf.xls" set wb = workbooks.open(myname) End sub === I don't think that this will help... But if you move that asf.xls file to a different location, does the original procedure work ok? I can't imagine what can be happening where you can open this file manually, but have problems when you open it via code. It will open by single code, like I said, but once we try it through the loop he hangs up right there. Otto has it working fine (With the same files) on his computer and I even transferred all my miles and code to another laptop I have running windows xp pro and office 2003 with the same exact results I get. That's why I was thinking it might be a setting on my computer...Again, if it works on other computers that would tend to isolate mine as the cause. Is there any reasons that you can think of..... Lastly do you think removing and reinstalling office would help. I can't see where it would since all features are installed, but I don't know enough to make a really educated guess.... Thanks Bob Reynolds rjr wrote: C:\Documents and Settings\Owner\Desktop\KathleenFolder\asf.xls This is the location of where the file is and the asf.xls is the file that was the first one to be opened. Would a new installation of Office Professional help, do you think??? Bob "Dave Peterson" wrote in message ... I don't have a guess, but I am curious about what the msgbox showed. What was the filename (or path and filename)? rjr wrote: I put Tom's in and the msgbox returned the complete directory that we're using as the directory and the proper file name. It still errored out on the Set wb line... I'm thinking that it has to do with some setting I've got on my computer that's keeping this from running. Otto had run it two different ways and also used my file and his ran perfectly, but I've continued getting the error. Bob "Tom Ogilvy" wrote in message ... Here is another guess - Change the code to: Do While TheFile < "" If TheFile < "Daily Error report MASTER.xls" Then MsgBox ThePath & "\" & TheFile Set wb = Workbooks.Open(ThePath & "\" & TheFile) make sure you actually need to append the "\" and don't actually have a filename result like: C:\\Report1.xls In windows NT type Operating systems, this will still work, but in Windows 9x, it will error. -- Regards, Tom Ogilvy "rjr" wrote in message . .. Run Time Error 1004 Method OPEN of object.workbooks failed. This is in the Microsoft vb alert window. the Error place in the code is at Do While TheFile < "" If TheFile < "Daily Error report MASTER.xls" Then MsgBox TheFile Set wb = Workbooks.Open(ThePath & "\" & TheFile) << error line. I've sent Otto the files while he was working on the code. The code works perfectly for him at his computer. I can open ALL files from file open or double clicking on the file. "Dave Peterson" wrote in message ... What's the error that is displayed? Any chance that Otto has access to that folder and you don't? Can you open the file just via File|Open? rjr wrote: Hi, I've got the problem that Otto is helping me with. I've done a complete detect and repair and as Chip has suggested I've looked in the References List box and I do have Microsoft Forms 2.0 Object Library installed and checked. I also have the FM20.dll installed and visible. I also attempted to include a user form and it was installed without a hitch, absolutely no problem. I did send the file to Otto and he can't recreate the problem. The code performs as it should on his machine and completes. I still get the error and the code stops, as Otto has said. Is there anything that anyone can think of that I can do that he and I haven't. If uninstalling and reinstalling Excel would work I could do that also. Thanks Bob Reynolds "Chip Pearson" wrote in message ... Do you know the exact wording of the error message? Is it coming from Excel or from VBA? he doesn't is "Microsoft Forms 2.0 Object Library". He doesn't have anything like that to check. That's a big clue. Does he really not have the Forms library listed in the References list box? Even if it not checked, it should be present in the list. MSFORMS is part of the basic Excel installation, not optional in any way. If "Microsoft Forms 2.0 Object Library" doesn't appear at all in the References list, the basic installation of Excel is bad. The first thing I'd advise to your user is to unregister and re-register Excel. Close Excel (and all other Office programs), go to the Windows Start menu, choose Run and enter RegSvr32 "Excel.exe" /unregserver The repeat the process with RegSvr32 "Excel.exe" /regserver You may have to use the full file path of Excel instead of just "Excel.exe". In the Immediate window of VBA, enter the following and press Enter. ?Application.Path & "\Excel.exe" This will display the full pathname of Excel.exe. Use that filename in place of "Excel.exe" in the RegSvr32 commands. Now try to open the workbook. In VBA, ensure that "Microsoft Forms 2.0 Object Library" appears in the list of references. Try adding a UserForm to the project. Doing so will automatically add a reference to MSFORMS. If this fails, and you (by which I mean your user) get an error message when trying to add a UserForm, try to add the reference to MSFORMS manually. In the References dialog, click "Browse" and navigate to the file C:\WINDOWS\system32\FM20.DLL If you find the file, click OK or Open. If you don't find the file, then you'll probably want to run "Detect And Repair" from the Help menu. If this doesn't fix the problem, you'll likely need to uninstall and reinstall Office. A missing MSFORMS reference item and/or a missing FM20.DLL file indicates that something serious has gone on. -- 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 ... 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 -- Dave Peterson -- Dave Peterson -- Dave Peterson -- Dave Peterson -- Dave Peterson -- Dave Peterson -- Dave Peterson |
#63
Posted to microsoft.public.excel.programming
|
|||
|
|||
Code looping through files new line
Bob neglected to mention one thing. This morning I had him clear out his
folder of all the .csv - .xls files. Then I had him create new blank Excel files and save them into that folder. About 4-6 files. Then he ran the code from Debra's site. It ran perfectly without a hitch. That's why I asked him to document the procedure that was used to generate his original files (the troublesome files) and post it here for all to peruse. I myself have zero experience with importing non-Excel files into Excel. Otto "rjr" wrote in message . .. Hello again, Otto and I have talking this morning and here are some clarifications about what we have discovered. first the source of my files are from a mainframe which saves as a csv file and I open it in MS Excel. Once opened I save it as an Excel .xls file and download it to my computer. All the files that we've been dealing with are of this nature. Otto has asked me to document the downloading process for these files and post them here in case we have a issue with the excel files. I will repost after this evening to see if I can document the process and recreate the issues. If I cant' recreate then it will have to be in the initial files that were saved in excel. If I do recreate the problem I'll return and ask for more advice. I tryly appreciate all of you for sticking by and trying to help me out. Thanks Bob Reynolds Next Otto asked me to clean out the folder and then create new .xls files (blank) and save them in the directory. The names were Book (1) (2) etc... I have six of those files placed in the folder and we discovered there was no problems with the opening and closing of the files. Otto gave me Debra's maco from her werbsite and all seems well at this time. "rjr" wrote in message .. . Started new here, the others are getting pretty deep. OK, Have tried using this with several different issues this morning. Here's the outcome. I used the F8 to step through it and see what was happening and also just ran the code and let her rip. I got several messages regarding the files weren't processed, using the full code. and I got a couple there were processed. then using the test code Tom wanted me to last night that worked, I got the first two didn't work, but the rest did as they were supposed to, opened the file and made the specific changes and closed the fiel. The first two files weren't opening as they should and I then opened them both manually and they opened and looked fine, and then I closed them out. They are the first two in alphabetical order. I then changed the order and was able to get two of the 22 to open. The others seemed to hang on et wb = Workbooks.Open(fl.Path) and then went to the error. I've got to go to work today and will try new files, but it would seem to me that if one or more works properly then something in the file would be the problem. Unfortunately all files were saved the same way and all were exported to excel in csv format, the files were opened and then saved as in xls format. Is there a need to put a slight pause into the code that when it attempts to start opening the file it would pause and make sure the file had enough time to open, or am I asking a stupid thing??? Thanks again Bob Reynolds 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 |
#64
Posted to microsoft.public.excel.programming
|
|||
|
|||
Code looping through files new line
Thanks Tom, I'm going to sit with my wife tonight and see what options we
have. If there is the ability to direct save as a csv I'll give it a try. If I recall correctly it can be done, but I must check to make sure. This evening (late) I'm going to have a sample of everything I can get and then do some troubleshooting and fact finding. No there were no manual manipulations she just saved them in .xls format... With everyone's help it seems that we've narrowed it down considerably, but I won't give up yet. Thanks so much to all Bob Reynolds "Tom Ogilvy" wrote in message ... Why not let your excel code open the original CSV files, or did you do some manual manipulation to after you opened them manually. You would point the code at a directory with the CSV files and change the code to look for *.csv rather than *.xls -- Regards, Tom Ogilvy "rjr" wrote: Hello again, Otto and I have talking this morning and here are some clarifications about what we have discovered. first the source of my files are from a mainframe which saves as a csv file and I open it in MS Excel. Once opened I save it as an Excel .xls file and download it to my computer. All the files that we've been dealing with are of this nature. Otto has asked me to document the downloading process for these files and post them here in case we have a issue with the excel files. I will repost after this evening to see if I can document the process and recreate the issues. If I cant' recreate then it will have to be in the initial files that were saved in excel. If I do recreate the problem I'll return and ask for more advice. I tryly appreciate all of you for sticking by and trying to help me out. Thanks Bob Reynolds Next Otto asked me to clean out the folder and then create new .xls files (blank) and save them in the directory. The names were Book (1) (2) etc... I have six of those files placed in the folder and we discovered there was no problems with the opening and closing of the files. Otto gave me Debra's maco from her werbsite and all seems well at this time. "rjr" wrote in message .. . Started new here, the others are getting pretty deep. OK, Have tried using this with several different issues this morning. Here's the outcome. I used the F8 to step through it and see what was happening and also just ran the code and let her rip. I got several messages regarding the files weren't processed, using the full code. and I got a couple there were processed. then using the test code Tom wanted me to last night that worked, I got the first two didn't work, but the rest did as they were supposed to, opened the file and made the specific changes and closed the fiel. The first two files weren't opening as they should and I then opened them both manually and they opened and looked fine, and then I closed them out. They are the first two in alphabetical order. I then changed the order and was able to get two of the 22 to open. The others seemed to hang on et wb = Workbooks.Open(fl.Path) and then went to the error. I've got to go to work today and will try new files, but it would seem to me that if one or more works properly then something in the file would be the problem. Unfortunately all files were saved the same way and all were exported to excel in csv format, the files were opened and then saved as in xls format. Is there a need to put a slight pause into the code that when it attempts to start opening the file it would pause and make sure the file had enough time to open, or am I asking a stupid thing??? Thanks again Bob Reynolds 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 |
#65
Posted to microsoft.public.excel.programming
|
|||
|
|||
Code looping through files new line
Tom, here is the answer to your question. When I opened the file it said it
was in csv format and questioned if I wanted to save it in that format. I selected yes, gave it a name and saved it. I got a warning that possibly some features of the file might not be supported in csv and did I want to change the format. I said no and saved it with the csv designation. Now when I double click it to open I get this warning, and it is labeled ***.csv. Excel has detected that "***.csv" is a SYLK file, but cannot load it. Either the file has errors or it is not a SYLK file format. Click OK to try to open the file in a different format. I click OK and the file opens up, with the ***.csv in the header bar. When I go to save it, I get a warning that it might contain features that are not compatible with CSV. DO you want to keep the workbook in this format?............... I could only download 3 tonight and ran them with the only issue being a msgbox appearing and saying that it is a SYLK file and can't be opened and requireing me to click on yes or no to continue. When I click on yes the code continues to run with no errors and performs as expected. I was concerned about the warnings but they don't appear to be a problem. Are you familiar with any of these issues or anyting I should look out for. I will download more tomorrow and try all of the out again. Thanks BOB Reynolds "Tom Ogilvy" wrote in message ... You said: from a mainframe which saves as a csv file so there is no need to do anything but have your code open the existing CSV file as produced by the Mainframe. then in your code where you close the file, you would do wb.SaveAs Replace(wb.fullname,".csv",".xls"), xlWorkbook.Normal So I don't know what you mean when you say If there is the ability to direct save as a csv -- Regards, Tom Ogilvy "rjr" wrote: Thanks Tom, I'm going to sit with my wife tonight and see what options we have. If there is the ability to direct save as a csv I'll give it a try. If I recall correctly it can be done, but I must check to make sure. This evening (late) I'm going to have a sample of everything I can get and then do some troubleshooting and fact finding. No there were no manual manipulations she just saved them in .xls format... With everyone's help it seems that we've narrowed it down considerably, but I won't give up yet. Thanks so much to all Bob Reynolds "Tom Ogilvy" wrote in message ... Why not let your excel code open the original CSV files, or did you do some manual manipulation to after you opened them manually. You would point the code at a directory with the CSV files and change the code to look for *.csv rather than *.xls -- Regards, Tom Ogilvy "rjr" wrote: Hello again, Otto and I have talking this morning and here are some clarifications about what we have discovered. first the source of my files are from a mainframe which saves as a csv file and I open it in MS Excel. Once opened I save it as an Excel .xls file and download it to my computer. All the files that we've been dealing with are of this nature. Otto has asked me to document the downloading process for these files and post them here in case we have a issue with the excel files. I will repost after this evening to see if I can document the process and recreate the issues. If I cant' recreate then it will have to be in the initial files that were saved in excel. If I do recreate the problem I'll return and ask for more advice. I tryly appreciate all of you for sticking by and trying to help me out. Thanks Bob Reynolds Next Otto asked me to clean out the folder and then create new .xls files (blank) and save them in the directory. The names were Book (1) (2) etc... I have six of those files placed in the folder and we discovered there was no problems with the opening and closing of the files. Otto gave me Debra's maco from her werbsite and all seems well at this time. "rjr" wrote in message .. . Started new here, the others are getting pretty deep. OK, Have tried using this with several different issues this morning. Here's the outcome. I used the F8 to step through it and see what was happening and also just ran the code and let her rip. I got several messages regarding the files weren't processed, using the full code. and I got a couple there were processed. then using the test code Tom wanted me to last night that worked, I got the first two didn't work, but the rest did as they were supposed to, opened the file and made the specific changes and closed the fiel. The first two files weren't opening as they should and I then opened them both manually and they opened and looked fine, and then I closed them out. They are the first two in alphabetical order. I then changed the order and was able to get two of the 22 to open. The others seemed to hang on et wb = Workbooks.Open(fl.Path) and then went to the error. I've got to go to work today and will try new files, but it would seem to me that if one or more works properly then something in the file would be the problem. Unfortunately all files were saved the same way and all were exported to excel in csv format, the files were opened and then saved as in xls format. Is there a need to put a slight pause into the code that when it attempts to start opening the file it would pause and make sure the file had enough time to open, or am I asking a stupid thing??? Thanks again Bob Reynolds 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 |
#66
Posted to microsoft.public.excel.programming
|
|||
|
|||
Code looping through files new line
That was the alert I was getting about the SYLK. Thank you for that code
I'll certainly add it and report back. Bob "Tom Ogilvy" wrote in message ... I say you should get no warning on opening if the file is a CSV file unless the first two characters in the file are "ID". http://support.microsoft.com/kb/323626 "SYLK: File format is not valid" error message when you open file If your file is like that stated in the article, then you can ovecome this prompt (it won't appear) in your code with Application.DisplayAlerts = False '<== added wb = Workbooks.Open(sPath & "\" & filename) Application.DisplayAlerts = True '<=== added (I don't remember you exact line of code, so this is representative). -- Regards, Tom Ogilvy "rjr" wrote: Tom, here is the answer to your question. When I opened the file it said it was in csv format and questioned if I wanted to save it in that format. I selected yes, gave it a name and saved it. I got a warning that possibly some features of the file might not be supported in csv and did I want to change the format. I said no and saved it with the csv designation. Now when I double click it to open I get this warning, and it is labeled ***.csv. Excel has detected that "***.csv" is a SYLK file, but cannot load it. Either the file has errors or it is not a SYLK file format. Click OK to try to open the file in a different format. I click OK and the file opens up, with the ***.csv in the header bar. When I go to save it, I get a warning that it might contain features that are not compatible with CSV. DO you want to keep the workbook in this format?............... I could only download 3 tonight and ran them with the only issue being a msgbox appearing and saying that it is a SYLK file and can't be opened and requireing me to click on yes or no to continue. When I click on yes the code continues to run with no errors and performs as expected. I was concerned about the warnings but they don't appear to be a problem. Are you familiar with any of these issues or anyting I should look out for. I will download more tomorrow and try all of the out again. Thanks BOB Reynolds "Tom Ogilvy" wrote in message ... You said: from a mainframe which saves as a csv file so there is no need to do anything but have your code open the existing CSV file as produced by the Mainframe. then in your code where you close the file, you would do wb.SaveAs Replace(wb.fullname,".csv",".xls"), xlWorkbook.Normal So I don't know what you mean when you say If there is the ability to direct save as a csv -- Regards, Tom Ogilvy "rjr" wrote: Thanks Tom, I'm going to sit with my wife tonight and see what options we have. If there is the ability to direct save as a csv I'll give it a try. If I recall correctly it can be done, but I must check to make sure. This evening (late) I'm going to have a sample of everything I can get and then do some troubleshooting and fact finding. No there were no manual manipulations she just saved them in .xls format... With everyone's help it seems that we've narrowed it down considerably, but I won't give up yet. Thanks so much to all Bob Reynolds "Tom Ogilvy" wrote in message ... Why not let your excel code open the original CSV files, or did you do some manual manipulation to after you opened them manually. You would point the code at a directory with the CSV files and change the code to look for *.csv rather than *.xls -- Regards, Tom Ogilvy "rjr" wrote: Hello again, Otto and I have talking this morning and here are some clarifications about what we have discovered. first the source of my files are from a mainframe which saves as a csv file and I open it in MS Excel. Once opened I save it as an Excel .xls file and download it to my computer. All the files that we've been dealing with are of this nature. Otto has asked me to document the downloading process for these files and post them here in case we have a issue with the excel files. I will repost after this evening to see if I can document the process and recreate the issues. If I cant' recreate then it will have to be in the initial files that were saved in excel. If I do recreate the problem I'll return and ask for more advice. I tryly appreciate all of you for sticking by and trying to help me out. Thanks Bob Reynolds Next Otto asked me to clean out the folder and then create new .xls files (blank) and save them in the directory. The names were Book (1) (2) etc... I have six of those files placed in the folder and we discovered there was no problems with the opening and closing of the files. Otto gave me Debra's maco from her werbsite and all seems well at this time. "rjr" wrote in message .. . Started new here, the others are getting pretty deep. OK, Have tried using this with several different issues this morning. Here's the outcome. I used the F8 to step through it and see what was happening and also just ran the code and let her rip. I got several messages regarding the files weren't processed, using the full code. and I got a couple there were processed. then using the test code Tom wanted me to last night that worked, I got the first two didn't work, but the rest did as they were supposed to, opened the file and made the specific changes and closed the fiel. The first two files weren't opening as they should and I then opened them both manually and they opened and looked fine, and then I closed them out. They are the first two in alphabetical order. I then changed the order and was able to get two of the 22 to open. The others seemed to hang on et wb = Workbooks.Open(fl.Path) and then went to the error. I've got to go to work today and will try new files, but it would seem to me that if one or more works properly then something in the file would be the problem. Unfortunately all files were saved the same way and all were exported to excel in csv format, the files were opened and then saved as in xls format. Is there a need to put a slight pause into the code that when it attempts to start opening the file it would pause and make sure the file had enough time to open, or am I asking a stupid thing??? Thanks again Bob Reynolds 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 |
#67
Posted to microsoft.public.excel.programming
|
|||
|
|||
Code looping through files new line
Tom,
Thank you for that bit of code. It worked fine and I was quite perplexed on how to make the apostraphe appear. Otto is helping me and we're using the snippet you provided here and once again Thanks and if I need to bring up other questions, I'll post in a new question cause this is hard to follow. My Thanks to all, Bob Reynolds "Tom Ogilvy" wrote in message ... I say you should get no warning on opening if the file is a CSV file unless the first two characters in the file are "ID". http://support.microsoft.com/kb/323626 "SYLK: File format is not valid" error message when you open file If your file is like that stated in the article, then you can ovecome this prompt (it won't appear) in your code with Application.DisplayAlerts = False '<== added wb = Workbooks.Open(sPath & "\" & filename) Application.DisplayAlerts = True '<=== added (I don't remember you exact line of code, so this is representative). -- Regards, Tom Ogilvy "rjr" wrote: Tom, here is the answer to your question. When I opened the file it said it was in csv format and questioned if I wanted to save it in that format. I selected yes, gave it a name and saved it. I got a warning that possibly some features of the file might not be supported in csv and did I want to change the format. I said no and saved it with the csv designation. Now when I double click it to open I get this warning, and it is labeled ***.csv. Excel has detected that "***.csv" is a SYLK file, but cannot load it. Either the file has errors or it is not a SYLK file format. Click OK to try to open the file in a different format. I click OK and the file opens up, with the ***.csv in the header bar. When I go to save it, I get a warning that it might contain features that are not compatible with CSV. DO you want to keep the workbook in this format?............... I could only download 3 tonight and ran them with the only issue being a msgbox appearing and saying that it is a SYLK file and can't be opened and requireing me to click on yes or no to continue. When I click on yes the code continues to run with no errors and performs as expected. I was concerned about the warnings but they don't appear to be a problem. Are you familiar with any of these issues or anyting I should look out for. I will download more tomorrow and try all of the out again. Thanks BOB Reynolds "Tom Ogilvy" wrote in message ... You said: from a mainframe which saves as a csv file so there is no need to do anything but have your code open the existing CSV file as produced by the Mainframe. then in your code where you close the file, you would do wb.SaveAs Replace(wb.fullname,".csv",".xls"), xlWorkbook.Normal So I don't know what you mean when you say If there is the ability to direct save as a csv -- Regards, Tom Ogilvy "rjr" wrote: Thanks Tom, I'm going to sit with my wife tonight and see what options we have. If there is the ability to direct save as a csv I'll give it a try. If I recall correctly it can be done, but I must check to make sure. This evening (late) I'm going to have a sample of everything I can get and then do some troubleshooting and fact finding. No there were no manual manipulations she just saved them in .xls format... With everyone's help it seems that we've narrowed it down considerably, but I won't give up yet. Thanks so much to all Bob Reynolds "Tom Ogilvy" wrote in message ... Why not let your excel code open the original CSV files, or did you do some manual manipulation to after you opened them manually. You would point the code at a directory with the CSV files and change the code to look for *.csv rather than *.xls -- Regards, Tom Ogilvy "rjr" wrote: Hello again, Otto and I have talking this morning and here are some clarifications about what we have discovered. first the source of my files are from a mainframe which saves as a csv file and I open it in MS Excel. Once opened I save it as an Excel .xls file and download it to my computer. All the files that we've been dealing with are of this nature. Otto has asked me to document the downloading process for these files and post them here in case we have a issue with the excel files. I will repost after this evening to see if I can document the process and recreate the issues. If I cant' recreate then it will have to be in the initial files that were saved in excel. If I do recreate the problem I'll return and ask for more advice. I tryly appreciate all of you for sticking by and trying to help me out. Thanks Bob Reynolds Next Otto asked me to clean out the folder and then create new .xls files (blank) and save them in the directory. The names were Book (1) (2) etc... I have six of those files placed in the folder and we discovered there was no problems with the opening and closing of the files. Otto gave me Debra's maco from her werbsite and all seems well at this time. "rjr" wrote in message .. . Started new here, the others are getting pretty deep. OK, Have tried using this with several different issues this morning. Here's the outcome. I used the F8 to step through it and see what was happening and also just ran the code and let her rip. I got several messages regarding the files weren't processed, using the full code. and I got a couple there were processed. then using the test code Tom wanted me to last night that worked, I got the first two didn't work, but the rest did as they were supposed to, opened the file and made the specific changes and closed the fiel. The first two files weren't opening as they should and I then opened them both manually and they opened and looked fine, and then I closed them out. They are the first two in alphabetical order. I then changed the order and was able to get two of the 22 to open. The others seemed to hang on et wb = Workbooks.Open(fl.Path) and then went to the error. I've got to go to work today and will try new files, but it would seem to me that if one or more works properly then something in the file would be the problem. Unfortunately all files were saved the same way and all were exported to excel in csv format, the files were opened and then saved as in xls format. Is there a need to put a slight pause into the code that when it attempts to start opening the file it would pause and make sure the file had enough time to open, or am I asking a stupid thing??? Thanks again Bob Reynolds 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 |
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 |