Communicating data between Excel and Project using VBA 
Author Message
 Communicating data between Excel and Project using VBA

Hi,

I am trying to write a macro that will access data held within a project
file whilst in Excel using VBA - is this possible.  I know about the import
/ export function but would like to be able to change values such as start
date, etc..., on the fly using code.

Thanks,
Peter



Sat, 23 Oct 2004 20:41:45 GMT  
 Communicating data between Excel and Project using VBA
Hi Peter.
Yes.
In VBE, Tools, Reference, buil dthe reference to MS Project.
Now in your object browser you have access to all MS project objects.
Seems like the help on the CreateObject method is a good start.
HTH
Jan



Quote:
> Hi,

> I am trying to write a macro that will access data held within a project
> file whilst in Excel using VBA - is this possible.  I know about the
import
> / export function but would like to be able to change values such as start
> date, etc..., on the fly using code.

> Thanks,
> Peter



Sat, 23 Oct 2004 21:29:51 GMT  
 Communicating data between Excel and Project using VBA
Jan,

Many thanks for your thoughts.  I don't appear to have any help covering the
CreateObject method.... I do have some on the CreateDatabase & CreateField
Methods but I am not really sure if this helps.  I have referenced MS
Project and have access to all the MS Project objects but would like to find
an example of a project file being opened within excel and values being
changed.  Would you know where I might find this??

Many thanks in advance,
Peter

Quote:

>Hi Peter.
>Yes.
>In VBE, Tools, Reference, buil dthe reference to MS Project.
>Now in your object browser you have access to all MS project objects.
>Seems like the help on the CreateObject method is a good start.
>HTH
>Jan



>> Hi,

>> I am trying to write a macro that will access data held within a project
>> file whilst in Excel using VBA - is this possible.  I know about the
>import
>> / export function but would like to be able to change values such as
start
>> date, etc..., on the fly using code.

>> Thanks,
>> Peter



Mon, 25 Oct 2004 00:40:08 GMT  
 Communicating data between Excel and Project using VBA
Hi Peter,

In VB Editor, you should click Object Browser, find createobject (it is in
the VBA.Interaction library) and asjk for the help within the object browser
(yellow question mark). It has examples.
Here is a cut-and-paste, but you really should get knowledgeable with teh
object browser's help, if not you will be back to this NG about every day!

OBTW, what you look for is
dim MspApp as MSProject.Application
Set MspApp= createobject ("MSProject.Application")

from there on, use things like

MspApp.Fileopen etc.
HTH
Jan

----------------------------------------------------------------------------
----------
CreateObject Function

Creates and returns a reference to an ActiveX object.

Syntax

CreateObject(class,[servername])

The CreateObject function syntax has these parts:

Part Description
class Required; Variant (String). The application name and class of the
object to create.
servername Optional; Variant (String). The name of the network server where
the object will be created. If servername is an empty string (""), the local
machine is used.

The class argument uses the syntax appname.objecttype and has these parts:

Part Description
appname Required; Variant (String). The name of the application providing
the object.
objecttype Required; Variant (String). The type or class of object to
create.

Remarks

Every application that supports Automation provides at least one type of
object. For example, a word processing application may provide an
Application object, a Document object, and a Toolbar object.

To create an ActiveX object, assign the object returned by CreateObject to
an object variable:

' Declare an object variable to hold the object
' reference. Dim as Object causes late binding.
Dim ExcelSheet As Object
Set ExcelSheet = CreateObject("Excel.Sheet")

This code starts the application creating the object, in this case, a
Microsoft Excel spreadsheet. Once an object is created, you reference it in
code using the object variable you defined. In the following example, you
access properties and methods of the new object using the object variable,
ExcelSheet, and other Microsoft Excel objects, including the Application
object and the Cells collection.

' Make Excel visible through the Application object.
ExcelSheet.Application.Visible = True
' Place some text in the first cell of the sheet.
ExcelSheet.Application.Cells(1, 1).Value = "This is column A, row 1"
' Save the sheet to C:\test.xls directory.
ExcelSheet.SaveAs "C:\TEST.XLS"
' Close Excel with the Quit method on the Application object.
ExcelSheet.Application.Quit
' Release the object variable.
Set ExcelSheet = Nothing

Declaring an object variable with the As Object clause creates a variable
that can contain a reference to any type of object. However, access to the
object through that variable is late bound; that is, the binding occurs when
your program is run. To create an object variable that results in early
binding, that is, binding when the program is compiled, declare the object
variable with a specific class ID. For example, you can declare and create
the following Microsoft Excel references:

