vba multiple sheets conflict

Question

I am opening 4 different xlsm files that ran a macro. The macro reads data from the cells. The macros work individually but together they get messed up. because

Cells(1, 1).Value

is taken from the active sheet and not from the sheet I want .

Is there anyway to spicify a sheet like

workbook("example1").sheet("sheet1").Cells(1, 1).Value
workbook("example2").sheet("sheet1").Cells(1, 1).Value

?

Edit : (thanks Gimp) I tried

Dim oWorkSheet As Worksheet    
Set oWorkSheet = Workbooks("example1.xlsm").Sheets("sheet1")
    oWorkSheet.Cells(1, 1).Value

and got a run time error

"Object doesn't support this property and method"

Consider using Excel Object model as described here: http://stackoverflow.com/a/7402165/649687 — JMax, Aug 16 '12 at 14:34

score 3 · Answer 1 · answered Aug 16 '12 at 14:36

Yes this is a very common problem if you do not fully qualify your object. Let's take an example. If we have 4 workbooks and all four have Sheet called "Sheet1" then how should we ensure that the data is picked up from the right cell?

Cells(1, 1).Value

will always pick up the data from the current active sheet which might not be the sheet that you actually want.

If you are opening the 4 files via macro then this is the right way of doing it.

Sub Sample()
    Dim wb1 As Workbook, wb2 As Workbook, wb3 As Workbook, wb4 As Workbook
    Dim ws1 As Worksheet, ws2 As Worksheet, ws3 As Worksheet, ws4 As Worksheet

    Set wb1 = Workbooks.Open("C:\File1.xlsx")
    Set ws1 = wb1.Sheets("Sheet1")

    Set wb2 = Workbooks.Open("C:\File2.xlsx")
    Set ws2 = wb2.Sheets("Sheet1")

    Set wb3 = Workbooks.Open("C:\File3.xlsx")
    Set ws3 = wb3.Sheets("Sheet1")

    Set wb4 = Workbooks.Open("C:\File4.xlsx")
    Set ws4 = wb4.Sheets("Sheet1")

    '~~> Now you can work with the relevant cells
    Debug.Print ws1.Cells(1, 1).Value
    Debug.Print ws2.Cells(1, 1).Value
    Debug.Print ws3.Cells(1, 1).Value
    Debug.Print ws4.Cells(1, 1).Value
End Sub

If you are not opening the four files via macro you can still set them to wb1, wb2 etc and then work as mentioned above.

danielpiestrak · Answer 2 · 2012-08-16T16:33:01.967

2

Yes, use:

workbooks("example1.xlsm").sheets("sheet1").Cells(1, 1).Value
workbooks("example2.xlsm").sheets("sheet1").Cells(1, 1).Value

Update Its either your workbook or sheet name that isnt working.

try msgbox Workbooks("example1.xlsm").name If that works then your problem is likely with the sheets() section. If it doesnt, then you may need to fully qualify the section like Workbooks("C:/Folder1/example1.xlsm")

edited Aug 16 '12 at 16:33

answered Aug 16 '12 at 14:28

danielpiestrak

5,279
3
30
29

I tried that =>Set oWorkSheet = Workbooks("RT_stocks.xlsm").Sheets("stocks") – devmonster Aug 16 '12 at 15:07

vba multiple sheets conflict

2 Answers2