Importing (or auto-pasting) external data into existing worksheet.

[Piohrun]

Hi,

First, I would like to say that I'm new to this forum, and relatively new to "advanced excel" ideas. I've recently started a new job where I work with excel on a daily basis so I decided to educate myself. Now to my question (and it's a complicated one, so please bear with me).

I need to either find or write myself a tool or script or macro or maybe something else entirely, with a very specific task in mind. Said tool would have to speed up the process of importing data from another program into an existing excel sheet.

Now the tricky part. Formentioned program is a badly written piece of software, where the data that I need, exists in such a table where it is impossible to select all the cells at once. I can only select one cell at a time, and then copy it's content, swith to my excel sheet, and paste it in a specific column. Normally it would be just a matter of a few ctr+c, ctr+v, but in my case those tables can consist of up to few hundred rows (and unfortunetelly the number of rows varies from case to case).

I'm not sure if I can do it with excel (and vba) alone, and maybe I need to look at some more general-purpose programming languages. Or maybe I'm missing some very simple and elegant solution. I just hope that by asking the question as broad, I'm not breaking any forum rules.

Thank you in advance for any help and for taking the time to even read this question.

 

[Smallman]

Hi Piohrun

Welcome to the forum. Your problem is indeed a difficult one mainly because of the unknown. This other system is the issue. You may be able to generate something that pulls information out of that program but without knowing the program we will be in the dark a bit. We have Excel programs at work for example which extract data from SAP but that married beautifully with Excel. So what is this other program. Someone may have worked with it before. A bit more info will be useful to your cause.

Take care

Smallman

 

[Piohrun]

Hi Smallman,

Thank you for the reply.

The program I am talking about is called aquila administrator (http://www.aquilauk.co.uk/index.php). I'm not sure what version of it I'm using. And the reason why I thought the program's name is irrelevant is that while I'm sure that aqula is using some kind of database, and I understand that pulling the data from the database may be easier than from the GUI, thing is that I do not have access to the database itself.

And I won't any time soon. While I'm working, the software is pulling only a fraction of of the data, that is relevant to my case, and for personal information security reasons, I can't have access to the whole database, only that small part that I require to finish given task.

That is why I need something that would allow me to pull the data from this unfortunatelly written GUI. It does not have to be fully automatic - autopasting selected number into desired excell worksheet would be a great help already. The smoother and faster, the better.

 

[NARAYANK991]

Hi ,

The link you have posted , says this :

Many of our successful implementations have been achieved through a suite of pre-defined standard interfaces which enables faster and lower risk integration. In addition, all administrator functionality is available via Application Programming Interfaces (APIs) using Web Services which allows for the most flexibility of functional integration. The technical architecture is clearly structured for high performance and open access, allowing high scalability, both vertically and horizontally in the enterprise environment.

Is it possible you can find out about these APIs ?

Knowing them would help to write an Excel macro to do what you want.

Narayan

 

[Piohrun]

To be honest, I don't know.

The website says about Web Services, and while those are probably available to few people in the company I work for, unfortunately (and if you ask me, it's understandable) company policy does not allow new joiners to tinker with the crucial software.

If it is possible to somehow find out abouth these API's from the program itself, I will look for it. But I can't promise anything.

Like I said before, I was hoping for a tool that would allow autopasting the content selected from aquila's GUI, into excel's workbook, one cell below the previous one - one by one. That would still make the copy - paste cycle even few seconds faster with every row, and a lot faster overall.

 

[Marc L]

Hi,

if you can manually paste data in Excel from source application,
you can use Excel's Macro recorder to start your code.

If you can't, no further help possible, may be considered as piracy !
​

 

[Piohrun]

What? Piracy?

I'm not sure if you are following where the problem lies. I can copy and paste the data from source software to excel manually, but I can only do it one cell at the time. It is due to the nature of this "table" and it's cells - they appear as seperate 'windows' withing the GUI.

Can I really target that other program with my macro's VBA language? And even if I can target some very specific window of aquila, and a very specific cell within that window, will it work with another case, on another set of data? Or do I have to rewrite the macro every time?

 

[Marc L]

So if it's a need for your work, why not asking an export to source software administrator ?

The other way is to obtain documentation on source software API …​

 

[Piohrun]

Good question. Problem is that the mindset among the people in charge in the company I work for is ... special. I'm new to this job, so every iniciative that comes from me to improve the process meets this "you don't have enough experience, it's out of your scope" etc. wall.

But nevermind then. If it is really impossible to speed up the process of copying the data from external gui driven software, into the prepared excel sheet, then I'm just wasting your and my time. API's are out of my reach, and noone is going to give me access to them any time soon, software people are too busy with other stuff to bother with some random quys query. Good game and thanks for your time. I'll stick to copying those numbers manually.

 

[Marc L]

With such internal politics, I have only one advice : change company as soon as possible !

________________________________________________________________________
Management is doing things right, leadership is doing the right things. (Peter Drucker)

​