Solved: Inserting Everything in a Directory MS Project

[SherryO]

I'm using MS Project ver 2002 sp1.

Does anyone have code to select everything in a directory. The contents of the directory(s) changes everyweek, so I have to insert a million files each time and I want to automate that selection.
Thanks!!!

[SherryO]

Is there anyone out there who could help me out? I'm still looking for help. Thanks!!!!

[TonyJollans]

Hi SherryO,

Can you be a bit more specific on what you want. 'Selecting' files in VBA code doesn't really mean very much - what do you want to do with the files? Do you want to process them in some way? Are they all mpp's - or various types?

[SherryO]

Didn't mean to be vaugue. I am trying to insert an unknown number of MSProject files into a "master" .mpp file so that I can run another routine on it for an output file that I then maniulate in MSExcel. I just don't know how to code it so it will insert everything in a named directory. I hope this helps and thanks for responding!

[TonyJollans]

Thanks Sherry,

I don't know how to do it off the top of my head but can probably find out. I have to go out for an hour or so now but will come back later.

[Killian]

Hi Sherry,

I have to confess to not knowing a damn thing about MSProject before I had a look at this and it was a bit of a strange experience... I sometimes wish the development teams for MS products would talk to each other about their object models before they started coding.

Anyway, there is, luckily, a ConsolidateProjects method that does exactly what you want. All you need is a list of files. That can quickly be done with the FileSystemObject, getting the names of all the mpp files in a specified folder.

So my first MSProject coding experience was quite painless in the end, if slightly disturbing... [VBA]'change this to your target folder
Const TARGETFOLDERPATH As String = "C:\Temp\"

Dim fso As FileSystemObject
Dim TargetFolder As Folder
Dim f As File
Dim strFiles As String

' make sure you've added a reference (Tools>References)
' to "Microsoft Scripting Runtime"
Set fso = New FileSystemObject
Set TargetFolder = fso.GetFolder(TARGETFOLDERPATH)

'build a list of file names
For Each f In TargetFolder.Files
If Right(f.Name, 3) = "mpp" Then
strFiles = strFiles & TARGETFOLDERPATH & f.Name & ListSeparator
End If
Next f
If Len(strFiles) <> 0 Then
'ConsolidateProjects with the file list
strFiles = Left(strFiles, Len(strFiles) - 1)
Application.ConsolidateProjects Filenames:=strFiles, NewWindow:=True
End If[/VBA]

[TonyJollans]

Thanks, Killian. I have only very little experience of VBA in MSProject and would have done just what you did (I mean poked around, I have no idea if your solution is the best) - and I agree it would be nice if MS development teams co-operated a bit more.

[SherryO]

I don't mean to be daft, but you lost me here
' make sure you've added a reference (Tools>References)
' to "Microsoft Scripting Runtime"

[SherryO]

I'm a super dork, nevermind about the tools, references, but when I run the script, it tells me that it cannot find one or more files. Thank you so much for your help.

[SherryO]

I needed Excel 10 object library. It's working perfectly. Thanks!!!

[TonyJollans]

Do you have a backslash at the end of your path?
[vba]
Const TARGETFOLDERPATH As String = "C:\Temp\" [/vba]

[TonyJollans]

I should've refreshed before posting!

Not sure I follow how adding the Excel 10 library helped find your project files, but glad you're sorted.

[SherryO]

I don't know either, but it works!! Thanks!