how do I retrieve data from another program into Excel?

[Semidevil]

so I"m learning VBA for excel, and it is not as hard as I thought actually.

I'm now able to write some simple codes, but I was wondering if there was a way to do this.

Lets say I have excel along with another program open. I want to be able to write a macro that will allow me to retrieve certain parts/data of the program and paste it into excel automatically.

is this possible? how would I go about dong that?

 

[esun]

Easiest way would be to write to a file, then have Excel read from that file. Not sure if you can pass data to Excel otherwise, though.

 

[EagleKeeper]

If your program can get at Excel via ODBC - you can address each cell and store information into it.

 

[Semidevil]

still not quite understanding it. i read a bit about the ODBC but not sure how to implement it.


Is this task as easy as defining the program name and then write simple 1 line codes for each action, or is there more to it???

example(ideas, not codes):

write a line to define the program
write a line to make it connect with eachother
write a line to navigate through and select the data I want.
write a line to copy it.
write a line to go back to excel, range("cell locaton).select
write a line to paste it.

repeat for other needed datas

Am I just simplifying this too much?

 

[EagleKeeper]

Requires multiple lines of code where you have a single line.

I am used to using C++ to get to the ODBC - but your concept is correct.

Get the data from the source.
Determine the row/col (cell) in Excel
Store the data

Loop back to the next input data.

 

[Semidevil]

another question I have is this:

in order to identify the second program, do you identify by the exact name(xxx.xx), or do I actually need to more meaningful information about the second program? Is the name of the program enough?

last question:

what does it mean when Excel and the second program 'connect' in order to retrieve data?

If the source program requires a log-in and password, do I need to consider this also in the code?


When I create a macro and doing this, am I essentially creating like a imaginary person who will need to log in and get the needed information, or am I just creating a cutting and pasting request(imagine a macro recording that will allow record your actions outside of excel).

 

[Noobsa44]

A perhaps much simpler alternative to me seems to be to just write a CSV file out and then open it up in Excel.

Also there are nice little automation programs out there that can do things like this: SaveText(window("myAppLine").window("part1").getText() & ", " & window("myAppLine").window("part2").getText()) For example, there is autoit v3 (http://www.autoitscript.com/autoit3/index.php), which could do something like this.

These solutions of course are both kludgy at best, so if you are doing this for a single time or accuracy is not critical, this maybe a good way to go, but if accuracy is important, go the ODBC route.

 

[xtknight]

Originally posted by: Semidevil
Lets say I have excel along with another program open. I want to be able to write a macro that will allow me to retrieve certain parts/data of the program and paste it into excel automatically.

is this possible? how would I go about dong that?

What kind of program would you be retrieving data from? Is this a text box you want to copy? Is this a program you made? If so, you can have that program interface with Excel.

Example VB code (for a VB app or VB script you're making, not for the macro):

---

Dim xlObj As Object
Set xlObj = GetObject(, "Excel.Application") ' obtain reference to existing Excel app

xlObj.Workbooks(1).Sheets(1).Range("A1").Value = InputBox("This data will be entered into Excel:")

---

This uses the COM object model to access Excel and interface with its methods. I'm sure it's possible with C, C++, Delphi, VB, VB.NET, C#, or any other language that supports COM/OLE.

If you need to grab the contents of a textbox from a "foreign" app, then you have to use the Windows API.

Visual Studio comes with a tool called Spy++ (there are other free window handle spies out there also) that allows you to find the Class and Window Name of the app in question. Using this information, you can look through all the HWNDS (window handles) and see which match this app. Once you have the HWND of the app, you can browse through all its controls and find the handle of the textbox. With the textbox's HWND, you can use SendMessage to retrieve the contents of the textbox and store it at a pointer, which you may access to get the text. This is all possible from within VB (and probably an Excel VBA macro) if you do it correctly.

I'm sure the app has some way to export its output (it should) so that's a far more elegant way.

 

[Semidevil]

the source is just a program that we have at work(developed by our company, I think). it contains a lot of our customers information, and lot of times we just have to verify it and document in excel.

I dont know anything about the program that we use at work, so I dont have any information on server, and whatnot. all I know is that once I log in, I am presented this screen with various menus and if it was possible to automatically import specific data in specific cells, it would help a lot.

Is this possible without going too deep into servers and stuff(since I dont know how it works anyways).

The idea was just to have excel and the progam open and just write/do a macro or something that will imitate me moving my mouse to certain parts of the program and do copy and pasting while switching back and forth between the 2 programs.

is that possible??

 

[KLin]

Originally posted by: Semidevil
the source is just a program that we have at work(developed by our company, I think). it contains a lot of our customers information, and lot of times we just have to verify it and document in excel.

I dont know anything about the program that we use at work, so I dont have any information on server, and whatnot. all I know is that once I log in, I am presented this screen with various menus and if it was possible to automatically import specific data in specific cells, it would help a lot.

Is this possible without going too deep into servers and stuff(since I dont know how it works anyways).

The idea was just to have excel and the progam open and just write/do a macro or something that will imitate me moving my mouse to certain parts of the program and do copy and pasting while switching back and forth between the 2 programs.

is that possible??

I think you're making it much more complicated that it needs to be. This sounds like a customer relationship management program. It is no doubt based on a database engine. If you can get access to the database engine itself, sucking data out of it and into Excel is pretty easy.

 

[Evadman]

Originally posted by: Semidevil
the source is just a program that we have at work(developed by our company, I think). it contains a lot of our customers information, and lot of times we just have to verify it and document in excel.

What is it called? Is it a mainframe application? Is it Attachmate or Extra? 3270 client? Powerbuilder? some random vb program?

Depending on the answer is how easy it will be. the problem is if the program does not open its objects up for use you will need to use hooks or some other interesting processes to get at the data that is displayed.

Is there a help---> about menu? If there is, what does it say in it's entirety? (leaving out any company specific or sensitive info)