Results 1 to 2 of 2
  1. #1
    2 Star Lounger
    Join Date
    Mar 2002
    Thanked 0 Times in 0 Posts

    Database Array (Excel 2003 VBA)

    I'm trying to open multiple workbooks in folder and locate one value in each workbook.

    I was thinking of storing each value in an array so when I return to the original workbook I can dump all the values.

    Here's what I have and it's not working.

    Private Sub BuildD2PPSheets()

    Dim aCell As Range
    Dim database_array(100) As Variant

    aCode = "COMPROB.METVB"

    Set aCell = Workbooks("Book2.xls").Sheets("Sheet1").Range("A1" )
    For Each aCell In Range(aCell, aCell.End(xlDown))

    With Application.FileSearch
    .LookIn = "Cocuments and SettingssarnelliDesktopNew Folder"
    .FileType = msoFileTypeExcelWorkbooks
    If .Execute > 0 Then
    For i = 1 To .FoundFiles.Count
    'Open the file
    Workbooks.Open .FoundFiles(i)

    var1 = 1
    var2 = 1
    Do While aCell <> Cells(var1, 1)
    var1 = var1 + 1

    Do While aCode <> Cells(1, var2)
    var2 = var2 + 1

    For a = 1 To .FoundFiles.Count
    Cells(var1, var2).Value = database_array(a)
    Next a
    ActiveWorkbook.Close False
    Next i
    End If
    End With

    Next aCell

    End Sub

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Thanked 31 Times in 31 Posts

    Re: Database Array (Excel 2003 VBA)

    You don't populate the array anywhere in the code: there is no line

    database_array(i) = ...

    In the loop

    For a = 1 To .FoundFiles.Count
    Cells(var1, var2).Value = database_array(a)
    Next a

    the cells you try to fill are in the workbook you opened some lines up, and the array is empty.

    Where exactly do you want yo "dump" the values? In the same location as found?

Posting Permissions

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