Ignore Pasting errors while using a macro.

[Eric W]

Hello,
I would like to create a macro to paste data from Access 2007 within Project
2007.

When pasting my data, I always receive a pasting error message with a dialog
box to press yes if I want to continue an ignore the error. Pressing Yes
works fine and give the expected result.

My questionis then: how can I automatise this using a macro ?

Thanks in advance for your help,

Eric

 

[John]

Hello,
I would like to create a macro to paste data from Access 2007 within Project
2007.

When pasting my data, I always receive a pasting error message with a dialog
box to press yes if I want to continue an ignore the error. Pressing Yes
works fine and give the expected result.

My questionis then: how can I automatise this using a macro ?

Thanks in advance for your help,

Eric

Eric,
You didn't say what the error message says. Nonetheless, if you want to
transfer the data using VBA, and I highly recommend you do, you will be
much better off transferring data elements directly from the Access
recordset than copying and pasting.

The process is pretty straightforward. It basically consists of opening
an ODBC connection and then cycling through the database table
recordset. Here's a snippet of code I use in one of my macros to do
essentially what you want.


Conn.ConnectionString = "Provider=Microsoft.jet.oledb.4.0;" _
& "Data source=[database name].mdb;persist security info=false"
Conn.Open

'import the data
Set rs = New ADODB.Recordset

rs.Open "SELECT * FROM Table3", Conn

Do Until rs.EOF
[your code here]
rs.MoveNext
Loop

For a more detailed explanation of the code, I highly recommend fellow
MVP, Rod Gill's book on Project VBA. For more information, see
http://www.projectvbabook.com.

John
Project MVP

 

[Eric W]

John,

Thanks for your fast answer.

The errors that I get are formating errors and start date being before
project start.
I would also prefer to do it via ODBC or at least open an mdb file in
project but the problem is that the data are the results of a query.

From what I saw, with project, I can only open a table and not a query.
Creating a table with the results of the query is not the prefered solution.

Any advice ?

Thanks,

Eric

 

[John]

John,

Thanks for your fast answer.

The errors that I get are formating errors and start date being before
project start.
I would also prefer to do it via ODBC or at least open an mdb file in
project but the problem is that the data are the results of a query.

From what I saw, with project, I can only open a table and not a query.
Creating a table with the results of the query is not the prefered solution.

Any advice ?

Thanks,

Eric

Eric,
I haven't actually done it this way, (I extract data from an Access
table), but importing the data via a query is also possible. The pseudo
code structure is:
[define your user query string]
rs.open [string name from above], Conn

Then you can go through the Do loop outlined in my previous response.

Hope this helps.

John
Project MVP