Printable Version of Topic

Click here to view this topic in its original format

UtterAccess Forums _ Visual Basic 6 and Earlier _ How Should I Set Up This Array?

Posted by: PaulinRhody May 15 2014, 09:12 AM

I have a workbook with 200 worksheets. Each worksheet represents a "TI". For each TI, there is a TI Title, a principal investigator, a due date, and a period of performance. There is also a list of CDRLs that are line items describing the work to be done: a code, a title, and a due date.
ach month, a form (a separate spreadsheet) is sent to each TI task manager who will comment on the progress of the work. Currently, we have an analyst manually filling in all the previously mentioned fields. (Otherwise, the task managers just won't complete the form, because of the hassle.) I want to write code that will automate this process. Because the workbook is constantly being added to (new TI worksheets included) and edited, the analyst has to complete all new forms each month.
My thought is that I would pull the information into an array and then feed the information into this template, save as, and continue this to create the 200 files automatically. My first question is whether or not using an array sounds like an efficient way of doing this. My second question is how to create the array. Would this be a multidimensional array with the same number of dimensions as there are field in the form to be created?

Posted by: ADezii May 15 2014, 10:54 AM

I am not sure that I understand the exact nature of your request, but it appears as though you can use the Worksheets Collection of the Active Workbook to:

  1. Loop through all the Worksheets in the Active Workbook.
  2. If the Worksheet's Name <> the Active Worksheet's Name:
    1. Increment a Row Counter.
    2. Copy the Data from E1 of the Worksheet to a Sequential Row Number in Column A of the MASTER Sheet.

    Dim wks As Excel.Worksheet
    Dim intRowNum As Integer
    For Each wks In ThisWorkbook.Worksheets
      If wks.Name <> ActiveSheet.Name Then
        intRowNum = intRowNum + 1
          Worksheets("Sheet4").Cells(intRowNum, 1) = wks.Range("E1").Value
      End If
  3. The above logic naturally assumes that specific information is located in exactly the same Cell(s) in each worksheet.
  4. If this is not what you are looking for, then I do apologize.

Posted by: PaulinRhody May 15 2014, 11:42 AM

I was just reading about Collections. I've never used them. I'll look into it.
The information is not in the same place every time. I wish it was.
Thanks AD

Posted by: PaulinRhody May 16 2014, 08:03 AM

My primary question is this: Which is more efficient performance,
. To gather the necessary information to fill out each form before ever beginning the form process. Gather the information into an array or collection, and then, when I have everything, go to the form and have the array or collection populate the forms.
2. Fill out the form as I iterate through each worksheet and don't worry about creating an array or collection.
I hope that makes sense.

Posted by: ADezii May 16 2014, 08:34 AM

At this point, I would think that we need to see a Completed Form (Spreadsheet)? Can you Upload one to us?