+ Reply to Thread
Page 7 of 7 FirstFirst ... 5 6 7
Results 91 to 101 of 101

Extracting data from database and pasting the extracted values on another sheet

  1. #91
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013
    Posts
    14,551

    Re: Extracting data from database and pasting the extracted values on another sheet

    OK,

    No matter how you separate the data, 60000+ rows x 270 columns of such formula can be never speed up.
    If you explain the formula for 1 row of each column, there will be chance to speed up.

    You might have a chance to speed up if you let vba do the whole job. No formula...

  2. #92
    Forum Contributor
    Join Date
    03-15-2012
    Location
    Mumbai, India
    MS-Off Ver
    Excel 2007
    Posts
    235

    Re: Extracting data from database and pasting the extracted values on another sheet

    Hello,

    The process uses up the entire RAM and processing stalls...

    Its probably because of pasting & calculating the formulae in Q303:JZ602 for the "n" number of stocks....

    Presently I am running it for 190 stocks... hence, it has to be pasted in 188 blocks of 300 rows each and then calculate the formulae.... is there any way of doing it in a better & easier manner? I tried leaving it on at night to see how much time it takes.... but it had hardly done a few stocks and the computer was hung....

    Can u suggest....

    Many thanks & god bless....
    Regards,
    Navin Agrawal

  3. #93
    Forum Contributor
    Join Date
    03-15-2012
    Location
    Mumbai, India
    MS-Off Ver
    Excel 2007
    Posts
    235

    Re: Extracting data from database and pasting the extracted values on another sheet

    Would it help, if we paste 30 columns at a time, instead of all 270 columns at one time?

    What I mean is can we copy Q303:AT602 all the way down (for as many times as required).

    We then copy AU303:BX602 all the way down (for as many times as required).

    We continue doing this till we copy IW303:JZ602 all the way down (for as many times as required).

    Maybe this helps.... What's your opinion?

  4. #94
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013
    Posts
    14,551

    Re: Extracting data from database and pasting the extracted values on another sheet

    As I said already, remove all formula and let vba to do the whole job.

    Probably faster.

    Formula in 300 x 190 x 270 = 15,390,000 cells... ridiculous to me...

    Why you need to calculate in 270 columns for 1 row?

  5. #95
    Forum Contributor
    Join Date
    03-15-2012
    Location
    Mumbai, India
    MS-Off Ver
    Excel 2007
    Posts
    235

    Re: Extracting data from database and pasting the extracted values on another sheet

    every cell does not have formulae...... Out of the (270 columns) x (300 rows) = 81000 cells, there are formulae only in 16652 cells.... the remaining are empty....

    Hence, out of 15,390,000 only 31,63,880 have formulae... which is 20.56%... the remaining are empty...

    but I need to have the block of (270 columns) x (300 rows) = 81000 cells for each stock....

    how would VBA do the job?

  6. #96
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013
    Posts
    14,551

    Re: Extracting data from database and pasting the extracted values on another sheet

    Then my answer is 'I don't know'.

    Can not comment on what I don't really understand what it does.

  7. #97
    Forum Contributor
    Join Date
    03-15-2012
    Location
    Mumbai, India
    MS-Off Ver
    Excel 2007
    Posts
    235

    Re: Extracting data from database and pasting the extracted values on another sheet

    I appreciate your reply. I can understand, if I am not able to explain, obviously you can't understand and if you don't understand, you can't help me.... The flaw and short coming is at my end....

    Meanwhile, what I have done is that I have physically pasted the block Q303:JZ602 right down.... what it did is made the file very bulky... but atleast my work will get done...

    Only help I need is, how do I remove the code which is copying this block down.... so that this step is completely removed....

    Pls help... and thanks for your help all this while... god bless you...

  8. #98
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013
    Posts
    14,551

    Re: Extracting data from database and pasting the extracted values on another sheet

    Change
    Please Login or Register  to view this content.
    to
    Please Login or Register  to view this content.
    In "GetRandom" procedure.

  9. #99
    Forum Contributor
    Join Date
    03-15-2012
    Location
    Mumbai, India
    MS-Off Ver
    Excel 2007
    Posts
    235

    Re: Extracting data from database and pasting the extracted values on another sheet

    Many thanks... sorry, have been away... let me revert is a bit... appreciate your help, guidance & concern.... God bless...

  10. #100
    Forum Contributor
    Join Date
    03-15-2012
    Location
    Mumbai, India
    MS-Off Ver
    Excel 2007
    Posts
    235

    Re: Extracting data from database and pasting the extracted values on another sheet

    Quote Originally Posted by jindon View Post
    Change
    Please Login or Register  to view this content.
    to
    Please Login or Register  to view this content.
    In "GetRandom" procedure.

    This helps, but The file is still very big and takes forever to compute.... fault at my end....

    I am now changing the cell reference to "RC" format, as recommended by you.... i will then put the formulae in the block "Q303:JZ602" in VBA and then add .value = .value so that only the value get displayed (in the hope that the file is not heavy....

  11. #101
    Forum Contributor
    Join Date
    03-15-2012
    Location
    Mumbai, India
    MS-Off Ver
    Excel 2007
    Posts
    235

    Re: Extracting data from database and pasting the extracted values on another sheet

    There a re a couple of other things that are cropping up....

    when we save the sheet "Dashboard" as a separate .xlsx file, it is getting save as it is..... which means it is getting saved with formulae and links to the main original file.....

    The new file is therefore very heavy and keeps getting refreshed everytime the data in mail file changes....

    It needs to be saved with >paste special > values...

    We are presently saving the sheet "Dashbiard" as .xlsx (problems listed above) and in .csv format..... can we also save it as .pdf

+ Reply to Thread
Page 7 of 7 FirstFirst ... 5 6 7

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1