Hi Paul, I've seen your post in the microsoft news groups, so you may have seen this link, but take a look at it anyway. Multiple instances of XL have always been a problem as far as VBA is concerned.
http://www.microsoft.com/office/community/en-us/default.mspx?&query=multiple+instances+of+Excel&lang=en&cr=US&guid=&sloc=en-us&dg=microsoft.public.excel.programming&p=1&tid=e038db96-1556-4d32-a524-fe6a711b8409&mid=1600366f-c5e5-42c0-8095-82242a0c87ec By the way, I put "multiple instances of excel" into the search thingy of the microsoft excel news group, and got lots of hits. Didn't have time to read them all. Dave. > Date: Fri, 11 Sep 2009 07:06:48 -0700 > Subject: $$Excel-Macros$$ Re: VBA - Access multiple Excel instances > From: paul...@gmail.com > To: excel-macros@googlegroups.com > > > Hi Dave, > > In a way that was my problem, dispite the fact that I had calculation > set to manual the sheet with the large large table was still > recalculating at each iteration! I've managed to get around it by > explicitly turning off calculation on that sheet during the goal seek > using > > wks.enablecalculation = false > > then re-enabling after the goal seek. > > That said I would still like to know if it is possible and how to > manage multiple instances of Excel from within one Sub? > > Thanks > > Paul > > On Sep 11, 2:39 pm, Dave Bonallack <davebonall...@hotmail.com> wrote: > > Hi, > > Can't you just set calculations to manual while you do the Goal Seek? > > Dave. > > > > > > > > > Date: Fri, 11 Sep 2009 03:33:04 -0700 > > > Subject: $$Excel-Macros$$ VBA - Access multiple Excel instances > > > From: paul...@gmail.com > > > To: excel-macros@googlegroups.com > > > > > Hi, > > > > > I have a very large large spreadsheet with 20000 rows and about 50 > > > columns most with long complex formulas so calculation on this sheet > > > takes time. The main purpose of my code is to perform a Goal Seek but > > > depending on results the code might loop up to 100 times. Each Goal > > > Seek takes about 9 minutes! > > > > > One column from the table changes at each iteration so what I want to > > > do is copy this column to another workbook, perform the Goal Seek, > > > then copy back my required result. My problem is though that if the > > > second workbook is in the same Excel instance as the actual table the > > > process is just as slow. > > > > > So, my question is how do I access a completely separate instance > > > within my VBA code so as to paste the 20000 line column, perform the > > > goal seek in that workbook then copy the result back > > > > > Any help appreciated > > > > > ..pc > > > > _________________________________________________________________ > > View photos of singles in your area Click > > Herehttp://clk.atdmt.com/NMN/go/150855801/direct/01/ > > > _________________________________________________________________ Use Messenger in your Hotmail inbox Find out how here http://windowslive.ninemsn.com.au/article.aspx?id=823454 --~--~---------~--~----~------------~-------~--~----~ ---------------------------------------------------------------------------------- Some important links for excel users: 1. Excel and VBA Tutorials(Video and Text), Free add-ins downloads at http://www.excelitems.com 2. Excel tutorials at http://www.excel-macros.blogspot.com 3. Learn VBA Macros at http://www.vbamacros.blogspot.com 4. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com If you find any spam message in the group, please send an email to: Ayush Jain @ jainayus...@gmail.com or Ashish Jain @ 26may.1...@gmail.com <><><><><><><><><><><><><><><><><><><><><><> HELP US GROW !! We reach over 6,500 subscribers worldwide and receive many nice notes about the learning and support from the group. Our goal is to have 10,000 subscribers by the end of 2009. Let friends and co-workers know they can subscribe to group at http://groups.google.com/group/excel-macros/subscribe -~----------~----~----~----~------~----~------~--~---
