manipulate other program using excel macro

[Billy]

I create a report in program, which has the capability to "Download to
Excel". The result is a windows document that looks like xl and has
some of the same commands. I then copy this data, select the workbook
[XL2003] I want to paste the data to, and run simple macro that
appends the data to an excel database. Is there anyway to automate the
generating of the report [have to select which report, then the start
date and end date, run report, then "Downolad to Excel".]
Also , I have several pivot tables accessing the database data. But
when i add more data, the pivot tables do not update. what must i do
to make them update with the new data?

Thanks,

Tonso

 

[Walter Briscoe]

In message <[email protected]
..com> of Fri, 30 Dec 2011 09:49:49 in microsoft.public.excel.programming

I create a report in program, which has the capability to "Download to
Excel". The result is a windows document that looks like xl and has
some of the same commands. I then copy this data, select the workbook
[XL2003] I want to paste the data to, and run simple macro that
appends the data to an excel database. Is there anyway to automate the
generating of the report [have to select which report, then the start
date and end date, run report, then "Downolad to Excel".]

Possibly! You need to find out if "program" can be automated.

An example of a program which can partially be automatically controlled
is the browser Internet Explorer.

A partial example of such a control, which I can run in VBA from Excel
is

Public sub foo()
Dim IE as object

Set IE = CreateObject("InternetExplorer.Application")
IE.Visible = True
end sub

That opens Internet Explorer and makes the window visible.
I won't bother showing any more. You can Google if interested.

Also , I have several pivot tables accessing the database data. But
when i add more data, the pivot tables do not update. what must i do
to make them update with the new data?

Once you have learnt if it is possible to automate access to "program",
it should be easy to do the rest of your requirements.

If I had your requirement, I would leave running "program" as a manual
exercise and write code to read the data into Excel and update the pivot
tables.

In Excel 2003, Tools/Macro/Record a Macro allows you to translate manual
operations into VBA code. It is not 100%, but I would say it is in the
high nineties. Start by doing a small, tedious operation which you fully
understand and extend from there. One gotcha is that you have no access
to the mouse when recording macros; you have to use equivalent keyboard
shortcuts. Another is that Microsoft views VBA code as insecure. You
have to persuade it to accept your code. Other Excel versions have
similar capabilities with different access mechanisms - I have heard the
word ribbon used in such contexts.

Thanks,

Tonso

I suggest you try to find out how to do something simple with macros,
which solves part of your problem. When you hit walls, ask specific
questions if you can't find solutions with Google.

OTOH, you can ask a specific question and somebody may roll you a
solution. It may serve, but you will learn little.

 

[Billy]

In message <[email protected]
.com> of Fri, 30 Dec 2011 09:49:49 in microsoft.public.excel.programming

I create a report in program, which has the capability to "Download to
Excel". The result is a windows document that looks like xl and has
some of the same commands. I then copy this data, select the workbook
[XL2003] I want to paste the data to, and run simple macro that
appends the data to an excel database. Is there anyway to automate the
generating of the report [have to select which report, then the start
date and end date, run report, then "Downolad to Excel".]

Possibly! You need to find out if "program" can be automated.

An example of a program which can partially be automatically controlled
is the browser Internet Explorer.

A partial example of such a control, which I can run in VBA from Excel
is

Public sub foo()
    Dim IE as object

    Set IE = CreateObject("InternetExplorer.Application")
    IE.Visible = True
end sub

That opens Internet Explorer and makes the window visible.
I won't bother showing any more. You can Google if interested.

Also , I have several pivot tables accessing the database data. But
when i add more data, the pivot tables do not update. what must i do
to make them update with the new data?

Once you have learnt if it is possible to automate access to "program",
it should be easy to do the rest of your requirements.

If I had your requirement, I would leave running "program" as a manual
exercise and write code to read the data into Excel and update the pivot
tables.

In Excel 2003, Tools/Macro/Record a Macro allows you to translate manual
operations into VBA code. It is not 100%, but I would say it is in the
high nineties. Start by doing a small, tedious operation which you fully
understand and extend from there. One gotcha is that you have no access
to the mouse when recording macros; you have to use equivalent keyboard
shortcuts. Another is that Microsoft views VBA code as insecure. You
have to persuade it to accept your code. Other Excel versions have
similar capabilities with different access mechanisms - I have heard the
word ribbon used in such contexts.

Thanks,

Tonso

I suggest you try to find out how to do something simple with macros,
which solves part of your problem. When you hit walls, ask specific
questions if you can't find solutions with Google.

OTOH, you can ask a specific question and somebody may roll you a
solution. It may serve, but you will learn little.

Walter,

Thank you very much. I actually have the code for pasting the data and
appending it to the xl database, but not for manipulating the other
program. I figured it would require a level of expertise beyond mine,
but was curious as to how it might be accomplished. I agree that the
simplest solution, perhaps the best, would be to continue as is, that
is, the data being manually generated in the other program, then
copied to the clipboard. At that point, my macro takes over. In any
event, thank you for taking the time to explain the situation to me.

Tonso