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?


    Thanks,
    Bill

    Bill

    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:

    Code
    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,


    Bill:)

    Bill

    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!

Register Yourself Login