Results 1 to 2 of 2
  1. #1
    2 Star Lounger
    Join Date
    Mar 2002
    Posts
    132
    Thanks
    0
    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()
    'COMPROA.METVA

    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
    .NewSearch
    .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
    Loop

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

    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
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 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
  •