[AccessD] Programmatically change cell formulae in Ecel

hi Arthur
Sorry I am not at work to really try this out before sending a answer
if I had a copy of the excel spreadsheet I could play with it a bit more and test 
But when building strings and I tend to put the " and ' in using the CHR()  and have gotten around many problems
 
Dim strFormula as String
strFormula = CHR(34) & "=VBLOOKUP(A6," & CHR(39) & 
strFormula = strFormula & "S:\Lgroup\Fundacct\Excel\!INVPORT\CSV FILES\Sep07\[# of securities.xls]SOI"
strFormula = strFormula & CHR(39) & "!$B$3:$C$100,2,TRUE)" & CHR(34)
 
or 
strFormula =  "=VBLOOKUP(A6," & CHR(39) & 
strFormula = strFormula & "S:\Lgroup\Fundacct\Excel\!INVPORT\CSV FILES\Sep07\[# of securities.xls]SOI"
strFormula = strFormula & CHR(39) & "!$B$3:$C$100,2,TRUE)"  
 
HTH
Patti

*************************************************************
* Patricia E. O'Connor
* Associate Computer Programmer/Analyst
* OTDA - BDMA
* (W) mailto:Patricia.O'Connor at otda.state.ny.us
* (W) mailto:aa1160 at otda.state.ny.us
***********************************************************


--------------------------------------------------------
This e-mail, including any attachments, may be confidential, privileged or otherwise legally protected. It is intended only for the addressee. If you received this e-mail in error or from someone who was not authorized to send it to you, do not disseminate, copy or otherwise use this e-mail or its attachments.  Please notify the sender immediately by reply e-mail and delete the e-mail from your system. 


________________________________


From: accessd-bounces at databaseadvisors.com on behalf of Arthur Fuller
Sent: Wed 11/28/2007 3:10 PM
To: Discussion of Hardware and Software issues; Access Developers discussion and problem solving
Subject: [AccessD] Programmatically change cell formulae in Ecel



Sorry for the double-post but I'm desperate.

I'm trying to change cell formulae in Excel using VBA code and am running
into two different problems (well, maybe problem 2 will disappear when I
figure out problem 1).

I have a string stored like this:

Dim strFormula as String
strFormula "=VLOOKUP(A6,'S:\Lgroup\Fundacct\Excel\!INVPORT\CSV Files\Sep
07\[# of securities.xls]SOI'!$B$3:$C$100,2,TRUE)"

I am trying to assign it to the formula of a cell like this:

Range("E6").Formula = strFormula


What happens is that it gets pasted in as text, NOT as the formula. I tried
removing the equals sign and assigning the rest to the formula of the cell,
but that didn't work either.

I'm way behind on a deadline and I have to get this working. Any assistance
greatly appreciated!

TIA,
Arthur
--
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com <http://www.databaseadvisors.com/>