Running an excel macro from Attachmate Extra!

[Spike48]

Hey guys, I've been lurking around these forums for a while and steadily increasing my knowledge thanks to it, but now I need some direct help. I'm fairly new to vba and coding in general, so I have some trouble understanding pieces of code I find here and on other forums that I then try to adapt for my own needs.

The problem:
I'm currently running an Excel 2010 macro that imports a bunch of stuff from the currently open Attachmate session, does things with it and the end result then gets pasted somewhere else. It works just fine, I decided to do the macro in excel because I find excel vba much easier to work with than extra basic, which I don't really know at all. However, I *need* to spare the average Joe of an user the trouble of opening excel and starting a macro from there every single time; it would simply be much more convenient if the macro could be started from Attachmate. I'm sure it's doable, however, I have no idea how to do achieve this.

I found an older thread where someone had a similiar problem and apparently fixed it (

http://www.tek-tips.com/viewthread.cfm?qid=1669601

). I do not know how to adapt the solution for my own needs, however. Please mote that my macro does not start automatically when I open the excel file and I don't want it to (unless there's no other way to do it, other than closing it and reopening it via macro every time).

Let's assume the file with the macro is called 1.xlsm and is stored on C:\excel\macro. The macro is in "module1" and is called "macro1". If someone could help me adapt the solution / create a new one, it'd be fantastic. How should the extra basic code look like? Thanks!

 

[SkipVought]

Hi Spike48 and welcome to Tei-Tips!

You have described how I, and no doubt thousands of others, have been introduced to Tek-Tips: lurk, learn 'n' leap! This is my go-to site for all things nerdy.

I'm a bit confused, as you seem to want to avoid using Excel at all, since it would appear that no data from your terminal emulator ends up in Excel. But then you refer to a thread that discusses a statement that opens Excel from Extra.

So you want to write an Extra macro that scrapes some data and does WHAT with it?

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[Spike48]

Thanks for the warm welcome

I've already written that part of the macro. I'm using the getstring function to get the export the data I want into excel. The macro checks for the last page and continues to scrape the data I need until it hits the last page. It then arranges (using excel vba almost exclusively from this point onward) the data according to a whole set of criteria; It deletes rows of data the user does not need for the final report based on some values, converts negative values into zeroes (if needed), looks up some additional data in Attachmate (if needed, according to certain values) using sendkeys / screen.search / getstring and pastes it in appropriate fields in the excel sheet. When it's done it produces an excel table with all the necessary data neatly arranged, which is then copied into Outlook / Clipboard, depending on user's need. I do need the excel functionality (there's plenty of loops, if statements, cases, cell-moving, cell-formatting etc. involved, plus that way once the final report is copied over to a different application it retains it's formatting and it has to look neat - especially when e-mailed).

What I need is a "macro" / vb extra script that will allow me to run the above-mentioned macro directly from attachmate (it will open the excel file storing the macro and run it, all in background). When I was first attempting the above-mentioned macro I tried to do it in attachmate - to still import data into excel, but somehow run all the functionality I need from attachmate, but it soon became clear doing it the other way around was much, much easier for me.

 

[SkipVought]

Why can't the Excel procedure run on the Workbook_Open event?

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[Spike48]

That's actually not that big of a no-no. The macro has to be run seperately for each (at least at this point in time) from a number of accounts, daily (200 or so), which I thought might have been a problem. I actually gave it some more thought, though, and I do not believe closing / reopening the file would be that much of an issue. Still, I would run into the same issue the original poster in the thread I linked to ran into, wouldn't I? How do I adapt his code (because frankly, I have no idea what it does)?

 

[SkipVought]

from a number of accounts, daily (200 or so)...

Does accounts mean UserIDs or Accounts within the database that the emulator is the front end for?

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[Spike48]

Accounts within the database.

 

[SkipVought]

What if you had a list of accounts from which to run? Could you loop thru the list to accomplish the 200 or so?

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[Spike48]

I could, not but that's not currently possible due to how and when (as in not on the same time) the accounts are delivered and how fast they need to be processed. I'm working on changing that input method in the future, so that the task could be fully automized.

 

[Spike48]

I can't edit the past, but I wanted to clarify the "how" portion - they're delivered on paper, user has to enter them manually.

 

[SkipVought]

So another alternative, using Excel as the front end, which I am obvoiusly driving toward, might be using that Account list as a selection for running individual accounts, or having the list grouped, in order that a certain group might be selected to run that particular group of accounts.

and how fast they need to be processed...

It seems to me that selecting an account or a group might be a lot faster than the manual method of selecting accounts in the emulator!

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[Spike48]

The thing is there's no *list*. There's 1 account per form delivered, each form delivered on different time, and each one needs to be processed immediatelly upon delivery and then sent to a different recipient (mostly via e-mail).

 

[SkipVought]

So the account on demand could be ENTERED in Excel, upon which the process could be initiated.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[Spike48]

I don't see a reason why not, at least as of now. There are some things a user would have to check manually, but the macro can signal the need to do that, so that would be ok. How would you go about it? Something like an inputbox that would star the macro? A userform of some kind?

 

[SkipVought]

A CELL on a worksheet and a BUTTON. Does not have to be complicated.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[remy988]

hi Spike48,

Skip wrote

Why can't the Excel procedure run on the Workbook_Open event?

.

i don't understand why you could not use this?