Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
CreatePivotTable: Invalid procedure call or argument | Excel Programming | |||
Invalid procedure call trying to use FormatConditions | Excel Programming | |||
invalid procedure call | Excel Programming | |||
problem with excel invalid procedure call | Excel Programming | |||
Invalid Procedure call or argument | Excel Programming |