PDA

View Full Version : modifying code using addfromfile method



paulr
03-22-2005, 03:44 PM
I am writing a macro which corrects code in many standard workbooks. I am working with Excel 98 for Mac. I have a loop which deletes all the code from a module using the deletelines method and then adds the corrected code from a file using the addfromfile method. The loop works correctly with the first workbook but not any subsequent workbooks. I've also noticed that the addfromfile method will work again if I edit the sub in any way (e. g., even add a comment). Why does the addfromfile method only work once?

Jacob Hilderbrand
03-22-2005, 03:58 PM
Paul

I moved this thread to our Mac section.

BlueCactus
03-23-2005, 09:32 AM
Can you post a copy of the loop? (I'm not familiar with these methods, so I'd have to see your code to understand the question.)

paulr
03-23-2005, 05:10 PM
This is the sub I want to run on each workbook:

Public Sub MSmodMender()
'replaces a module with a new module from a text file
For Each i In Application.VBE.VBProjects("VBAMidasWrkShtProj").VBComponents
If i.CodeModule.Name = "UpdatePhaseMod" Then
'need to name the module to be replaced
i.CodeModule.DeleteLines 1, i.CodeModule.CountOfLines
'delete all lines in the module
i.CodeModule.AddFromFile "cape cod:fmidas:closet:back shelf:UpdatePhases03042005.txt"
'need to name the text file
Exit Sub
End If
Next
End Sub

First workbook is fine, then the AddFromFile statement does nothing unless I quit and relaunch or edit the procedure in some way (any way actually).

Ken Puls
03-23-2005, 09:00 PM
Hi Paul,

a

I've fixed this one for you! ;)