How to export from Excel to another programme?

[hutia]

I have a list of pat_ids and I would like to set up a macro in Excel so that for each corresponding pat_id in another programme (a clinical system) it adds a code for example "9N" in the records.
I would appreciate if someone could help.

[Sisyphus]

Hello hutia,
This task is divided in two parts:
A) You wish to add remarks to a non-Excel record.
B) You need a data feed from Excel which tells which records to mark in which way.

From this sequence of events you can see that writing a program that can add remarks to the non-Excel records isn't an Excel job at all. Excel can't write to another application's records. Excel can provide the data required by the program that does the writing.

Many programs can read data from an Excel file. All programs can be made to read data from a comma delimited file which you can easily product from any Excel file, just by saving it in CSV (Comma Separated Values) format. This will be a machine readable file, meaning, it must conform exactly to the requirements of the program that writes the remarks. There is no point in thinking about how to extract the data. Rest assured it can be done once you have the requirements.

[hutia]

Thank you for clarification.
I just wonder, is it possible to write a simple programme in VBA so that it takes the pat_id from Excel CVS file and file it to pat_id read_term field in our clinical system?
I have heard that one uses VBA for such things, but I haven't come across a useful but simple tutorial.

[Sisyphus]

Hello hutia,
The answer is hard to give truthfully. Your other system is some kind of database and most likely it is possible for VBA to write to its tables. But such a database consists of at least a few dozen tables which are carefully arranged to interact with each other and the forms that are its user interfaces. Only if you use the progam's own user interface can you be sure not to cause severe damage. If one can presume that there is a field on one of the screens of that system which is either blank or self-creating, and further presume that you can identify the table and cell that is linked to that particular field on the screen, then you might try and use VBA to write to that cell. But there is a good chance that the system will reject this interference, not allow you access or act in some other interruptive or even corruptive way. Databases are sensitive, to say the least, and, if they are mature, they are also protective of anything that might upset them, especially outside programs that try to write to their tables.

The answer is to create the program that writes to it not in Excel but within itself. It is quite possible that the system uses MS Access where VBA is built-in. But I strongly suggest that you give up the idea of accessing tables in that system from outside. This should be a one-way street. The system can import whatever it wants but no one can export to it.

[Sisyphus]

Hello hutia,
I think I have hit on the right way to explain this thing:

Imagine Excel as the customer and the other program as the store.
Now, even if the customer has the exact change to pay for his purchase the store will not like him to open the cash register and put his money inside. There are security and other concerns. So, like I said, it is a one-way street. The store must open the cashier's counter. The customer must queue up, cash in hand. And only when the cashier is ready can the money be handed over, and no one but the cashier himself will put it in the tray.

Not that the customer couldn't do that, perhaps, but he may not know which keys to press to ring up the sale correctly and even if he did ... that isn't the way it should be.

[hutia]

Oh well.. I'll have to do 500 record manually then...still - could've been 5000!
Thank you very much for your explanations. I would still want to learn how to use VBA, but maybe I will find myself a different project...

[Sisyphus]

Hi,
Why so dejected, Hutia?
Don't you have access to the other program? If they want to let you export data to it from Excel, they should be happy to let you get at the same task from the other end. What is the name of that application? Which software is being used to run that database?
Google for "VBA for beginners". You will get plenty of help learning.

[charles]

Hi,

My 2 cents worth. If the user was able to access the database what he wants to do is do able. When i worked for "Chicos" I created a program that pulled info from their database so that they could generate reports. So basically what I'm saying is if "Excel" can see it then it's possible to add info to the data base.

[Sisyphus]

Hello Charles,
Pulling data from a foreign database is a totally different can of worms from putting data there. What I am saying is that it's a one-way street. You can go down the wrong way but the cops usually wait at the other end.

[hutia]

We use a remote Vision server, and can only view the database of Vision (clinical programme from INPS company).
but I'd like to create something using VBA and then ask them to let me install it from their end.
I don't know how to start writing a code in VBA. Could you give me an example?
I want to take data from let's say row A (with pat_id) and file the code (let's say A5 which is in row B) into drive P where they keep Data and the field to write into is: read_code.
How do I start writing a VBA code for such a task?
(-: