• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Excel Template filled from Web page asp.net, won't calculate formulas

D

Daniel Forgacs

New Member
I have an excel template on a web page and is accessed by a link, when you click on link it opens the template and populates the excel template with data from web page. That all works fine but none of the formulas calculate any of data? If you double click in a cell that has a formulas and then unselect that cell it then calculate the formula, Calculation is set to Automatic. If I select all of the data and do a copy/paste then all formulas calculate. How or what is the best way of overcoming this problem? Is it because the asp.net fills the spreadsheet while its' closed with data that is "text" and it doesn't have a chance to auto check each cell for data type. Like entering a date or scientific number?
Thanks!
Daniel Forgacs
 
Without seeing the file.. just a blind shot..
did you tried.. Refresh All (Ctrl + Alt + F5)
 
Hi, Daniel Forgacs!

As a new user you might want (I'd say should and must) read this:
http://chandoo.org/forum/forums/new-users-please-start-here.14/

And regarding your issue, consider uploading a sample file (including manual examples of desired output if applicable), it'd be very useful for those who read this and might be able to help you, relieving the contributor of having to build a test file, if necessary. Thank you.

Regards!
 
Hi to all,
Sorry! For not properly introducing myself, I was swamped at the time and didn't take the time to do it. I'll do it now!

I'm Daniel, I live and work near Detroit, Michigan in the USA. I'm a Contractor at Ford Motor Company working in FCSD(Ford Customer Servcie Division) and I manage data in our department through excel and access. I've been a subscriber to Chandoo's news letter for a year or so now. I don't have a lot of time but I do read each news letter to see if there is anything I could gleen from it!

Back to my issue. I've found two methods that work but not perfect. I've also attached file!

I tried Debraj suggestion but it wouldn't work-Thanks for your response!
  1. Select Range("A11:AJ34").copy then paste - Doesn't work for all cells like (B1) but it works for the formulas I have in the middle section.
  2. John Walkenbach's Pup v7 AddIn function Force Cell Values works for some but not all cells on the sheet plus my users don't have his AddIn installed.
If you hit keys Ctrl ~ you'll be able to see where the formulas are that aren't calculating. Once you copy/paste the data they all seem to work.

I appreciate you help and assistance!
Thanks!
Daniel Forgacs
 

Attachments

This works but not sure why it hast to be done?
Code: 
public FixValues()
dim wWkSht as Worksheet
dim vCell as variant
 
Set wwksht = sheets(sheet1).usedrange
for each vcell in selection
    vcell.value = vcell.value
next vcell
end sub

Anyone have a better way? O know why?

This also works
UsedRange.Copy
usedRange.pastespecial xlpasteall, operation:=xlpastespecialoperationnone

Thanks!
DF
 
You must log in or register to reply here.
Back
Top