Dim xlApp As Excel.Application
Dim xlBook As Excel.Workbook
Dim xlSheet As Excel.WorkSheet
Set xlApp = CreateObject("Excel.Application")
Set xlBook = xlApp.Workbooks.Add
Set xlSheet = xlBook.Worksheets(1)

The reference through an early-bound variable can give better performance,
but can only contain a reference to the class specified in the declaration.

You can pass an object returned by the CreateObject function to a function
expecting an object as an argument. For example, the following code creates
and passes a reference to a Excel.Application object:

Call MySub (CreateObject("Excel.Application"))

You can create an object on a remote networked computer by passing the name
of the computer to the servername argument of CreateObject. That name is the
same as the Machine Name portion of a share name: for a share named
"\\MyServer\Public," servername is "MyServer."

Note   Refer to COM documentation (see Microsoft Developer Network) for
additional information on making an application visible on a remote
networked computer. You may have to add a registry key for your application.

The following code returns the version number of an instance of Excel
running on a remote computer named MyServer:

Dim xlApp As Object
Set xlApp = CreateObject("Excel.Application", "MyServer")
Debug.Print xlApp.Version

If the remote server doesn't exist or is unavailable, a run-time error
occurs.

Note   Use CreateObject when there is no current instance of the object. If
an instance of the object is already running, a new instance is started, and
an object of the specified type is created. To use the current instance, or
to start the application and have it load a file, use the GetObject
function.

If an object has registered itself as a single-instance object, only one
instance of the object is created, no matter how many times CreateObject is
executed.



Quote:
> Jan,

> Many thanks for your thoughts.  I don't appear to have any help covering
the
> CreateObject method.... I do have some on the CreateDatabase & CreateField
> Methods but I am not really sure if this helps.  I have referenced MS
> Project and have access to all the MS Project objects but would like to
find
> an example of a project file being opened within excel and values being
> changed.  Would you know where I might find this??

> Many thanks in advance,
> Peter


> >Hi Peter.
> >Yes.
> >In VBE, Tools, Reference, buil dthe reference to MS Project.
> >Now in your object browser you have access to all MS project objects.
> >Seems like the help on the CreateObject method is a good start.
> >HTH
> >Jan



> >> Hi,

> >> I am trying to write a macro that will access data held within a
project
> >> file whilst in Excel using VBA - is this possible.  I know about the
> >import
> >> / export function but would like to be able to change values such as
> start
> >> date, etc..., on the fly using code.

> >> Thanks,
> >> Peter



Mon, 25 Oct 2004 14:16:19 GMT  
 Communicating data between Excel and Project using VBA
Quote:

> Hi,

> I am trying to write a macro that will access data held within a project
> file whilst in Excel using VBA - is this possible.  I know about the import
> / export function but would like to be able to change values such as start
> date, etc..., on the fly using code.

> Thanks,
> Peter

Peter,
Jan is right about learning how to use the built in help files. They
have a lot of good information but unfortunately like most help files
there are errors. Another place to get sample code is, believe it or
not, the Microsoft Support Knowledge Base. In particular, article
Q163099 should help do what you want. It goes the opposite direction,
(i.e. uses Project to pull data from Excel), but a few changes will
reverse the flow.

John



Mon, 25 Oct 2004 23:28:49 GMT  
 Communicating data between Excel and Project using VBA
Jan,

Many, many thanks.

Regards,
Peter



Mon, 25 Oct 2004 17:22:02 GMT  
 
 [ 6 post ] 

 Relevant Pages 

1. automating export data from project to oracle db using vba

2. Write data from excel to MS-Access using VBA

3. Excel VBA using ADO to extract data from SQL Server

4. Using Visual Basic for Excel 97 to communicate with Serial Port

5. Compatibility Problem Using MS Excel 97 VBA on Excel 2000/XP - Causes crashes

6. Running VBA code in EXcel from VB6 using EXcel Objects

7. Manage Excel VBA project with Visual SourceSafe Control Add-In

8. Digital signature for Excel and Word VBA projects

9. Using VBA to develop DBase file using Excel

10. Help with VBA between Excel and Project 98

11. Problems opening excel with Project VBA

12. VBA, Excel, Project 98

 

 
Powered by phpBB® Forum Software