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

 

[jabawalkie5000]

Hi Trevor

Just about to test it but all looks good.

Have you looked at stage 3? Opening each file selected in turn and copying each tab into a slide in powerpoint (the files will only ever be powerpoint or excel files)

eg 5 files , 3 excel, 2 powerpoint

3 excel files have 3 tabs each, 2 powerpoint files have 6 slides.

Copy the above into one powerpoint file in the order they were selected.

Prompt to save the new powerpoint file

End macro and clean down "List" tab for future use. Or maybe the macro can do a cleandown of the list tab at the start of the macro.

Kind regards

 

[Trevor G]

Test this part to see that it opens the workbooks.

<font face=Courier New><SPAN style="color:#00007F">Sub</SPAN> openFiles2()<br><SPAN style="color:#00007F">Dim</SPAN> strPath <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN><br><SPAN style="color:#00007F">Dim</SPAN> rngFileNames <SPAN style="color:#00007F">As</SPAN> Range:  <SPAN style="color:#00007F">Set</SPAN> rngFileNames = ActiveSheet.Range("a2", Cells(Rows.Count, "a").End(xlUp))<br><SPAN style="color:#00007F">Dim</SPAN> FileNameCell <SPAN style="color:#00007F">As</SPAN> Range<br>strPath = "M:\Access Files\"<br>    <SPAN style="color:#00007F">For</SPAN> <SPAN style="color:#00007F">Each</SPAN> FileNameCell <SPAN style="color:#00007F">In</SPAN> rngFileNames<br>        <SPAN style="color:#00007F">If</SPAN> <SPAN style="color:#00007F">Not</SPAN> IsEmpty(FileNameCell) <SPAN style="color:#00007F">Then</SPAN> Workbooks.Open strPath & FileNameCell.Value<br>    <SPAN style="color:#00007F">Next</SPAN> FileNameCell<br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN></FONT>

 

[jabawalkie5000]

Testing now, one thing I still cant see the form in excel unless I go to vba editor. I used the sub routine above but I am not sure I have put it in the right place. Should I make this a private sub routine just below the userform code?

Thanks

 

[jabawalkie5000]

Also there is no cancel button on the userform to end the routine should you chose to.

Many Thanks

 

[Trevor G]

Open the workbook then use Alt + F11 to go into VBA then select the Insert Menu and select Module add this code as previously mention

Create a Module (Insert menu and select Module), then add this, you can then see the Subroutine in the normal Excel workbook screen.

Sub ShowForm()
UserFrom1.Show
End Sub

<!-- / message --><!-- sig -->

Switch back to Excel and use Alt + F8 (this will list your macros then select ShowForm and run it.

 

[Trevor G]

Also there is no cancel button on the userform to end the routine should you chose to.

Many Thanks

Open the workbook and then use Alt + F11 then select the form. Add a command button from the toolbar and then name it in the properties cmdCancel and change the Caption to Cancel. Then double click the button and in the code screen simply add

unload me

 

[jabawalkie5000]

ahh, you are a genius. Excellent.

Two things, can i hide sheet1 and list tabs so noone gets to see it but the macro.

Then i will have a blank control tab where the userform will pop up.

I am aslo going to assign alt + f8 to a button so the list of macros does not pop up. It will confuse anyone else but me

testing your last code now

Thanks again. Will let you know in 15 mins

 

[Trevor G]

Im just looking at the PowerPoint code, so far it is similar to the Excel but is only opening the last Presentation in a list. Will work on this. You have to set the Reference to use PowerPoint. In VBA go to the Tools Menu > References > Search down list until you see Microsoft PowerPoint XX .Object Library, tick the box.

Sub openPowerPoint()
Dim strpath As String
Dim rngFileNames As Range: Set rngFileNames = ActiveCell.Range("A2", Cells(Rows.Count, "a").End(xlUp))
Dim FileNameCell As Variant
strpath = "M:\Access Files\"
Dim pptApp As PowerPoint.Application
Set pptApp = CreateObject("PowerPoint.Application")

pptApp.Visible = True
For Each FileNameCell In rngFileNames
If Not IsEmpty(FileNameCell) Then pptApp.Presentations.Open strpath & FileNameCell.Value
Next FileNameCell


End Sub

 

[jabawalkie5000]

Hi Trevor

I have hidden the list and sheet tabs and the macro works perfectly. However when I do the test to open then, I need to change the code from active sheet which is the control sheet to "list" sheet. Is this the correct amendment to your code?

Dim rngFileNames As Range: Set rngFileNames = Sheets("List").Range("a1", Cells(Rows.Count, "a").End(xlUp))

See your last but one coding sent above

Thanks

 

[jabawalkie5000]

Hi Trevor

The open workbooks macro is not working. I run it and nothing happens. I have also assigned to a button, nothing happens when clicked.

I think I need to make this code point to the hidden list sheet

Please help

Thanks

Sub openFiles2()
Dim strpath As String
Dim rngFileNames As Range: Set rngFileNames = ActiveCell.Range("A1", Cells(Rows.Count, "a").End(xlUp))
Dim FileNameCell As Range
strpath = "F:\Jacuzzi\Procurement\Platforms\Deloitte\"
For Each FileNameCell In rngFileNames
If Not IsEmpty(FileNameCell) Then Workbooks.Open strpath & FileNameCell.Value
Next FileNameCell
End Sub