PDA

View Full Version : Help with activating sheets



NaturalTvven
08-12-2014, 09:56 AM
I have a VBA code that activates two different sheets in the same xlsx workbook. Or, at least I think it should. But the code below does not switch active sheets. The debug return text is always either 'Sheet1' repeated or 'Sheet5' repeated. Can someone help me understand why? This is running in powerpoint, by the way.


'open excel spreadsheet
Set xlApp = New Excel.Application
xlApp.Workbooks.Open xltbl, True, True
xlApp.Workbooks(1).Worksheets(xlssheet).Activate
q = ActiveSheet.UsedRange.Rows.Count
For r = 2 To q


xlApp.Workbooks(1).Worksheets("Sheet1").Activate
Debug.Print ActiveSheet.Name



'Change table
xlApp.Workbooks(1).Worksheets("Sheet5").Activate
Debug.Print ActiveSheet.Name

mancubus
08-12-2014, 11:28 PM
you should have opened the thread in PowerPoint Help forum.

try this:



Sub test()
'Tools - References - Microsoft Excel x.x Object Library

Dim xlApp As Excel.Application
Dim xlWbk As Excel.Workbook
Dim xlSht As Excel.Worksheet

Set xlApp = New Excel.Application
xlApp.Visible = True 'or False
Set xlWbk = xlApp.Workbooks.Open("C:\files\my_file.xlsm") 'change to suit

For Each xlSht In xlWbk.Worksheets
Debug.Print xlSht.Name & " - " & xlSht.UsedRange.Rows.Count
Next

xlApp.Quit

End Sub

Bob Phillips
08-13-2014, 01:54 AM
It worked fine in my test.

NaturalTvven
08-13-2014, 08:12 AM
Thanks for the tips :) I got it to work in a similar fashion as mancubus indicated. And I'll find the powerpoint forum for future questions.