Excel and Project

[Cheryl]

I want to change dates in my project plan with dates in
an excel document. How on god's green earth do I get
this to work? I'm beyond frustrated.

I want my VBA app which resides in project to read the
data from the excel worksheet and then change the dates
in project based on the dates in excel worksheet..

If anyone cares to help in a more detailed way, you can
reach me via e-mail at (e-mail address removed)

TIA!

 

[John]

Cheryl,
Sorry you are so frustrated. If it is any comfort, I've been there many
times. Basically you need to use the GetObject function to open the
Excel Workbook. Once that is open, you can read the necessary data and
then take appropriate action in Project. The following code example
should get you started:

[your initial Project macro code here]
'Open Excel to get data
Dim Xl As Object

On Error Resume Next
'Start Excel and open Workbook
Set Xl = GetObject([full pathname of your Workbook])
If Err <> 0 Then
MsgBox "Excel application is not available on this workstation", _
vbCritical, "Fatal Error"
Exit Sub
End If
End If

'Keep Excel in the background if direct viewing is not needed
Xl.Visible = False

[reference Excel objects directly and use to modify Project values]

'When done with Excel instance, close it
Set Xl = Nothing

Hope this helps. If you need further assistance, post again or you may
write me direct.

John

 

[Jan De Messemaeker]

On top of John's precious advice: In the vB Editor you have to go to tools,
references, check Micrososft Excel foirst.
HTH

 

[John]

Jan,
Wow, I've never considered my advice to be precious. My experience is
that most of the Excel objects can be manipulated effectively without a
reference. I have only found a few, mostly those dealing with formatting
a Worksheet, that actually require a reference to be set for the Excel
object library. I didn't mention setting a reference in my reply to
Cheryl because for what she needs, I don't believe it is necessary.

However, for the record I always set the reference for Excel in my own
macros but then my macros tend to be more complex and I often do
formatting of the Excel Worksheet in the code.

John

 

[JackD]

Jan,
Wow, I've never considered my advice to be precious. My experience is
that most of the Excel objects can be manipulated effectively without a
reference. I have only found a few, mostly those dealing with formatting
a Worksheet, that actually require a reference to be set for the Excel
object library. I didn't mention setting a reference in my reply to
Cheryl because for what she needs, I don't believe it is necessary.

Really? I've found that to even define a range I need an excel reference.
Now you are making me uncertain.

-Jack

 

[Jan De Messemaeker]

Hi,

Yes you can Dim everything "as object" but you lose all editor help.
I don't feel that as elegant.

 

[John]

Jack and Jan,
I typically set up three dimension statements in the Excel portion of my
macros
Dim s As Object
Dim c As Object
Dim Xl As Object
I set "s" to be a particular Worksheet, "c" to be a particular cell and
of course "Xl" to be the Excel object itself. Given those assignments I
have found that generally, the only time I need a reference to the Excel
object library is if I have to format a Worksheet. For example,
HorizontalAlignment and VerticalAlignment require the reference to be
set. The vast majority of other code lines that work on the Excel
objects run fine without the object library reference.

I recently wrote a macro for a client that used those formatting
statements. For some reason, and I have yet to figure out why, but the
client was unable to successfully set a reference to his Excel object
library. Sometimes it is just a matter of the clients version of Excel
versus the version I have and if I set the reference in the macro I
provide to him a runtime failure may occur if his Excel version is
later. At any rate, when we commented out the Worksheet formatting
statements, the macro runs fine with no reference to Excel set.

So Jack, yes, it will work, probably under most circumstances. Try it.

And Jan, if dimensioning my 3 simple objects is not elegant, then ok, so
be it.

John

 

[John]

Jan,
See my reply to Jack. You are right about the editor help but the same
is true for reference objects in Project. I tend to do reuse a lot of
code but if I forget the details of the syntax the help file is ready
and waiting.

John

 

[JackD]

Jack and Jan,
I typically set up three dimension statements in the Excel portion of my
macros
Dim s As Object
Dim c As Object
Dim Xl As Object
I set "s" to be a particular Worksheet, "c" to be a particular cell and
of course "Xl" to be the Excel object itself. Given those assignments I
have found that generally, the only time I need a reference to the Excel
object library is if I have to format a Worksheet. For example,
HorizontalAlignment and VerticalAlignment require the reference to be
set. The vast majority of other code lines that work on the Excel
objects run fine without the object library reference.

I recently wrote a macro for a client that used those formatting
statements. For some reason, and I have yet to figure out why, but the
client was unable to successfully set a reference to his Excel object
library. Sometimes it is just a matter of the clients version of Excel
versus the version I have and if I set the reference in the macro I
provide to him a runtime failure may occur if his Excel version is
later. At any rate, when we commented out the Worksheet formatting
statements, the macro runs fine with no reference to Excel set.

So Jack, yes, it will work, probably under most circumstances. Try it.

And Jan, if dimensioning my 3 simple objects is not elegant, then ok, so
be it.

John

I'll try it next time. Sounds more elegant than telling people how to set
references in project or explaining why the code doesn't work.

-Jack

 

[John]

Jack,
Just be sure to test the code without the reference before providing it
to anyone or you WILL be trying to explain why it doesn't work.

John