automating spreadsheets

  • I want to run a spreadsheet analysis over and over again in an automated fashion. My procedure would need to be this

    step 1: Import 6 item data set

    step 2: spreadsheet performs a complex set of calculations on this data set

    step 3: Export 12 item output data set which is the results of the calculations.

    step 4 repeat for 400 more 6 item data sets.

    does someone know how to do this?



  • Welcome to Ozgrid Forum

    There are many questions raised by your request.
    Firstly, where is your data originating and in what format?
    If another Excel Workbook, is the data always in the same place?
    Where do you want to export your results to?

    I would suggest that you tackle it stage by stage. Write code to import your data into your calculation workbook. Let Excel perform the calculation then write code to export the results. At this stage have some code to clear the calculation sheet of data ready for the next import. When you have got this working you can look into automatically dragging source data in for each data set.

    Use the Visual Basic Editor to get started

  • Hi Bill,

    Welcome to the board :)

    The basic premise required here is the use of a For Next loop for the number of iterations required, 400 in this case.

    For example:

    Sub Loopy()
        Dim cnt As Integer
        For cnt = 1 To 400
            'import the data
            'do the processing
            'export the data
        Next cnt
    End Sub

    However, to make the code a little more meaningful you will need to provide some more detail. What is the source of the imported data? What is the location of the Target for the exported data?

    EDIT: Roy beat me to it :)

  • Thanks for the response. wE have some options on data format for import and export. WE will be writing data from a computer driven test program. The data will be set up as either a text file or an excel spreadsheet, wE are wirting custom software for the Labview test software so we can choose formats to make the excel job easier. Similarly we can accept the export in a variety of formats including excel. I'm guessing that startign with excel data and ouputing excel data will make this easier. What do you think?

    Thanks for the help,



  • It will certainly be easier to import export to and from Excel.Maybe you can post an example of your workbooks, Or you could try performing your tasks with the VB Editor on and recording your code.

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!