I have been reading and researching for days and cannot seem to find
what I am looking for. And I am close. I have excel file and in the
file are 150 different links (each in their own cell) to files on a
server. Each file is a PowerPoint document (.pptx). I update my
spreadsheet with a lot of different data but I want to run a macro
that opens all the PowerPoint files into one PowerPoint presentation.
I can generate a macro that creates a PowerPoint slide but not one
that opens the files from the excel sheet I work from. Any
example codes would be helpful. I am close but yest so far away.
Thanks
Marty
I think you're going about this entirely wrong!!
I read your code and I'm curious as to why you're not doing this in a
PowerPoint VBA project! You could read the files list without having to
open the Excel file (using ADODB) into VBA there and process the entire
task in PP. Easier yet, store the list in a txt file and use standard
VBA file I/O functions to read the file into an array, then loop to get
each file.
But.., if you insist on doing this in Excel then...
Add another variable of Variant type, and a counter for the loop:
Dim vList, n&
'Dump the list into an array
vList = ActiveSheet.Range("A1:A5")
'Iterate the array to process each list item
For n = LBound(vList) To UBound(vList)
Debug.Print vList(n, 1) '//process each file here
Next 'n
...where vList is a 2D array consisting of 5 rows and 1 col.
Note that best practice in VBA programming recommends *'NEVER hijack an
existing instance'* of an app for automation. (Exception is Outlook
because it doesn't allow multiple instances!)
Now I've never automated PP but reading its Object ref I suspect you
could revise your Excel code like so...
Sub CreatePowerPoint()
Dim vList, n&
vList = ActiveSheet.Range("A1:A5")
On Error GoTo Cleanup
'Automate a new instance of PowerPoint
With CreateObject("PowerPoint.Application")
'Make a presentation in PowerPoint
.Visible = True
For n = LBound(vList) To UBound(vList)
'Add a new slide from the file
With .Presentations.Add
'Insert the slide into the presentation
.slides.InsertFromFile vfile(n, 1), .slides.Count + 1
End With '.Presentations.Add
Next 'n
End With 'CreateObject
Cleanup:
End Sub
...to simplify the process
But I think you'd be better off doing a PP project and store the slides
list in a text file. That means you'll need to persue this in a PP
group. In this case the following revised Excel code should work...
Sub InsertSlidesFromFile()
' Inserts slides from a list of PPTs stored in a txt file
Dim vList, n&
vList = Split(ReadTextFile("C:\documents\TestPP.txt"), vbCrLf)
On Error GoTo Cleanup
With Application
For n = LBound(vList) To UBound(vList)
'Add a new slide from the file
With .Presentations.Add
'Insert the slide into the presentation
.slides.InsertFromFile vfile(n), .slides.Count + 1
End With '.Presentations.Add
Next 'n
End With 'Application
Cleanup:
End Sub
Function ReadTextFile$(Filename$)
' Reads large amounts of data from a text file in one single step.
Dim iNum%
On Error GoTo ErrHandler
iNum = FreeFile(): Open Filename For Input As #iNum
ReadTextFile = Space$(LOF(iNum))
ReadTextFile = Input(LOF(iNum), iNum)
ErrHandler:
Close #iNum: If Err Then Err.Raise Err.Number, , Err.Description
End Function 'ReadTextFile()
Optionally, if your slide files are the only files stored in a specific
folder then you could also drill that folder with VBA's Dir() function
to access each file without the need for the 'ReadTextFile' helper
routine...
Sub InsertSlidesFromFolder()
' Inserts slides from a list of PPTs stored in a txt file
Dim vFile, n&
vFile = Dir("C:\documents\*.*", vbDirectory)
On Error GoTo Cleanup
With Application
Do While Len(vFile)
'Add a new slide from the file
With .Presentations.Add
'Insert the slide into the presentation
.slides.InsertFromFile vFile, .slides.Count + 1
End With '.Presentations.Add
vFile = Dir()
Loop
End With 'Application
Cleanup:
End Sub
HTH
--
Garry
Free usenet access at
http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion
