Loop question

[Biggles]

I am trying to do some simple formatting. When I run the simple count of
records in RS_proj(1), I get 12 records. When I do all of the other
formatting, it runs 22 times (counted by the second v_ctr), opening many of
the projects twice and inserting the columns multiple times.

Any ideas what would cause this? The code to create them is slightly
different, but I thought I adjusted for that.

I have the following code:

Sub open_and_format()

Dim projApp As MSProject.Application
Dim proj As MSProject.Project
Dim proj_file As String
Dim TESTING As Boolean
Dim RS_PROJ1 As ADODB.recordset
Dim ssql1 As String
Dim fullpath As String
Dim x As Integer
Dim V_CTR As Integer

TESTING = True

If TESTING = True Then
'files to be used for testing
proj_file = "F:\scheudling tool\2008 audit plan.mpd"

Else
'files to be used for production
proj_file = "L:\COMMON\Time Reporting\2008 audit plan.mpd"

End If

Dim CONN_PROJ As ADODB.Connection 'connection to the project database

Set CONN_PROJ = New ADODB.Connection
CONN_PROJ.ConnectionString = "provider=microsoft.jet.oledb.4.0;data
source=" & "'" & proj_file & "'"
CONN_PROJ.Open

Set RS_PROJ1 = New ADODB.recordset
ssql1 = "select proj_name from msp_projects"
RS_PROJ1.Open ssql1, CONN_PROJ

x = 1

Do Until RS_PROJ1.EOF
x = x + 1
RS_PROJ1.MoveNext
Loop
Debug.Print x
V_CTR = 1

RS_PROJ1.MoveFirst
Do Until RS_PROJ1.EOF
fullpath = "<" & proj_file & ">\" & RS_PROJ1!proj_NAME
Set projApp = CreateObject("msproject.application")
projApp.Visible = True
projApp.FileOpen fullpath 'full path name
Set proj = ActiveProject

OptionsView ProjectSummary:=True
ViewApply Name:="Task Sheet"
SelectTaskColumn Column:="Name"
TableEdit Name:="&Entry", TaskTable:=True, NewName:="",
FieldName:="", NewFieldName:="WBS", Title:="", Width:=10, Align:=2,
ShowInMenu:=True, LockFirstColumn:=True, DateFormat:=255, RowHeight:=1,
ColumnPosition:=1, AlignTitle:=1
TableApply Name:="&Entry"
SelectTaskColumn Column:="Duration"
TableEdit Name:="&Entry", TaskTable:=True, NewName:="",
FieldName:="", NewFieldName:="Work", Title:="", Width:=10, Align:=2,
ShowInMenu:=True, LockFirstColumn:=True, DateFormat:=255, RowHeight:=1,
ColumnPosition:=3, AlignTitle:=1
TableApply Name:="&Entry"

For x = 1 To 8
ColumnBestFit x
Next x

projApp.FileClose pjSave
Set projApp = Nothing

RS_PROJ1.MoveNext
V_CTR = V_CTR + 1
Loop
Debug.Print V_CTR
Debug.Print "end_loop"

 

[Rod Gill]

Without knowing what setup you have I can't comment on the running 22 times.
However, best practice is to create or recreate the whole table as you never
know what state the table is in when you open the project. Creating a new
table (or re-creating the old one) guarantees a known end state. Try closing
then re-opening the recordset, that should only show 11 projects again.

In msp_Projects, I would filter for Proj_type=1 which I think from memory
ignores templates.

--

Rod Gill
Project MVP

Project VBA Book, for details visit:
http://www.projectvbabook.com

NEW!! Web based VBA training course delivered by me. For details visit:
http://projectservertraining.com/learning/index.aspx

 

[Biggles]

I have not figured out how to fix it yet, but I have isolated at least part
of the problem. I manually opened each project and saved it, and then the
macro ran cleaner, so I must not be inserting something important in my
project insertion code.

More tomorrow, if you are following along.

 

[Scudder]

Biggles.

I see a couple of problems here:

If you want to dynamically navigate a Jet database using MoveFirst for
instance make sure you use the "adOpenDynamic" parameter when you open the
recordset.

"dOpenForwardOnly" is the default for this parameter and will give you
problems with movefirst.

However I don't believe this is the root of your problem, your code
construct for the main loop has a couple of problems:

You are creating an application instance for each project, you only need to
do this once prior to the loop.

You open and assing the project okay, but you never actually release its
reference.

You are setting the application instance to nothing, but the active project
is never released under the garbage collection rules for referenced objects,
this will give you some strange behaviour.

Try setting the proj reference to nothing after your save close statement
and before you release the application reference.

Scudder.