I assume you're using linked tables in Access to write to the Oracle DB?

Is there any reason why you can't write to the database directly rather
than go through the application's front end? If you have a data
dictionary and/or good working knowledge of the database and are happy
to check data integrity yourself before storing then I would do this.

Alternatively, are you able to have the database administrator/developer
provide you with a stored procedure through which you can pass arguments
for storing? (Using DO or ODBC.)

If you can't go with the options above, taking a different tack (and
assuming you are using linked tables), I would control Access from Excel
using OLE (or vice versa) to write to the table - thus you could write
data directly rather than use COpy/paste or SendKeys.

I can expand on one of these if any of them appeal to you.

HTH,
Gareth


Zakynthos wrote:
I would welcome any suggestions on getting data from Excel into a Resource
Management (Oracle-based) database. The program allows only single
copy/paste actions but not multiple entries - the software does not allow
copy/pasting a whole column at once. I've succeeding in using a SendKeys
program to paste multiple values into a numeric field in Access, but using
the same code simply does not work in the Vantage Point program.

SendKeys definitely do not work, I've tried a number of variations and I
think the program blocks direct data entry, so I'm no longer pursuing this
line.

The best results I've got come from single copy/paste using:

Selection.Copy

AppActivate "calls offered"

With ThisWorkbook.Sheets("Sheet1")

Selection.Copy

SendKeys "%ep"

End With

This, at least, succeeds in getting the value in (admittedly, it could be
typed in just as quickly, which defeats the object).

I've produced a series of 52 (named) control buttons in Excel, which
correspond to 15 min time slots such as 8:15 am, 8:30 am etc and each one of
these has the code above embedded to allow copying/pasting a single value at
a time. There is also a cross check that the correct numerical value is
entered at the correct time slot. This is, nevertheless, still a cumbersome
as it means:

1. moving to the cell in the Excel column and clicking the button
2. moving down in the database
3. switching back to Excel, deselecting the cell and
4. moving down to the next cell etc
5. then repeating the process, by clicking the next button to copy/paste the
next value!

I would welcome suggestions for the shortest number of key strokes/button
clicks (preferably a single one would be nice!) to automate this process
without the need to:

1. manually switch between Excel and the database
2. manually move down a cell, both in Excel and the database.

Any suggestions would be most welcome!