Results 1 to 6 of 6
  1. #1
    Silver Lounger
    Join Date
    Dec 2000
    Location
    California, USA
    Posts
    1,758
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Importing ThisWorkbook Code (XP)

    I am having a challenge importing code from a text file to "ThisWorkbook" using the code below. It brings in the first line which is "Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)". Shortly after bringing in the first line, Excel crashes. From what I can tell, the code below should work.

    Set fs = CreateObject("Scripting.FileSystemObject")
    Set a = fs.OpenTextFile("c:testfile.txt")

    Do Until a.AtEndOfStream
    LineNum = oWB.VBProject.VBComponents("ThisWorkbook").CodeMod ule.CountOfLines + 1
    oWB.VBProject.VBComponents("ThisWorkbook").CodeMod ule.InsertLines LineNum, a.ReadLine
    Loop
    a.Close


    Any words of wisdom would be appreciated.

    Thanks,
    John

    PS
    oWB is a workbook name.

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Importing ThisWorkbook Code (XP)

    Does the thread starting at <post:=524,286>post 524,286</post:> help?

  3. #3
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: Importing ThisWorkbook Code (XP)

    Instead of copying the code in one line at a time (which would lead to invalid syntax), try inserting it all at once:
    <pre> Set fs = CreateObject("Scripting.FileSystemObject")
    Set a = fs.OpenTextFile("c:testfile.txt")
    Set objModule = oWb.VBProject.VBComponents("ThisWorkbook").CodeMod ule
    LineNum = objModule.CountOfLines + 1
    objModule.InsertLines LineNum, a.Readall
    </pre>

    HTH
    Regards,
    Rory

    Microsoft MVP - Excel

  4. #4
    Silver Lounger
    Join Date
    Dec 2000
    Location
    California, USA
    Posts
    1,758
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Importing ThisWorkbook Code (XP)

    Rory,

    Thanks for the "readall" portion of the code.

    I included a save rountine within the code and Excel crashes. As if there is something going on with the VBAProject which was updated. This I know works as I can see the changes.

    Regards,
    John

  5. #5
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: Importing ThisWorkbook Code (XP)

    What does the BeforeSave routine you are importing actually do? Does it help if you disable events before saving?
    Regards,
    Rory

    Microsoft MVP - Excel

  6. #6
    Silver Lounger
    Join Date
    Dec 2000
    Location
    California, USA
    Posts
    1,758
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Importing ThisWorkbook Code (XP)

    Rory,

    The "EnableEvents" was the issue. Thanks for the suggestion.

    Regards,
    John

Posting Permissions

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