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]

Not sure if this has fallen through... can anyone help? pls pls

Many Thanks

 

[Trevor G]

Welcome to the Board, this sounds a little complicated rather than simple excel code. So here are some question for you!

Which version of Excel/PowerPoint are you using
You will need to specify sheet names and ranges/chart names to be used
What is your knowledge of Excel/PowerPoint VBA like
Are you going to use a UserForm to run this process

 

[jabawalkie5000]

Thanks Trevor G

Office 2003 is the version
My knowledge is basic, only record macro level with ability to tweak the code to suit my needs
I am not sure about a userform, which ever method is best.

I have a slight alteration to the requirements, instead of reading the tabs in a workbook, ignore that. I would like the vba coding to read from a specific folder, create menu items of all the files in that folder. The files could be excel, word or powerpoint files.

So here is what the macro should do:

Step 1 - go to a specific folder, read the names of all the files within; must be dynamic and be able to pick up additional files within the folder each time
Step 2 - use those names to create a menu of files in excel
Step 3 - User uses a form or drop down to select files to export to power point
Step 4 - excel must be able to export files in order chosen
Step 5 - Open a new powerpoint file and export each file in turn; however some files may have more than one tab or slide or pages, excel must export all contents of each file

Can this be done?

Many Thanks

 

[Trevor G]

You will have to do this stage by stage. What you are asking for is not a simple fix.

Stage 1 look to list the files from a folder into an excel sheet. Sample code to do this is shown below: Open a workbook use Alt + F11 then select the Insert Menu and Module, then copy this code into it, note the second set of code will run to a folder and list the files, adjust the path and also .xls can be changed to *.* to list all the files in the folder. Give this ago before the next step of getting the file names into a userform and a drop down arrow.

Function GetFileList(FileSpec As String) As Variant
' Returns an array of filenames that match FileSpec
' If no matching files are found, it returns False

Dim FileArray() As Variant
Dim FileCount As Integer
Dim FileName As String

On Error GoTo NoFilesFound

FileCount = 0
FileName = Dir(FileSpec)
If FileName = "" Then GoTo NoFilesFound

' Loop until no more matching files are found
Do While FileName <> ""
FileCount = FileCount + 1
ReDim Preserve FileArray(1 To FileCount)
FileArray(FileCount) = FileName
FileName = Dir()
Loop
GetFileList = FileArray
Exit Function

' Error handler
NoFilesFound:
GetFileList = False
End Function
Sub test()
Dim p As String, x As Variant

p = "M:\Access Files\*.xls"
x = GetFileList(p)
Select Case IsArray(x)
Case True 'files found
MsgBox UBound(x)
Sheets("Sheet1").Range("A:A").Clear
For i = LBound(x) To UBound(x)
Sheets("Sheet1").Cells(i, 1).Value = x(i)
Next i
Case False 'no files found
MsgBox "No matching files"
End Select
End Sub

 

[jabawalkie5000]

It works thankx

Now onto the next stage of creating a list that is smart enough to detec the order of selection.

Then stage 3 of exporting all contents of each file into a power point presentation.

Question: what if the path was a virtual drive ie http eg VBCS or sky drive? How do you make this work?

Many Many Thanks Trevor

 

[Trevor G]

Ok then next stage, you are looking to create a userform that will use a combo box and then the combo box will get the list of file names, once you have done this, then when you select a file name it will place the name into another sheet, so the order will be based on what you select first from the drop down.

Go Into VBA (Alt + F11), then Insert Menu and UserForm.

Add 2 controls to the form, ComboBox and in the properties change the name to cboList. Add a Command Button and change the name in the properties to cmdOK and the caption to OK

Then behind the form (double click), then in the code screen change the event to Activate then add this code

Private Sub UserForm_Activate()
Application.ScreenUpdating = False
Sheets("Sheet1").Activate
Range("A2").Select
Do Until ActiveCell.Value = ""
Me.cboList.AddItem ActiveCell.Value
ActiveCell.Offset(1, 0).Select
Loop
Application.ScreenUpdating = True

End Sub

This will fill the combobox

Then behind the OK button we start building what has been selected (First of all each time). You need to have a sheet called List

Private Sub cmdOK_Click()
Application.ScreenUpdating = False
Sheets("List").Activate
Range("A2").Select
Do Until ActiveCell.Value = ""
ActiveCell.Offset(1, 0).Select
Loop
ActiveCell.Value = Me.cboList.Value
Application.ScreenUpdating = True

End Sub

See how you get on with this stage.

As to virtual path, no idea as yet, but lets see how you get on with this stage first.

 

[jabawalkie5000]

Thanks.

However, I cant see the userform on the Sheet1 tab. I have done everything you asked and now i want to run it, what do I do?

Also can we link stage one and two together at this point or should I test stage 2 first and if it works do next stage before merging all together?

Many Thanks again Trevor G

 

[jabawalkie5000]

Hi Trevor G, I think its working but how do I get the form permanently showing in excel?

Thanks

 

[Trevor G]

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

To run the initial code to get all the file names, in the UserForm on the Initialize add the word Call and the macro name