Visual Basic With Excel Problem

From Visual Basic to GNU C, this is the place to talk programming.

Moderators: SecretSquirrel, just brew it!

Visual Basic With Excel Problem

Postposted on 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:

Code: Select all
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:

Code: Select all

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

Code: Select all
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
Image
Nitrodist
Grand Gerbil Poohbah
 
Posts: 3280
Joined: Wed Jul 19, 2006 1:51 am
Location: Minnesota

Re: Visual Basic With Excel Problem

Postposted on Thu Jun 18, 2009 12:53 pm

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?
UberGerbil
Gerbil Khan
 
Posts: 9969
Joined: Thu Jun 19, 2003 3:11 pm

Re: Visual Basic With Excel Problem

Postposted on 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.
All civilizations become either spacefaring or extinct - Carl Sagan
Scorpiuscat
Gerbil Elite
 
Posts: 768
Joined: Tue Jan 01, 2002 7:00 pm
Location: Somewhere on the Edge of Reality

Re: Visual Basic With Excel Problem

Postposted on 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 :P

One part that was confusing to me was

Code: Select all
            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.
Image
Nitrodist
Grand Gerbil Poohbah
 
Posts: 3280
Joined: Wed Jul 19, 2006 1:51 am
Location: Minnesota


Return to Developer's Den

Who is online

Users browsing this forum: No registered users and 3 guests