Importing Modules From Other Workbooks

[Jim Jackson]

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

 

[Gary Brown]

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
(e-mail address removed)
If this post was helpful to you, please select ''YES'' at the bottom of the
post.

 

[Jim Jackson]

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.