Results 1 to 7 of 7
  1. #1
    Join Date
    Mar 2005
    Posts
    7

    Unanswered: Exporting Autocalc Values... with VBA?

    I have a large table of data generated from multiple replicates of output. I
    usually use the autocalc feature in a pivot table or chart and only examine
    the averages over all of the replicates. While this is nice in pivot tables
    and charts, I would like to export these average values to a text file. All my previous attemps at exporting resulted in a copy of the entire form that the autocalc values were generated from, and I was reduced to cutting and pasting the cell values that I wanted. Is there any way to only export these calculated values into a text file without having to highlight and paste from the pivot table view? I would also like to use VBA so I can automate this process. Can anyone offer any advice?

  2. #2
    Join Date
    Mar 2005
    Posts
    7

    A brief correction

    The autocalc values were generated from a table, not a form. When I attempted to export these averages, I just got a full version of this table.

  3. #3
    Join Date
    Jul 2003
    Location
    Michigan
    Posts
    1,941
    Why not just export the pivottable?
    Inspiration Through Fermentation

  4. #4
    Join Date
    Mar 2005
    Posts
    7

    Exporting AutoCalc Values

    Exporting the pivottable into excel gives me all of the data in the original table, along with all of the average calculation results. This is a problem because of the size of the table. Even then it would be another step to save the pivottable worksheet in excel as a text file. I want a one step export that dosen't create a copy (and double the directory size) with just the average autocalc results in a text file for some external processing by MATLAB. Can anyone help?

  5. #5
    Join Date
    Jul 2003
    Location
    Michigan
    Posts
    1,941
    The best place to find an example of how to output data to a text file from VBA is to go to your on line help for VBA and search for "Open Statement".
    That tells how to create/open a file for sending data to.
    Inspiration Through Fermentation

  6. #6
    Join Date
    Mar 2005
    Posts
    7

    Exporting AutoCalc Values

    I guess my main problem is that I don't know how to use VBA to calculate the averages on my table data. Once I know how to do that, writing those results to a file should be ok.

    One problem is that I have several experiments in the table, all with experiment numbers (in Column "E"), each with several replicates (numbered in Column "R"). I want to be able to average over the replicates for each of the experiments, then have an averages output file for each of the experiments for later processing.

    So say I have data for 4 experiments, each with 5 replicates, I would like 4 output files corresponding to Experiments 1, 2, 3, 4, each containing averaged values from each experiment's 5 replicates. Do you know how to do this average calculation for these multiple experiments in VBA?

    I thank you for your help so far!

  7. #7
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    might not solve your problem, but take a look at davg()
    davg("thisField", "thisTable", "someField = 123 and anotherField='"text"'")

    izy
    currently using SS 2008R2

Posting Permissions

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