vba api class / macro to copy from excel to an Access form

[BHspicer]

can anyone help?

i am trying to write a macro to copy values from excel and paste into an open msAccess form using,

API to find class
and
Send keys / keypress / keystrokes to paste value to open form textbox

can anyone point me in right direction.

many thanks

Nicholas

 

[xlbo]

I know it's not the answer to the question but sendkeys is just so flaky - have you considered / is it possible to use excel to send data directly to the database then open the form up based on the new data entered in the background tables?

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[PHV]

Why not using a Recordset ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[BHspicer]

xlbo

Thank you for reply.

Unfortunately we dont have access to database.

I am a vba developer working with business team, and IT admin wont on this occasion give us direct access.

i can, via api's identify the class of the open object, but from there, i do not know how to write the code to control the classor textbox object and pass via sendkeys the value to the form, tab and hit 'enter'?

 

[BHspicer]

PHV

as with above post, i dont have vba control of the database or form?

 

[PHV]

paste into an open msAccess form
we dont have access to database
Well, you see the contradiction ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[kjv1611]

BHspicer,

I'm assuming you're working for the same company that owns the database in question? If so, you need to press for access to said database. If this is a business important function, then the business needs to allow the proper persons proper access to get the job done. You can do it via SendKeys, but what if something goes wrong? You can sit back and say I told you so, but the best option here would be to press for access to the Access database first. If it's a production database that can't be taken off line, then take a copy - it's a VERY simple operation... you take the copy, work on it, and when finished, import the tables from the "production" database to the one you've worked on, keep the current/previous "production" database somewhere as a back-up, move the new one back to the production spot, and viola! Of course, testing/debugging would have to come before swapping with the current production database.

Anyway, from your question and responses, I'd say your biggest problem right now isn't technical - it's political in your company.

 

[BHspicer]

PHV

There are, i understand, windows API's that can identify the 'open' object, in this case the Access form.

that isnt the problem.

its using the then found 'class' and using sendkeys / messege, to send a variable, value to that 'class' being an Access form is irrelevant.

so not really a contradiction, just that we are talking different things.

yes, in VBA, whether its Excel, or within Acces, i can create a session of the database and control the form, but i dont have the database details, connection string etc, or admin access to the msAccess database.

 

[PHV]

You want something like Copy in excel and Paste in access ?
If so, how your excel VBA code will know that the right control in the access form has focus ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[BHspicer]

kjv1611

i completely agree.

but i dont think we will get the access.

The 'IT' team have designed the User forms so that data can be updated in respective tables,

from Business point, they have thousands of these changes.

there is a business process in place to send data to DB admin team, but they, currently take two days plus, to make these changes.

They wont allow the VBA developers working for the business to design, write the code/scripts to format, verify, integrity check, error check, and then upload.

Yes, of course, copying database, or respective tables offline and then updating would be simple if access granted, but at this stage, unfortunately i dont think it will be.

i agree also, writing a vba macro in Excel, and looping through the values that need pasting /copying to the application, ie access form, is dangerous, as it takes human element out and relies on data checked before macro kicked off. but im sure you know how finance/business/stakeholders are, want everything now or tyesterday, and dontr always worry or think about the IT element/side.

but thank you for the suggestions

 

[BHspicer]

PHV

exactly.

as said the API call will, can identify the open/running Access sesion and the form itself.

identifying the actual control on the form will rely on form open and respective textbox having focus initially.

i just dont know how to code the api class find, or really the sendkeys messege?

ive seen little bits of code on various forums, but none really explain what i require.

 

[kjv1611]

I think you would have to just count the number of "tabs" between fields, and go with that.

I've done this myself for less important usage. In order to have somewhat better control than the built-in VBA SendKeys, I used Dev Ashish's SendKeys API. It seemed to give cleaner results than the built-in SendKeys. It worked out well for me on one system, but not so well on another system. First one was an old Pentium 3 500MHZ PC running WinXP. Second one was a Pentium 4D system running XP MCE. To this day I never found out exactly what caused the issue on the second system. I later tried it on a newer system, Core 2 Quad Q6600, running Windows Vista, not a single issue.