Results 1 to 5 of 5

Thread: modifying code using addfromfile method

  1. #1

    modifying code using addfromfile method

    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?

  2. #2
    Site Admin
    Jedi Master
    VBAX Guru Jacob Hilderbrand's Avatar
    Jun 2004
    Roseville, CA

    I moved this thread to our Mac section.

  3. #3
    VBAX Tutor
    Mar 2005
    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.)

  4. #4

    Further detail

    This is the sub I want to run on each workbook:

    [vba]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
    End Sub[/vba]

    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).

  5. #5
    Moderator VBAX Guru Ken Puls's Avatar
    Aug 2004
    Nanaimo, BC, Canada
    Hi Paul,

    FYI - if you enclose your code in [vba][/vba] tags then it will be formatted as per the VBIDE to improve readability.

    I've fixed this one for you!
    Ken Puls, CMA - Microsoft MVP (Excel)
    I hate it when my computer does what I tell it to, and not what I want it to.

    Learn how to use our KB tags! -||- Ken's Excel Website -||- Ken's Excel Forums -||- My Blog -||- Excel Training Calendar

    This is a shameless plug for my new book "RibbonX - Customizing the Office 2007 Ribbon". Find out more about it here!

    Help keep VBAX clean! Use the 'Thread Tools' menu to mark your own threads solved!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts