TR Forums

Nitrodist · Thu Jun 18, 2009 3:52 am

Basically I'm trying to get a program to take values from certain cells in all of the files in a folder.

VBA is NOT cooperating with me

I think something is wrong with Excel

After messing around with the code for several hours I've boiled it down to this:

Sub Workbook()
    Dim myWorkbook As Workbooks
    Set myWorkbook = ActiveWorkbook
    With myWorkbook
        'actions here
    End With
End Sub

So that won't work. Is there a way to fix this? Help!

The complete code is here:


Sub openAllfilesInALocation()
    Dim i As Integer, wb As Workbook
    Dim Val1 As Integer
    Dim Val2 As Integer
    Dim Val3 As Integer
    Dim Val4 As Integer
    Dim Val5 As Integer
    Dim Val6 As Integer
    With Application.FileSearch
        .NewSearch
        .LookIn = "C:\myexcel"
        .SearchSubFolders = False
        .Filename = "*.xls"
        .Execute
        For i = 1 To .FoundFiles.Count
            MsgBox (.FoundFiles(i))
            Workbooks.Open (.FoundFiles(i))
            wb = Workbooks(Filename:=.FoundFiles(i))
            'Open each workbook
            MsgBox ("this does not work")
            'Perform the operation on the open workbook
            'Val1 = Val1 + Workbooks(.FoundFiles(i)).Worksheets(1).Range("E6")
            'Val2 = Val2 + wb.Worksheets(1).Range("E7")
            'Val3 = Val3 + wb.Worksheets(1).Range("E8")
            'Val4 = Val4 + wb.Worksheets(1).Range("E9")
            'Val5 = Val5 + wb.Worksheets(1).Range("E10")
            'Val6 = Val6 + wb.Worksheets(1).Range("E11")
            'Save and close the workbook
            wb.Close
            'On to the next workbook
            MsgBox ("runs")
        Next i
    End With
    
    MsgBox ("Does run!")
    
    ThisWorkbook.Worksheets(1).Range("A1").Value = Val1
    ThisWorkbook.Worksheets(1).Range("A2").Value = Val2
    ThisWorkbook.Worksheets(1).Range("A3").Value = Val3
    ThisWorkbook.Worksheets(1).Range("A4").Value = Val4
    ThisWorkbook.Worksheets(1).Range("A5").Value = Val5
    ThisWorkbook.Worksheets(1).Range("A6").Value = Val6
    

End Sub

Argh, I got it, finally

Sub openAllfilesInALocation()
    Dim i As Integer, wbOpened As Workbook, wsOpened As Worksheet
    
    Dim Val1 As Integer
    Dim Val2 As Integer
    Dim Val3 As Integer
    Dim Val4 As Integer
    Dim Val5 As Integer
    Dim Val6 As Integer
    With Application.FileSearch
        .NewSearch
        .LookIn = "C:\myexcel"
        .SearchSubFolders = False
        .Filename = "*.xls"
        .Execute
        For i = 1 To .FoundFiles.Count
            'Open each workbook
            Set wbOpened = Workbooks.Open(.FoundFiles(i))
            Set wsOpened = wbOpened.Sheets(1)
            'Perform the operation on the open workbook
            'Val1 = Val1 + wbOpened.Worksheets(1).Range("E6")
            Val1 = Val1 + wsOpened.Range("E6")
            Val2 = Val2 + wsOpened.Range("E7")
            Val3 = Val3 + wsOpened.Range("E8")
            Val4 = Val4 + wsOpened.Range("E9")
            Val5 = Val5 + wsOpened.Range("E10")
            Val6 = Val6 + wsOpened.Range("E11")
            'Save and close the workbook
            wbOpened.Close
            'On to the next workbook
        Next i
    End With
    
    ThisWorkbook.Worksheets(1).Range("A1").Value = Val1
    ThisWorkbook.Worksheets(1).Range("A2").Value = Val2
    ThisWorkbook.Worksheets(1).Range("A3").Value = Val3
    ThisWorkbook.Worksheets(1).Range("A4").Value = Val4
    ThisWorkbook.Worksheets(1).Range("A5").Value = Val5
    ThisWorkbook.Worksheets(1).Range("A6").Value = Val6
    

End Sub

UberGerbil · Thu Jun 18, 2009 12:53 pm

I see that you got it, but you must've been getting errors with

    Dim myWorkbook As Workbooks
    Set myWorkbook = ActiveWorkbook

You do understand that there's a difference between Workbooks and Workbook?
One is a collection object (with properties like Count) that holds references to the other. Or was that just a typo?

Scorpiuscat · Thu Jun 18, 2009 1:01 pm

If one of the fine Gerbils here dosent have an answer for you, the forums at http://www.mrexcel.com/ are priceless for those working on complex Excel forms.

Nitrodist · Thu Jun 18, 2009 1:12 pm

UberGerbil wrote:
I see that you got it, but you must've been getting errors with
Code: Select all
    Dim myWorkbook As Workbooks
    Set myWorkbook = ActiveWorkbook
You do understand that there's a difference between Workbooks and Workbook?
One is a collection object (with properties like Count) that holds references to the other. Or was that just a typo?

Both. It was very late last night.

I wasn't sure at all what wasn't working so I was just trying to muddle my way through it as the debugger in VBA is complete ****. Trial and error didn't favor me this time

One part that was confusing to me was

            wb = Workbooks(.FoundFiles(i))

Where you have to put Set behind that one. That doesn't make sense to me because in all of the other variables that are set, you don't have to do that.

In that little code segment there it's going from a Workbooks to a Workbook object, so I was messing around with Workbooks => Workbooks -- I thought that may have been the problem.

TR Forums

Visual Basic With Excel Problem

Visual Basic With Excel Problem

Re: Visual Basic With Excel Problem

Re: Visual Basic With Excel Problem

Re: Visual Basic With Excel Problem

Who is online