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

    New VBComponent (XP; SP3)

    I am having an issue with transfering code from <!post=ThisWorkbook in one workbook to that in another workbook,465950>ThisWorkbook in one workbook to that in another workbook<!/post> . I have a new Excel file which I would like to use this feature. It works in other Excel files that I have but not this particular one. I have my VBA Project references set to include MSoft Visual Basic for Applications Extensibility 5.3. So this is not an issue.

    What I am encountering is and issue with the line of code ".AddFromString strLines". It appears to be copying but eventually I end up with Excel crashing.

    Any ideas?

    John

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

    Re: New VBComponent (XP; SP3)

    This reeks of corruption. Check out Corrupt Files on Jan Karel Pieterse's site.

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

    Re: New VBComponent (XP; SP3)

    Hans,

    I tried Jan Karel's suggestion and actually rebuilt the workbook. Both are crashing Excel.

    Regards,
    John

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

    Re: New VBComponent (XP; SP3)

    Sorry, no idea then without seeing both workbooks involved.

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

    Re: New VBComponent (XP; SP3)

    Hans,

    I have attached a sample copy of the workbook.

    Regards,
    John

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

    Re: New VBComponent (XP; SP3)

    It crashes for me too. There are many threads about this problem in the newsgroups, and various solutions have been proposed, among which the one you referred to (originally from Excel MVP Tom Ogilvy, I think). I tried several of them, but none of them work - Excel crashes all the time if there is an event procedure in ThisWorkbook (which is the entire purpose of ThisWorkbook). I'm afraid I cannot help here... <img src=/S/sad.gif border=0 alt=sad width=15 height=15>

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

    Re: New VBComponent (XP; SP3)

    Hans,

    Conceptually it is a great idea being able to copy code to

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

    Re: New VBComponent (XP; SP3)

    Yes, running slightly adapted code to import code from ThisWorkbook in your sample workbook into ThisWorkbook in the current workbook (the one running the code) works OK for me.

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

    Re: New VBComponent (XP; SP3)

    Hans,

    Do you mean the Sample file's (thisworkbook) can be exported and then imported to the new file?

    I can export the code from the sample file by using:
    With oCurWB
    Fname = "ECurWB.txt"
    .VBProject.VBComponents("ThisWorkbook").Export Fname
    End With


    When I import the code from the text file (oCurWB.txt) it imports in to a class module:
    ActiveWorkbook.VBProject.VBComponents.Import Fname

    I am not familiar with class modules and I have to brush up on some reading. I tried going directly to "ThisWorkbook": ActiveWorkbook.VBProject.VBComponents("ThisWorkboo k").Import Fname but this fails.

    Regards,
    John

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

    Re: New VBComponent (XP; SP3)

    No, I meant that I put a modified version of your SaveRoutine in the workbook into which you want to import code into ThisWorkbook. This is not what you intended, since you want to create a new workbook, so it probably is useless, but it did import the code. This is the modified version, to be placed in a module in the "receiving" workbook, and run from there (Sample.xls must also be open):

    Sub ImportRoutine()
    Dim oThisWb As Workbook, oSampleWb As Workbook
    Dim strLines As String

    Set oThisWb = ThisWorkbook
    Set oSampleWb = Workbooks("Sample.xls")

    With oSampleWb.VBProject.VBComponents("ThisWorkbook").C odeModule
    strLines = .Lines(1, .CountOfLines)
    End With

    With oThisWb.VBProject.VBComponents("ThisWorkbook").Cod eModule
    .DeleteLines 1, .CountOfLines
    .AddFromString strLines
    End With
    End Sub

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

    Re: New VBComponent (XP; SP3)

    Hans,

    I now understand. I will have to give it a whirl.

    Thanks,
    John

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

    Re: New VBComponent (XP; SP3)

    Hans,

    Call me stubborn but I did manage to determine why Excel was crashing. One can not step through the code; you just have to let it run in it's entirety.

    Use this line of code to import the text file into the new file's "ThisWorkbook":
    With ActiveWorkbook.VBProject.VBComponents("ThisWorkboo k").CodeModule
    .AddFromFile FName
    End With

    There is a caveat-
    The extract file initially creates nine lines of code and then appends the code. When you try to import the file based on the code I previously mentioned the first four lines of code are brought in starting with "Version 1 Class" and ending with "End" along with the appropriate code.

    If you delete the first nine lines of code from the text file, save the file and then run the load process everything works just fine.

    I am trying to determine how not to load the first nine rows in the text file but have not had any luck so far.

    Regards,
    John

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

    Re: New VBComponent (XP; SP3)

    That is because you use the built-in Export method to export the ThisWorkbook code. This method adds extra lines at the top of the file. You can export it as follows instead:

    Sub ExportIt()
    Dim f As Integer
    Dim i As Integer
    f = FreeFile
    Open "Export.txt" For Output As #f
    With ThisWorkbook.VBProject.VBComponents("ThisWorkbook" ).CodeModule
    For i = 1 To .CountOfLines
    Print #f, .Lines(i, 1)
    Next i
    End With
    Close #f
    End Sub

    Replace "Export.txt" with the appropriate path and file name.

    Use like this in the main routine:

    Sub SaveRoutine()
    Dim oNewWB As Workbook, oCurWB As Workbook
    Set oCurWB = ThisWorkbook

    Application.SheetsInNewWorkbook = 1
    Set oNewWB = Workbooks.Add
    oNewWB.Sheets(1).Name = "Temp"

    ExportIt

    With oNewWB.VBProject.VBComponents("ThisWorkbook").Code Module
    .DeleteLines 1, .CountOfLines
    .AddFromFile "Export.txt"
    End With
    End Sub

    Again, replace "Export.txt" as needed.

    Note: my virus scanner thought I was creating a macro virus, so be careful!

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

    Re: New VBComponent (XP; SP3)

    Hans,

    Thank you for your suggestion. It worked very nicely. Now I understand why so many people have an issue with exporting/importing code from one module to another. They don't look at the export file for superfluous lines that are not needed for the import.

    Thanks again,
    John

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

    Re: New VBComponent (XP; SP3)

    The Export method is intended to be used in combination with the Import method. Export stores some info about the module in the first lines of the exported file; Import uses this info to set properties of the imported module, and strips the lines away.
    The AddFromFile method does not import an entire module, it adds lines to an existing module. It treats the text file as text only, does not try to interpret the header as special info. So you must use "plain" text files with AddFromFile.

Posting Permissions

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