Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Importing Modules From Other Workbooks
Here is the code I am using to import modules from other workbooks.
Everything works fine until the opened workbook is closed and the code gets to "w = Dir()". Then the "invalid Procedure Call or Argument" error message comes up. I have tried several adjustments but the most success has been for the code to Loop and re-open the previous workbook. I know it's something simple but cannot put my finger on it. Could my "Dim" statements need some change/addition? Thanks for any help you can give. Sub CopyAllModules() Dim VBComp As VBIDE.VBComponent Dim FName As String Dim w As String w = Dir("H:\*.xls") Do While w < "" Workbooks.Open (w) With Workbooks(w) FName = .Path & "\code.txt" If Dir(FName) < "" Then Kill FName End If For Each VBComp In .VBProject.VBComponents If VBComp.Type < vbext_ct_Document Then VBComp.Export FName ThisWorkbook.VBProject.VBComponents.Import FName Kill FName End If Next VBComp End With Workbooks(w).Close w = Dir() ' Error Message "Invalid Procedure Call or Argument" Loop End Sub -- Best wishes, Jim |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Importing Modules From Other Workbooks
Gary,
Thanks for the response. That didn't work for me either. I did find one solution, though. I split the macro into two separate ones. The first opens the workbooks and lists them on a worksheet in the home workbook. It then passes to the second which imports the modules as listed and closes the workbooks. It is strange but the w = Dir() works fine in the standalone "Open Workbooks" macro. Something in the Import Modules" macro is interfering and I cannot figure what. At least I did find an alternate solution. -- Best wishes, Jim "Gary Brown" wrote: I think the state of Excel at the point of... w = Dir() is either... the active workbook has not been saved yet or there is no active workbook Change w = Dir() to w = "" -- HTH, Gary Brown If this post was helpful to you, please select ''YES'' at the bottom of the post. "Jim Jackson" wrote: Here is the code I am using to import modules from other workbooks. Everything works fine until the opened workbook is closed and the code gets to "w = Dir()". Then the "invalid Procedure Call or Argument" error message comes up. I have tried several adjustments but the most success has been for the code to Loop and re-open the previous workbook. I know it's something simple but cannot put my finger on it. Could my "Dim" statements need some change/addition? Thanks for any help you can give. Sub CopyAllModules() Dim VBComp As VBIDE.VBComponent Dim FName As String Dim w As String w = Dir("H:\*.xls") Do While w < "" Workbooks.Open (w) With Workbooks(w) FName = .Path & "\code.txt" If Dir(FName) < "" Then Kill FName End If For Each VBComp In .VBProject.VBComponents If VBComp.Type < vbext_ct_Document Then VBComp.Export FName ThisWorkbook.VBProject.VBComponents.Import FName Kill FName End If Next VBComp End With Workbooks(w).Close w = Dir() ' Error Message "Invalid Procedure Call or Argument" Loop End Sub -- Best wishes, Jim |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Importing modules from a folder | Excel Discussion (Misc queries) | |||
Newbie question, XL2003, VBA deleting/adding modules in multiple workbooks | Excel Programming | |||
Newbie question: Importing modules (.bas) at startup? | Excel Programming | |||
Updating VBA Modules and Forms in User Workbooks | Excel Programming | |||
using forms and modules in multiple workbooks | Excel Programming |