Copy And Paste Between A Database And

[Nexus]

Hi Folks;

 

I'm from Germany (thus please don't mind my bad English) and I'm trying to decide whether or not this tool is right for me. I downloaded the demo of it and it seems quite interesting, however there is a lot to work through. So instead of spending a few hours looking through this I wanted to know a couple things I hope the community can help me with.

 

I have an MS-Access database table containing about 2000 addresses, that I can't import to an other address management application. Thus I was thinking to use Macro Express to read the single database fields (i.e. Name, Street, zip, city and so on) of each record set and copy them step by step to the corresponding fields in the address form of the other application and save them there. This procedure should be repeated 2000 times to transfer the whole address information to the new application.

Of course it is also an option to export the database table to an comma separated ASCII file and then copy the data step by step to the corresponding fields in the address form of the other application.

 

I looked through the "Learn by example" Division on the Macro Express homepage and searched in the forum, but didn't find a fitting macro example. I think it should be possible, but I don't have an idea how to realize and especially how to program it.

 

Did someone already programmed such a macro?

Or may be the community can help me with a source code example, that I can adjust to my special requirements?

 

Thanks in advance!

NEXUS

[paul]

I have an MS-Access database table containing about 2000 addresses, that I can't import to an other address management application.

Access has comprehensive export facilities and formats, so I can only infer that your other address amanagement application is not capable of importing anything.

Assuming you have opened your Access table/query of 2000 records, each of which has 12 fields to export, you'll need to write a macro structured as follows:

Repeat 2000 times

Move to first/next Access row

Move to first/next other application "row"

Repeat 12 times

Activate Access

Select Access column and copy contents to clipboard

Move right to next Access column

Activate other application

Focus on column and paste new value from clipboard

Move to next column

End Repeat

End Repeat

 

Does this help?

[kevin]

Another alternative would be to use Access to export you data into a .csv ASCII format. Then use the ASCII Text File Begin/End Process commands to enter the information into the new program. Something like this:

// Add macro commands to launch your application here


ASCII File Begin Process: "test.csv" (Comma Delimited Text )
 
 // Add macro commands to open a New record
 
 // The variables T10, T11, T12, etc contain the information for this record.
 // There will be one variable for each field in in your record.
 //  
 // Use Text Type commands to enter the information into your new database
 // You may need to enter them in a differnt order than they came out of Acess.
 
 Text Type: %T10%<TAB>
 Text Type: %T15%<TAB>
 Text Type: %T11%<TAB>
 Text Type: %T14%<TAB>
 Text Type: %T13%<TAB>
 Text Type: %T12%<TAB>
 Text Type: %T16%<TAB>
 Text Type: %T17%<TAB>
 Text Type: %T18%<TAB>
 Text Type: %T19%<TAB>
 Text Type: %T19%<TAB>
 
 // Add macro commands to save the record
 
ASCII File End Process

You may be able to combine the Text Type commands into fewer separate commands. You may need to insert delays between the Text Type commands or use the Keystroke Speed command. It is likely that you will need to give the program a moment to keep up with the data entry.

 

You will likely need an even longer delay after saving the record.

 

This technique does not require that you know how many records are in the original data to begin with. It also does not require that you switch between Acess and the new database.

[jowensii]

I've used Macro Express to input large amounts dirty data (data not easily cleaned up using queries and so I want to manually review before entry) into Access on several occasions. I just have ME capture the data (either from a CSV file or copy data in fields of other application), and then manipulate and display that captured data in a ME message box. If I like what I see, ME enters the data either into a table/form or creates a CSV file for importing into Access.

 

I've been using ME for several years and can usually write a quick macro to accomplish this task. Someone with less experience may have more difficulty but it certainly is possible and worth exploring. I would definitely recommend Macro Express.

[paul]

Another alternative would be...

Ha, Kevin's suggestion is very much better than mine, and will work much more reliably!

[Nexus]

Thanks for your help and proposals.

I did it the way Kevin suggested. It works perfectly.

 

Best greetings

 

NEXUS