Simple Excel code

[jabawalkie5000]

Hi All,

Need some help if possible.

Problem is in 3 parts

1) I want to create a drop down of reports that is based on the tabs in the workbook. Each tab is a report. And I want to exclude the menu tab ie the first tab from the left

2) Then I want to be able to select/ highlight using the drop down, multiple tab names

3) Finally I want Excel to know the exact order I selected those reports and then export these tabs in that order into PowerPoint to create a nice little presentation ie first slide equals 1st tab selected in drop down and so on.

Excel will create a new PowerPoint file and then prompt the user to create a name for the new file

Note: some of these tabs contain graphs, others just tables, others commentary

Can this be done?

ps for the sake of this query, lets assume an excel report with 6 tabs (one being the menu tab which should be excluded)


Thanks

 

[Trevor G]

The code I added in this thread unhides the sheet. Comment out the Application.ScreenUpdating=False line and you will see it become visible.

http://www.mrexcel.com/forum/showpost.php?p=2755784&postcount=21

 

[Trevor G]

I have created the code to open all documents that are listed from cell A2 downloads, I have limited them to Excel/Word/PowerPoint. Please note I have used A2 as the start point and refered to G2 as the cell which contains the path.

I am adding this code here if others may find it useful.

<font face=Courier New><SPAN style="color:#00007F">Sub</SPAN> openDocs()<br><SPAN style="color:#007F00">'The following code will open all Word/Excel/PowerPoint documents</SPAN><br><SPAN style="color:#007F00">'That are listed in Column A starting in Range("A2")</SPAN><br><SPAN style="color:#007F00">'The path is shown in Range("G2")</SPAN><br><SPAN style="color:#007F00">'I am using the Object method rather than using the Set Reference</SPAN><br><SPAN style="color:#007F00">'Created by Trevor G 20 June 2011</SPAN><br><br><SPAN style="color:#00007F">Dim</SPAN> app <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Object</SPAN><br>Range("A2").Select<br><SPAN style="color:#00007F">Do</SPAN> <SPAN style="color:#00007F">Until</SPAN> ActiveCell.Value = ""<br><SPAN style="color:#00007F">Select</SPAN> <SPAN style="color:#00007F">Case</SPAN> LCase(Right(ActiveCell.Value, 3))<br><SPAN style="color:#00007F">Case</SPAN> "xls"<br><SPAN style="color:#00007F">Set</SPAN> app = CreateObject("Excel.Application")<br>app.Visible = <SPAN style="color:#00007F">True</SPAN><br>app.Workbooks.Open Range("G2") & ActiveCell.Value<br><SPAN style="color:#00007F">Case</SPAN> "doc"<br><SPAN style="color:#00007F">Set</SPAN> app = CreateObject("Word.Application")<br>app.Visible = <SPAN style="color:#00007F">True</SPAN><br>app.documents.Open Range("G2") & ActiveCell.Value<br>Case "ppt"<br><SPAN style="color:#00007F">Set</SPAN> app = CreateObject("PowerPoint.Application")<br>app.Visible = <SPAN style="color:#00007F">True</SPAN><br>app.Presentations.Open Range("G2") & ActiveCell.Value<br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Select</SPAN><br>ActiveCell.Offset(1, 0).Select<br><SPAN style="color:#00007F">Loop</SPAN><br><SPAN style="color:#00007F">Set</SPAN> app = <SPAN style="color:#00007F">Nothing</SPAN><br><br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN></FONT>