VBA to copy and past spacial (link) from Project to Excel

[Vit]

Hi all

how can I do that??

Thanks to everybody

Vit

 

[Rod Gill]

You need to create an object that links to Excel:
Dim xlApp as new Object
set xlapp=createobject("Excel.Application")

then open the workbook and copy in Project, but Paste link with the Excel
application object.

However, be aware that paste links are very prone to corrupting one or both
files. Never rename, move or over-write either of the files as each time you
do you roll the dice. It's when, not if, the files corrupt, maybe 5 years
maybe 5 days! Much better to have a macro run to do the copying.

--

Rod Gill
Microsoft MVP for Project

Author of the only book on Project VBA, see:
http://www.projectvbabook.com

 

[Vit]

That's OK, I'm performing some test on it and it works....

but how can I open an excel stored in a workspace????

for example, if I'm working on a Project namend Test1, the WorkSpace
should have the following URL: http://TestServer:8080/PWA/Test1

and there, in the Project Document, there is a folder named "00 - MS
Excel Support File" and inside there is the Excel File that I should
open and manipulate....

how can I open this file???


I have tried with

Set xlApp = GetObject("http://TestServer:8080/PWA/Test1/Project
Documents/00 MS Excel File/Direct Cost Summary.xls",
Excel.Application)

but it doesn't work..... it give me the following error:

Run time error: 429
ActiveX Component can't create object...

what is wrong????

thanks so much...

Cheers

Vit

 

[Rod Gill]

If you have a hyperlink that opens the file, then add the hyperlink to a
relevant Task (Insert Hyperlink). In VBA use Application.FollowHyperlink
Address, SubAddress, AddHistory, NewWindow

See help for more informaiton.

GetObject is used to try and link to any object that is active, such as the
Excel application.

on error resume next
Set xlApp=GetObject(,"Excel.Application")
If xlapp is nothing then
'No Excel application running so start one
set xlapp=createobject("Excel.Application")
xlapp.visible=true
End if


--

Rod Gill
Microsoft MVP for Project

Author of the only book on Project VBA, see:
http://www.projectvbabook.com



That's OK, I'm performing some test on it and it works....

but how can I open an excel stored in a workspace????

for example, if I'm working on a Project namend Test1, the WorkSpace
should have the following URL: http://TestServer:8080/PWA/Test1

and there, in the Project Document, there is a folder named "00 - MS
Excel Support File" and inside there is the Excel File that I should
open and manipulate....

how can I open this file???


I have tried with

Set xlApp = GetObject("http://TestServer:8080/PWA/Test1/Project
Documents/00 MS Excel File/Direct Cost Summary.xls",
Excel.Application)

but it doesn't work..... it give me the following error:

Run time error: 429
ActiveX Component can't create object...

what is wrong????

thanks so much...

Cheers

Vit

 

[Vit]

thanks for your help...

now I'm able to open the workspace in the browser....

but ho can I open the specific excel file and work on it (for example
copy cell from project and paste special as link in excel???)

thank you so much...

Cheers

Vit

 

[Vit]

I have do it!!!!!

thanks... I will let you know the progress.... I have just to input
the url with the file name...

thanks...

just a question.. ow can I bring frome somewhere the name of the
workspace of the project ???


thanks

Vit

 

[Vit]

another question:

how can I open the file not in "Read Only"??? where can I set up the
permission???

the file comes from a workspace, so I think that in VBA I have to
explicit the opening option.....

thanks

Vit

 

[Vit]

I have found how to check out the file....

Workbooks.CanCheckOut....

but how can I get from MS Project the information about the url of the
workspace????

how can I check if the workspace it has already been created

thanks

cheers

Vit

 

[Vit]

hi all again...

I have some problems.....

here you are the VBA code that I'm using....

Sub UseCanCheckOut()

Dim xlFile As String
xlFile = "http://Test:8080/PWA//Project Documents/00 MS Excel File/
Direct Cost Summary.xls"

' Determine if workbook can be checked out.
If Workbooks.CanCheckOut(xlFile) = True Then

Workbooks.CheckOut xlFile

Application.FollowHyperlink xlFile, , , True

Else
MsgBox "You are unable to check out this document at this
time."
End If

End Sub

with this code I can Check Out the File (I can see it on the
WorkSpace...), but when I'm going to open with
application.followhyperlink, it still opens the file in "Read
Only"....

how can I open the file and edit it???

thanks

Vit

 

[Vit]

Now it works...

here you are the code:

Sub UseCanCheckOut()

Dim xlApp As Excel.Application
Dim wb As Workbook
Dim xlFile As String
xlFile = "http://test:8080/PWA/Project Documents/00 MS Excel File/
BookTest.xls"

'Determine if workbook can be checked out.
If Workbooks.CanCheckOut(xlFile) = True Then
Workbooks.CheckOut xlFile

Set xlApp = New Excel.Application
xlApp.Visible = True

Set wb = xlApp.Workbooks.Open(xlFile, , False)

MsgBox wb.Name & " is checked out to you."


Else
' MsgBox "You are unable to check out this document at this
time."
End If

End Sub


the last question is: how can I get the infomation in MS Project about
the WorkSpace?
information about:

- url
- if it exist (maybe I have not alteady publish the project)

thank you so much

Cheers

Vit