Sequential Numbering

[Fiona Vidal]

I am trying to write a piece of VBA code to copy and paste a specific folder, “Data1”, from multiple files into one file. The data will subsequently be concatenated. I would like to name the folders that are pasted into the main FPD file sequentially, i.e. 01,02,03… etc. Do you know how I would do this? The number of files would be arbitrary.

Many thanks for your help

[Fiona Vidal]

I am trying to write a piece of VBA code to copy and paste a specific folder, “Data1”, from multiple files into one file. The data will subsequently be concatenated. I would like to name the folders that are pasted into the main FPD file sequentially, i.e. 01,02,03… etc. Do you know how I would do this? The number of files would be arbitrary.

Many thanks for your help

[Bernhard Kantz]

Using the Open method of the Databases collection of the Application object, one can load a FlexPro project database. The Folder object to be copied can be obtained via the RootFolder method of the opened Database object. Using its CopyTo method with the RootFolder of the destination Database performs the actual copy operation. With the Rename method the newly created folder can be given the desired name.

If you count the databases using an integer variable nCnt and FlexPro project database filenames as a string sFpd the code in your loop might look like the following:

[code]
Dim nCnt As Integer
Dim sDstName As String
Dim oSrcDb As Database
Dim oSrcFolder As Folder
Dim sFpd

…

sDstName = “Data” & Right$(Str$(nCnt + 100), 2)
Set oSrcDb = Application.Databases.Open(sFpd, fpOpenModeReadOnly)
Set oSrcFolder = oSrcDb.RootFolder(“Data”, fpObjectTypeFolder)
oSrcFolder.CopyTo(ThisDatabase.RootFolder, fpNameClashHandlingOverwrite).Rename sDstName
oSrcDb.Close fpDoNotSaveChanges
[/code]

But be aware that opening a project database is a time consuming operation, so get a cup of coffee when executing the script.

[Author]

Posts