Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Code looping through files
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Code looping through files
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Code looping through files
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Code looping through files
I wouldn't guess so--but that's just a guess.
I'm still guessing that it's something simple--like a locked file. If you create a separate procedure in a test module: 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. 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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Code looping through files
"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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Code looping through files
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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Code looping through files
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 |
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 |