Simple Excel code

J

jabawalkie5000

New Member
Joined
Jun 13, 2011
Messages
41
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
 
Sort by date Sort by votes
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
Click to expand...
 
Upvote 0

Excel Facts

Who is Mr Spreadsheet?
Author John Walkenbach was Mr Spreadsheet until his retirement in June 2019.
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


 
Upvote 0
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
 
Upvote 0
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.
 
Last edited:
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0
Here is the code

Dim rngFileNames As Range: Set rngFileNames = ActiveSheet.Range("A1", Cells(Rows.Count, "a").End(xlUp))
Dim FileNameCell As Range
 
Upvote 0
You must log in or register to reply here.

Similar threads

G
  • Question Question
Jump to a TAB from a Drop-Down Menu
2
Replies
12
Views
506
LongToast
L
Y
  • Question Question
VBA Code to activate the tab and and transfer data
Replies
1
Views
251
Joe4
Joe4
M
  • Question Question
Can excel return results that are calculated from separate items in a drop down list
2 3 4
Replies
33
Views
750
Peter_SSs
Peter_SSs
GMC The Macro Man
  • Solved
Hide Sheet Tabs in Excel Online
Replies
3
Views
126
Fluff
Fluff
A
  • Question Question
Open, Copy data and Close a workbook based on the names of the tabs in my active workbook
Replies
2
Views
104
Aurore87
A

Forum statistics

Threads
1,224,586
Messages
6,179,723
Members
452,939
Latest member
WCrawford

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top