View Single Post
  #5   Report Post  
Posted to microsoft.public.excel.programming
mikeb mikeb is offline
external usenet poster
 
Posts: 36
Default Excel crashes when adding code using vbproject object?

Peter, What I'm actually doing is adding or updating code to "ThisWokbook"
module for any workbook from a "template" workbook, if that makes sense. I'm
adding some events to "thisworkbook" from a different workbook. I want to
copy code from one "thisworkbook" to another "thisworkbook". Thanks!

"Peter T" wrote:

Serious problems can occur if you are changing code to the ThisWorbook
module or Sheet modules in ThisWorkbook, ie in same project as running the
code. Try the same code but out of ThisWorkBook, ie process some other
workbook.

Regards,
Peter T

"mikeb" wrote in message
...
Hi,
I'm running Office 2003, the code below causes Excel to crash. I tried to
accomplish this with several workarounds but it still crashed Excel. Any
ideas?
It usually crashes at the "End With" statement

filename1 = ThisWorkbook.Path & "\tempmoditerate.bas"
filename2 = ThisWorkbook.Path & "\tempmodrunsfgcalcs.bas"
filename3 = ThisWorkbook.Path & "\tempmodsfcalcs.bas"
filename4 = ThisWorkbook.Path & "\menuxxx.bas"

ThisWorkbook.VBProject.vbcomponents("modIterate"). Export filename1
ThisWorkbook.VBProject.vbcomponents("runsfgcalcs") .Export filename2
ThisWorkbook.VBProject.vbcomponents("sfcalcs").Exp ort filename3
ThisWorkbook.VBProject.vbcomponents("ThisWorkbook" ).Export filename4

Set vbp = ActiveWorkbook.VBProject
On Error Resume Next
With vbp.vbcomponents
.Remove vbp.vbcomponents("moditerate")
.Import filename1
.Remove vbp.vbcomponents("runsfgcalcs")
.Import filename2
.Remove vbp.vbcomponents("sfcalcs")
.Import filename3

Lines = vbp.vbcomponents("thisworkbook").codemodule.counto flines
vbp.vbcomponents("thisworkbook").codemodule.delete lines 1, Lines
vbp.vbcomponents("thisworkbook").codemodule.addfro mfile filename4
vbp.vbcomponents("thisworkbook").codemodule.delete lines 1, 4
End With

Kill filename1
Kill filename2
Kill filename3
Kill filename4