Exporting Formulas to Excel

[bpurser]

I am trying to create a db to help us report on equity ownership....currently, we update the total shares outstanding of a particular stock outstanding manually...and with upwards of 2200 stocks, that gets old. We have a data reporting system that links with Excel--put in the right formula, you get the data you requested...so I want to export to Excel the right formula. It looks like this:

=blp("XYZ equity", "Long_comp_name&quot

in excel, this returns the long company name....IF it is entered correctly....however, when exporting the forumlas, it gets entered into the cells in excel like so:

'=blp("XYZ equity", "Long_comp_name&quot

note the first character--which denotes a TEXT entry in Excel, preventing the formula from being read as a formula.

Short of writing code to cycle through 2200 entries in about 6 columns of excel data, and removing the offending single quote, anyone have any ideas how I can make the exported info get read as a formula?

TIA--
Ben

 

[tinymind]

Guessing you are using the Excel via access and are coding through access ...
Select the cell where you want the formula and enter the type of value you are passing ...

EG:
Excel.Range("A1&quot.Select
Excel.ActiveCell.Formula = ="blp("XYZ equity", "Long_comp_name&quot"

Tiny


Perfection is Everything
If it worked first time we wont be here!

 

[tinymind]

Correction ...

Excel.Range("A1&quot.Select
Excel.ActiveCell.Formula = "blp("XYZ equity", "Long_comp_name&quot"


Perfection is Everything
If it worked first time we wont be here!

 

[tinymind]

damn ... I will get it right ...

Excel.ActiveCell.Formula = "=blp(""XYZ equity"", ""Long_comp_name"&quot"

Perfection is Everything
If it worked first time we wont be here!

 

[bpurser]

Thanks for the replies, folks...I know bupkis about Excel Automation, and was just using the TransferSpreadsheet command to export the query where I construct the forumulas...Now for the REAL story...as mentioned, I have some 2200 recs, with about 5 of these forumulas per rec...can I get a bit more info as to the Excel coding...I can open the RS in ADO and cycle through it, but will then need to do five forumulas per line, 2200 times...

Many thanks --

Ben

 

[bpurser]

OK, got the formulas exported, but they aren't being evaluated, unless I go into the formula bar, click it and then select OK....not sure what's up...code follows:

objExpSheet.Range("rngDataAll&quot.Clear

objExpSheet.Activate
objExpSheet.Range("expbeg&quot.Select

With rst1
.MoveFirst
Do While Not .EOF()
objXLApp.ActiveCell.Formula = ![Long_comp_name]
objXLApp.ActiveCell.Offset(0, 1).Activate
objXLApp.ActiveCell.Formula = ![Ticker]
objXLApp.ActiveCell.Offset(0, 1).Activate
objXLApp.ActiveCell.Formula = ![ID_CUSIP]
objXLApp.ActiveCell.Offset(0, 1).Activate
objXLApp.ActiveCell.Formula = ![EQY_Sh_Out_Real]
objXLApp.ActiveCell.Offset(0, 1).Activate
objXLApp.ActiveCell.Formula = ![EQY_Sh_Out_Dt]
objXLApp.ActiveCell.Offset(0, 1).Activate
objXLApp.ActiveCell.Formula = ![Ticker+Equity]
objXLApp.ActiveCell.Offset(0, 1).Activate
objXLApp.ActiveCell.Formula = ![Ticker+Equity+Cusip]
objXLApp.ActiveCell.Offset(0, 1).Activate
objXLApp.ActiveCell.Formula = ![Security_Typ]
objXLApp.ActiveCell.Offset(0, 1).Activate
objXLApp.ActiveCell.Formula = ![ID_ISIN]
objXLApp.ActiveCell.Offset(0, 1).Activate
objXLApp.ActiveCell.Formula = ![AMT_OUTSTANDING]
objXLApp.ActiveCell.Offset(1, -9).Activate
.MoveNext
Loop

Yes, there's gotta be a better way to move around the spreadsheet, but for now this works:

results in this:

=blp("XYZ equity"," Long_comp_name&quot

in the cells, but it reads just as it does above...if I click on the InsertFunction button on the toolbar, then click OK, it evaluates, but not automatically...tried F9, CTL F9, all those, but no joy.....

Any suggestions?

Ben