Invalid Procedure Call
 

I have the following code I am trying to execute:

At w = Dir() near the end of the procedure, I get the "Invalid Procedure
Call or Argument" error message. I thought I knew what to do about this but
nothing works.

Any ideas welcomed.

Sub CopyAll()
Dim VBComp As VBIDE.VBComponent
Dim FName As String
Dim w As String
w = Dir("C:\Users\Jim\Documents\MsExcel\*.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
--
Thanks,

Pops Jackson

Invalid Procedure Call
 

Use

w =Dir

no brackets

--
__________________________________
HTH

Bob

"Pops Jackson" wrote in message
...
I have the following code I am trying to execute:

At w = Dir() near the end of the procedure, I get the "Invalid Procedure
Call or Argument" error message. I thought I knew what to do about this
but
nothing works.

Any ideas welcomed.

Sub CopyAll()
Dim VBComp As VBIDE.VBComponent
Dim FName As String
Dim w As String
w = Dir("C:\Users\Jim\Documents\MsExcel\*.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
--
Thanks,

Pops Jackson

Invalid Procedure Call
 

Thanks, Bob. I tried that but still got the error message.
--
Pops Jackson


"Bob Phillips" wrote:

Use

w =Dir

no brackets

--
__________________________________
HTH

Bob

"Pops Jackson" wrote in message
...
I have the following code I am trying to execute:

At w = Dir() near the end of the procedure, I get the "Invalid Procedure
Call or Argument" error message. I thought I knew what to do about this
but
nothing works.

Any ideas welcomed.

Sub CopyAll()
Dim VBComp As VBIDE.VBComponent
Dim FName As String
Dim w As String
w = Dir("C:\Users\Jim\Documents\MsExcel\*.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
--
Thanks,

Pops Jackson

Invalid Procedure Call
 

I think it's because you're stacking calls to Dir().

Option Explicit
Sub CopyAll()
Dim VBComp As VBIDE.VBComponent
Dim FName As String
Dim w As String
Dim myFolder As String

myFolder = "C:\Users\Jim\Documents\MsExcel\"

w = Dir(myFolder & "*.xls")

Do While w < ""
Workbooks.Open myFolder & w 'changed to include path
With Workbooks(w)
FName = .Path & "\code.txt"

'just delete it if it's there
'ignore any error if it's not
On Error Resume Next
Kill FName
On Error GoTo 0

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 Savechanges:=true 'don't you want to save it???
w = Dir()
Loop
End Sub


Pops Jackson wrote:

I have the following code I am trying to execute:

At w = Dir() near the end of the procedure, I get the "Invalid Procedure
Call or Argument" error message. I thought I knew what to do about this but
nothing works.

Any ideas welcomed.

Sub CopyAll()
Dim VBComp As VBIDE.VBComponent
Dim FName As String
Dim w As String
w = Dir("C:\Users\Jim\Documents\MsExcel\*.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
--
Thanks,

Pops Jackson

--

Dave Peterson

Invalid Procedure Call
 

Many thanks to both of you for your responses and help. I do indeed wish to
save the file and added the code as suggested as well as the "myfolder"
lines.

I finally got past the error by adding "Kill Dir()" just above "w = Dir()".

Thanks again,

Jim
--
Pops Jackson


"Dave Peterson" wrote:

I think it's because you're stacking calls to Dir().

Option Explicit
Sub CopyAll()
Dim VBComp As VBIDE.VBComponent
Dim FName As String
Dim w As String
Dim myFolder As String

myFolder = "C:\Users\Jim\Documents\MsExcel\"

w = Dir(myFolder & "*.xls")

Do While w < ""
Workbooks.Open myFolder & w 'changed to include path
With Workbooks(w)
FName = .Path & "\code.txt"

'just delete it if it's there
'ignore any error if it's not
On Error Resume Next
Kill FName
On Error GoTo 0

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 Savechanges:=true 'don't you want to save it???
w = Dir()
Loop
End Sub


Pops Jackson wrote:

I have the following code I am trying to execute:

At w = Dir() near the end of the procedure, I get the "Invalid Procedure
Call or Argument" error message. I thought I knew what to do about this but
nothing works.

Any ideas welcomed.

Sub CopyAll()
Dim VBComp As VBIDE.VBComponent
Dim FName As String
Dim w As String
w = Dir("C:\Users\Jim\Documents\MsExcel\*.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
--
Thanks,

Pops Jackson

--

Dave Peterson

Invalid Procedure Call
 

I don't understand how that worked, but glad you do!

Pops Jackson wrote:

Many thanks to both of you for your responses and help. I do indeed wish to
save the file and added the code as suggested as well as the "myfolder"
lines.

I finally got past the error by adding "Kill Dir()" just above "w = Dir()".

Thanks again,

Jim
--
Pops Jackson

"Dave Peterson" wrote:

I think it's because you're stacking calls to Dir().

Option Explicit
Sub CopyAll()
Dim VBComp As VBIDE.VBComponent
Dim FName As String
Dim w As String
Dim myFolder As String

myFolder = "C:\Users\Jim\Documents\MsExcel\"

w = Dir(myFolder & "*.xls")

Do While w < ""
Workbooks.Open myFolder & w 'changed to include path
With Workbooks(w)
FName = .Path & "\code.txt"

'just delete it if it's there
'ignore any error if it's not
On Error Resume Next
Kill FName
On Error GoTo 0

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 Savechanges:=true 'don't you want to save it???
w = Dir()
Loop
End Sub


Pops Jackson wrote:

I have the following code I am trying to execute:

At w = Dir() near the end of the procedure, I get the "Invalid Procedure
Call or Argument" error message. I thought I knew what to do about this but
nothing works.

Any ideas welcomed.

Sub CopyAll()
Dim VBComp As VBIDE.VBComponent
Dim FName As String
Dim w As String
w = Dir("C:\Users\Jim\Documents\MsExcel\*.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
--
Thanks,

Pops Jackson

--

Dave Peterson


--

Dave Peterson

Invalid Procedure Call
 

I don't understand it either but am just glad it worked.
--
Pops Jackson


"Dave Peterson" wrote:

I don't understand how that worked, but glad you do!

Pops Jackson wrote:

Many thanks to both of you for your responses and help. I do indeed wish to
save the file and added the code as suggested as well as the "myfolder"
lines.

I finally got past the error by adding "Kill Dir()" just above "w = Dir()".

Thanks again,

Jim
--
Pops Jackson

"Dave Peterson" wrote:

I think it's because you're stacking calls to Dir().

Option Explicit
Sub CopyAll()
Dim VBComp As VBIDE.VBComponent
Dim FName As String
Dim w As String
Dim myFolder As String

myFolder = "C:\Users\Jim\Documents\MsExcel\"

w = Dir(myFolder & "*.xls")

Do While w < ""
Workbooks.Open myFolder & w 'changed to include path
With Workbooks(w)
FName = .Path & "\code.txt"

'just delete it if it's there
'ignore any error if it's not
On Error Resume Next
Kill FName
On Error GoTo 0

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 Savechanges:=true 'don't you want to save it???
w = Dir()
Loop
End Sub


Pops Jackson wrote:

I have the following code I am trying to execute:

At w = Dir() near the end of the procedure, I get the "Invalid Procedure
Call or Argument" error message. I thought I knew what to do about this but
nothing works.

Any ideas welcomed.

Sub CopyAll()
Dim VBComp As VBIDE.VBComponent
Dim FName As String
Dim w As String
w = Dir("C:\Users\Jim\Documents\MsExcel\*.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
--
Thanks,

Pops Jackson

--

Dave Peterson


--

Dave Peterson