Copy/Paste or Copy/Paste Special of filtered view to access

[DanRoy]

I am writing a vb appplication in MSAccess to extract field info from project
into Access tables. I also would like to ave a copy of the Gantt view when
filters are applied so that I can create a formatted report from MS Access
which presents MS project data organized in a particualr format, with a
corresponding picture at the top of the report.

I have automated the retrievable of all of the MS oproject field data that I
need. Does anyone know how you can save a picture of a filtered view in MS
project into an OLE field in MS Access.

I am able to programatically filter an MS Project file and, using
EditCopyPicture, can save the picture as a gif file with a new name. I would
like to be able to programatically, using vba, add some input strings to the
EDITCOPYPICTURE dialog to change the file type to gif, re-defining the date
range and renaming the final gif files. Can anyone show me how to add an
input string to a dialog which is generated by MS Project? Once I am able to
programatically create gif views, I will then need to read the gif into an MS
Access OLE field ( or link it so it can be refreshed when the graphic
changes).

Any help would be appreciated.

Dan

 

[Jack Dahlgren]

Use "SendKeys" to send responses to dialog boxes.
It can be a bit tricky sometimes.

Start by recording the process using the macro recorder and use sendkeys to
fill in the blanks.

-Jack Dahlgren

 

[DanRoy]

Jack,

I created this code and ran it. If you comment out the 2nd half to filter
"A" from text1 field, it works ok. If you run the code with none of the
commands commented out, then it sends the sendkey data to the function that
is executing. Can you tell me what is happening or what i am doing wrong. I
will need to either create a long procedure which is comprized of 8
iterations of the basic process or use a lookup so I can substiture the 8 cam
codes that I want to produce output for.

Alternatively, can you suggest a different way to attach this?

the code:
Option Explicit
Sub CopyPicture()
Dim prjApp As Application

Dim jTasks As Tasks
Dim jTask As Task
' Dim jjTask As Task


'clear the flag for task selection
For Each jTask In ActiveProject.Tasks
If Not (jTask Is Nothing) Then
jTask.Flag5 = "No"
End If
Next jTask

For Each jTask In ActiveProject.Tasks
If Not (jTask Is Nothing) Then
If jTask.Text2 = "B" Then
jTask.Flag5 = "Yes"
End If
End If
Next jTask



SendKeys ("{TAB}")
SendKeys ("c:\test\cam b.gif")
SendKeys ("{TAB}")
SendKeys ("{TAB}")
SendKeys ("{TAB}")
SendKeys ("{TAB}")
SendKeys ("5/1/8")
SendKeys ("{TAB}")
SendKeys ("8/1/8")
SendKeys ("{TAB}")
SendKeys ("{Enter}")
SendKeys ("{Enter}")

EditCopyPicture


'clear the flag for task selection
For Each jTask In ActiveProject.Tasks
If Not (jTask Is Nothing) Then
jTask.Flag5 = "No"
End If
Next jTask

For Each jTask In ActiveProject.Tasks
If Not (jTask Is Nothing) Then
If jTask.Text2 = "a" Then
jTask.Flag5 = "Yes"
End If
End If
Next jTask



SendKeys ("{TAB}")
SendKeys ("c:\test\cam a.gif")
SendKeys ("{TAB}")
SendKeys ("{TAB}")
SendKeys ("{TAB}")
SendKeys ("5/1/8")
SendKeys ("{TAB}")
SendKeys ("8/1/8")
SendKeys ("{TAB}")
SendKeys ("{Enter}")
SendKeys ("{Enter}")


End Sub


Thanks

Dan

 

[DanRoy]

Jack,

Could not simulate by running in the macro recorder as " EditCopyPicture" is
not a menu item. I was only able to run as vba code.

Dan

 

[Jack Dahlgren]

Looks like you are missing at least one "editcopypicture" line in your code.
I think it should be located right near the send keys.

Sorry I don't have a lot of time to debug this further for you.
But here are my suggestions.
build a procedure which just takes a snapshot. Call it something like
takeSnapshot. You can even feed it a parameter for the name.
Cut out all the filtering from it.

sub takeSnapshot(by ref picName as string)
'all the sendkeys and editcopy picture stuff goes here
dim fileString as string
fileString = "c:\test\cam " & picName"
SendKeys (fileString)
'etc.
end sub



Then build a procedure which just does filtering given a parameter.

sub filterMe(by ref filterCriteria as string)
If jTask.Text2 = filterCriteria Then
jTask.Flag5 = "Yes"
'etc.
end sub

then combine them

sub filterAndCopy(by ref thing as string)
filterMe thing
takeSnapshot thing
end sub

Now you have something you can call for each of your values
Put them in an array or something and you can go through for each then then
filterAndCopy no matter how many you have.
It will be easier to debug and extend.

-Jack