Array or Dictionaries?

[ClimoC]

Hello

I'm quite comfortable with building an array from a Project file, 25 columns wide and 10000 rows long, then dumping it into an Excel Spreadsheet.

I'm now essentially doing something similar in reverse though. I have a uniqueID-type column as column1 in the excel spreadsheet, and need to match this to a field in Project, then overwrite all the other fields based on what is in the array row.

My question is whether it would be (noticably) quicker to try and do this as an array, rather than with 20 scripting dictionaries? I know that seems like a rhetorical question on the face of it, but dictionaries are much easier to use, and I have access to the '.exists' functions, simple and quick adding etc.

And I suppose, how easy is it to 'loop' through my array and test conditions on it? I'd build a dictionary containing the task IDs and UniqueIDs : Would it be as simple as (quick example

For ArrR = 0 to UBound(MyArray,1)
If Dict.exists(MyArray(ArrR,1)) then
With Activeproject.tasks(MyArray(ArrR,1))
.Name = MyArray(ArrR,2)
.ResourceName = MyArray(ArrR,3)
.Duration = MyArray(ArrR,4) /480
.Text1 = MyArray(ArrR,5)
'etc
'etc
'etc
End With
Else
With Activeproject.tasks.add(MyArray(ArrR,1))
Dict.Add(MyArray(ArrR,1), .ID)
.Name = MyArray(ArrR,2)
'etc
'etc
End With
End If

ArrR = ArrR + 1
Loop

...Or is there something I'm fundamentally missing here?

Thanks
C

 

[Ruddles]

Use both - a hybrid approach. Create a single dictionary, the key being the unique project id, and the value being the position in the array where the rest of the project info is stored. That gives you the database-y look and feel of a dictionary (.Exists etc) and the ability to extract the rest of project info without the overheads of a dictionary.

Like an Access table with a primary key, I suppose.

 

[RoryA]

My understanding is that Project files (early versions anyway) were basically just mdb files with a different extension, so you might be able to actually use SQL to update it.

 

[ClimoC]

Rorya - they still are really - but for reasons I won't go into, our tech team can't or won't allow it. Plus the database that updates this Project file isn't SQL, it's something weird (and comes from a Mac...), so intermediary Excel sheets are what we use.

Plus there are some slightly more complicated task, resource, and assignment properties I need to set dependant on the values in certain fields of the array, so a loop is answer (*grumble grumble*)

 

[RoryA]

A Mac database - FileMaker?

 

[Norie]

Climo

Can you not create a csv file from the data in Project?

As far as I know you can - it's what I used to do when I had data in Excel and Project that had to be merged/combined/whatever.

Long time ago so I can't remember the exact mechanics but it wasn't difficult.

Also if you can get it into a CSV then you could perhaps use SQL as rory suggested, perhaps via ADO.

In fact you might be able to get the data you want with a 'simple' UPDATE query.

Might even be worth considering dumping both sets of data into something like Access, if available of course.

 

[ClimoC]

Climo

Can you not create a csv file from the data in Project?

As far as I know you can - it's what I used to do when I had data in Excel and Project that had to be merged/combined/whatever.

Long time ago so I can't remember the exact mechanics but it wasn't difficult.

Also if you can get it into a CSV then you could perhaps use SQL as rory suggested, perhaps via ADO.

In fact you might be able to get the data you want with a 'simple' UPDATE query.

Might even be worth considering dumping both sets of data into something like Access, if available of course.

Access available = False
If access available = false then use MSpProject csv export mapping
If project.tasks.count > 11000 and project.used fields > 60 and .mpp filesize > 38mb then crash all of windows

If crash all of windows = true, then give up and use intermediary excel sheets built from arrays to load into bespoke database systems

We do use ADO for a couple of things, but they are tiny and don't have to put up with all the nutty things we do with it (Like if the unit allocation of a resource assignment isn't 100% or 0%, then export the number portion of the units string field to one array column, and the cleaned Resource Name string to another column, so that at the DB end they can go into separate string and number fields)

Its such a stupid program... brilliant for some things... but I reckon given enough time I could develop a 'Project - System' in Excel. Heck, I kinda alreadh have in the past, putting dates across Row 6 and making 'bars' representing time coloured across x columns

 

[Norie]

Do you need to report an entire project?

Couldn't you use filtering to narrow things down and then export that data into some appropriate format?

PS I don't know what versions it's available in but you can save as to Excel, but you've probably tried that.

 

[ClimoC]

In a word - no. Windows and VBA make up a tiny fraction of my company - they don't know, they don't care, and they use and abuse and find amazingly odd workarounds to get things to look and behave how they want. Well outside of the typical 'boundaries' of the program. It's not ideal, but heck, it works (most of the time)

 

[Norie]

Oh, well.

Just thought it might be an idea.