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!

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!

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.

What happens when you try using sendkeys? I don't know how the
application could "block direct data entry", since I'm not sure it can
distinguish between sendkeys and the user using the keyboard.

Tim


"Zakynthos" wrote in message
...
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!

When I use any SendKeys which don't involve a prior 'selection.copy' in the
code, the Maintain Queue Actuals Window is activated but the cursor is 'lost'
unless you click in the selected cell or on the scrollbar.

Ive tried this code:

AppActivate "maintain queue actuals"

With ThisWorkbook.Sheets("Sheet1")

Selection.Copy

SendKeys "%ep"

And then ONE of the following:

SendKeys "+END"
SendKeys "{DOWN}"
SendKeys "{TAB}"

These last SendKeys have been tried both with/without the brackets and "" .

This shows in the Database as a highlighted pasted number copied from Excel.

I've not succeeded in:

a) getting the cursor to select the next cell down (with DOWN or ENTER, nor
any other cell, e.g. with TAB)
b) switching back to the Excel window.

If I could write the script to do this, I would be able to enter the data by
simply clicking the buttons I've created. Given the large amount of data
being manually input data from one system to another, this would save a lot
of time and reduce errors in transcription.
"Tim Williams" wrote:

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.

What happens when you try using sendkeys? I don't know how the
application could "block direct data entry", since I'm not sure it can
distinguish between sendkeys and the user using the keyboard.

Tim


"Zakynthos" wrote in message
...
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!

If there are no key combinations you can use to navigate the VP user
interface and the application does not support automation then it would seem
you are out of luck.

Perhaps you could try one of the more generic "automation" applications such
as AutoIt ?

http://www.autoitscript.com/autoit3/

Tim

--
Tim Williams
Palo Alto, CA


"Zakynthos" wrote in message
...
When I use any SendKeys which don't involve a prior 'selection.copy' in
the
code, the Maintain Queue Actuals Window is activated but the cursor is
'lost'
unless you click in the selected cell or on the scrollbar.

Ive tried this code:

AppActivate "maintain queue actuals"

With ThisWorkbook.Sheets("Sheet1")

Selection.Copy

SendKeys "%ep"

And then ONE of the following:

SendKeys "+END"
SendKeys "{DOWN}"
SendKeys "{TAB}"

These last SendKeys have been tried both with/without the brackets and ""
..

This shows in the Database as a highlighted pasted number copied from
Excel.

I've not succeeded in:

a) getting the cursor to select the next cell down (with DOWN or ENTER,
nor
any other cell, e.g. with TAB)
b) switching back to the Excel window.

If I could write the script to do this, I would be able to enter the data
by
simply clicking the buttons I've created. Given the large amount of data
being manually input data from one system to another, this would save a
lot
of time and reduce errors in transcription.
"Tim Williams" wrote:

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.

What happens when you try using sendkeys? I don't know how the
application could "block direct data entry", since I'm not sure it can
distinguish between sendkeys and the user using the keyboard.

Tim


"Zakynthos" wrote in message
...
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!

Tim,

Many thanks for the AutoIT link, certainly worth following up. I have now
managed to use SendKeys TAB routines to move between various parts of some
windows of the VP database, so I think, with a trial and error approach, if
what I want to do can be done, I'll do it!

"Tim Williams" wrote:

If there are no key combinations you can use to navigate the VP user
interface and the application does not support automation then it would seem
you are out of luck.

Perhaps you could try one of the more generic "automation" applications such
as AutoIt ?

http://www.autoitscript.com/autoit3/

Tim

--
Tim Williams
Palo Alto, CA


"Zakynthos" wrote in message
...
When I use any SendKeys which don't involve a prior 'selection.copy' in
the
code, the Maintain Queue Actuals Window is activated but the cursor is
'lost'
unless you click in the selected cell or on the scrollbar.

Ive tried this code:

AppActivate "maintain queue actuals"

With ThisWorkbook.Sheets("Sheet1")

Selection.Copy

SendKeys "%ep"

And then ONE of the following:

SendKeys "+END"
SendKeys "{DOWN}"
SendKeys "{TAB}"

These last SendKeys have been tried both with/without the brackets and ""
..

This shows in the Database as a highlighted pasted number copied from
Excel.

I've not succeeded in:

a) getting the cursor to select the next cell down (with DOWN or ENTER,
nor
any other cell, e.g. with TAB)
b) switching back to the Excel window.

If I could write the script to do this, I would be able to enter the data
by
simply clicking the buttons I've created. Given the large amount of data
being manually input data from one system to another, this would save a
lot
of time and reduce errors in transcription.
"Tim Williams" wrote:

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.

What happens when you try using sendkeys? I don't know how the
application could "block direct data entry", since I'm not sure it can
distinguish between sendkeys and the user using the keyboard.

Tim


"Zakynthos" wrote in message
...
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!