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]

I'm not showing the code in the normal way when I copy it so you can see that I am adding a couple of lines, highlighted in Red. Have you assigned the code to behind a CommandButton?

Sub openFiles2()
Dim strpath As String
Dim rngFileNames As Range: Set rngFileNames = ActiveSheet.Range("a2", Cells(Rows.Count, "a").End(xlUp))
Dim FileNameCell As Range
Application.ScreenUpdating = False
Sheets("Sheet1").Visible = True
strpath = "M:\Access Files\"

For Each FileNameCell In rngFileNames
If Not IsEmpty(FileNameCell) Then Workbooks.Open strpath & FileNameCell.Value
Next FileNameCell
Application.ScreenUpdating = False
End Sub

 

[jabawalkie5000]

i have assigned code to a command button on my control tab. running this code now.

will get back shortly

Thanks Trevor

 

[jabawalkie5000]

Sheets("Sheet1").Visible = True

The above code in your last post, should it not refer to the "list" tab to pick up the reports selected and open them?

Thanks

 

[Trevor G]

Yes change the name accordingly.

 

[jabawalkie5000]

Hi Trevor

It does not work, when I run the openfiles2 routine, nothing happens

also with the export to powerpoint routine, I get the error

compile error: user-defined type not found

and it highlights this line

pptApp As PowerPoint.Application

Please help in both instances

 

[Trevor G]

The PowerPoint one you have to set the References to use PowerPoints code in Excel, as per my instructions. In VBA Select the Tools Menu > References > Microsoft PowerPoint XX Object Library.

What have you got for the Excel Workbooks? In the List sheet you are showing the workbook names in full i.e. including .xls? Have you adjusted the strPath as well....

I have tested this several times and works fine. Try stepping through your code, which means you click in the code then in the Debug menu you will see Step Into, this will test each line of the code, Keep Pressing F5 once you start, if there is a problem it will highlight it, then you can post back your code.

 

[jabawalkie5000]

Also can you tweak the code slightly so that

a) the path can be picked up from a fixed cell in the control tab
b) the tab or slide can be scaled down or up automatically to fit the powerpoint slide (another fixed cell in control tab where a % can be inputted to adjust all tabs/ slides exported
c) can we adjust so that only word, excel or powerpoint files can be picked up, all others ignored

Thanks

 

[Trevor G]

I think it might help if I see your workbook as you have created it so far. So send a Private Message and I will reply.

 

[jabawalkie5000]

I notice when i step through it and do a split screen, I dont see the cursor highlighting and moving through the selected files in the list tab

Is this line of code the problem? Can we specifically ask it to unhide and make active the list tab or is it already doing so?

Thanks

 

[jabawalkie5000]

Here is the code

Dim rngFileNames As Range: Set rngFileNames = ActiveSheet.Range("A1", Cells(Rows.Count, "a").End(xlUp))
Dim FileNameCell As Range