Run-time error 1100 on EditPaste

[Meghana]

Hi,
I am using a simple code to copy data from Excel and paste it into
Project 98. I am getting the Runtime Error: 1100 - This method is not
available in this situation error.
The code works fine for some files but fails for others. Here is the
piece of code:

objMPP.SelectCell 2, 2, False
Set objRange = objActiveSheet.Range("F1:I" & CStr(intUsedRange))
objRange.Copy
objMPP.EditPaste


I am new to using VBA for Project and any help/clues as to what be
going wrong or what to look for would be highly appreciated.

Thanks,
Meghana.

 

[John]

Hi,
I am using a simple code to copy data from Excel and paste it into
Project 98. I am getting the Runtime Error: 1100 - This method is not
available in this situation error.
The code works fine for some files but fails for others. Here is the
piece of code:

objMPP.SelectCell 2, 2, False
Set objRange = objActiveSheet.Range("F1:I" & CStr(intUsedRange))
objRange.Copy
objMPP.EditPaste


I am new to using VBA for Project and any help/clues as to what be
going wrong or what to look for would be highly appreciated.

Thanks,
Meghana.

Meghana,
Congratulation on taking the initiative to learn some VBA. It is a
fantastic tool.

First, it is a little hard to help troubleshoot your code since you only
show a snippit and it is very likely that the part not shown is very
relevant. For example, is "objMPP" defined as an application object? How
are you activating the Excel Worksheet and the Project file?

Data can certainly be copied and pasted using VBA but that method is
very inefficient and typically runs slow because it uses foreground
processing (i.e. active selection of objects). A much better approach is
to use background processing. With background processing the code
operates directly on Excel and Project objects (i.e. no selecting, hence
it operates in the background). The process is much more flexible,
easier to control and runs faster.

You didn't mention anything about how you are learning to use VBA. May I
offer some suggestions. First, for a really good VBA tutorial go to the
MVP website at, http://www.mvps.org/project/links.htm
and click the link at the bottom of the page for, "Project 98 Visual
Basic Environment Training Materials". Although it says it is for
Project 98, it is equally applicable to all current versions of Project.
I also suggest you take a look at fellow MVP, Jack Dahlgren's website at,
http://masamiki.com/project/macros.htm
Jack has several good examples of VBA code. One of them, "Export
hierarchy to Excel" demonstrates how to use VBA between applications.
You could probably adapt it to your needs by reversing the "GetObject"
lines.

Hope this helps.
John
Project MVP

 

[Meghana]

John,
Thank you for your prompt reply and the references you have provided. I
will go through the Export code on Jack's website. Hopefully, that will
resolve my issue.
In the meanwhile, here's some details of my code:

objMPP is the Project object being instantiated as:
set objMPP = new MSProject.Application

objExcel is the Excel object:
set objExcel = new Excel.Application

'Opens the excel file to be copied from
objExcel.Workbooks.Open strPath & strFile
Set objActiveSheet = objExcel.Workbooks(1).Sheets(1)

'Opens the project file
objMPP.FileOpen strGanttPath & strPrjFile, ReadOnly:=False
WindowActivate WindowName:=strGanttPath & strPrjFile

'Set range in excel to copy
intUsedRange = objActiveSheet.UsedRange.Rows.Count
Set objRange = objActiveSheet.Range("F1:I" & CStr(intUsedRange))

'Copy from Excel and Paste into Project
objRange.Copy
objMPP.SelectCell 2, 2, False
objMPP.EditPaste

The "EditPaste" method above throws the Run-time error.
What am I doing wrong?

I have been working with VBA before but this is the first time that I
am using the Project interface provided by VBA. I started off with the
Microsoft Office Project Visual Basic Reference online.

-Meghana.

 

[John]

John,
Thank you for your prompt reply and the references you have provided. I
will go through the Export code on Jack's website. Hopefully, that will
resolve my issue.
In the meanwhile, here's some details of my code:

objMPP is the Project object being instantiated as:
set objMPP = new MSProject.Application

objExcel is the Excel object:
set objExcel = new Excel.Application

'Opens the excel file to be copied from
objExcel.Workbooks.Open strPath & strFile
Set objActiveSheet = objExcel.Workbooks(1).Sheets(1)

'Opens the project file
objMPP.FileOpen strGanttPath & strPrjFile, ReadOnly:=False
WindowActivate WindowName:=strGanttPath & strPrjFile

'Set range in excel to copy
intUsedRange = objActiveSheet.UsedRange.Rows.Count
Set objRange = objActiveSheet.Range("F1:I" & CStr(intUsedRange))

'Copy from Excel and Paste into Project
objRange.Copy
objMPP.SelectCell 2, 2, False
objMPP.EditPaste

The "EditPaste" method above throws the Run-time error.
What am I doing wrong?

I have been working with VBA before but this is the first time that I
am using the Project interface provided by VBA. I started off with the
Microsoft Office Project Visual Basic Reference online.

-Meghana.

Meghana,
Seeing more of your code, you are obviously running VBA outside of
either Project or Excel (VB.net perhaps?). Unfortunately my experience
thus far is with VBA run from within an application. In that case I
always use the GetObject or CreateObject functions to open the secondary
application (the primary application being where the code resides -
Project or Excel). As I recall, all of Jack's VBA examples assume the
code resides in Project. The example from Jack's website that I cited
earlier uses the GetObject function to open Excel from Project.

That said, I do not have any definitive suggestions for your existing
code. You mentioned that sometimes it works and sometimes it doesn't.
How about the file and windownames that are being opened and activated?
If those are not always correct, I assume the code would hit an error at
some point.

Sorry I can't be of more help. Perhaps one of the other guys will check
in.

John
Project MVP