Bypass User Input from someone else's Macro

[Ebes1099]

I have a workbook that is interfacing with another workbook tool maintained by a separate team so I don't have authority to make changes to their tool.

A little background info on what the other team's tool does. It grabs information from a SQL into their workbook and pastes it into sheets. There is a drop down list where you select a plan and then click a button to "Load Information". This kicks off a macro, but at the start of it a UserForm pops up prompting you which server you want to connect to with 3 options (radio buttons). Then there's two buttons on the server that say "Continue" or "Cancel".

What my tool does is it batch runs their tool with a large number of plans at once, and then grabs the information I need and pastes it into my workbook. What I need to figure out how to do is bypass that UserForm every time, I can't have someone sit there and click it every time a new plan needs to be loaded. (I could be loading 500-1000 plans at a time). I know which server I want to connect to and it will always be the same.

So without me having access to change their tool, is there anything I can do to automate that UserForm selection?

Thanks!

 

[SkipVought]

Hi,

So why not do you own query to the same source and forget their workbook?

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[Ebes1099]

I don't have access to their SQL Server to do that.

Is there no way that I can bypass the user input?

 

[SkipVought]

Rather than "run their tool" just access the tables in their db any wat you like.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[PHV]

You may try the unreliable SendKeys way in conjunction with AppActivate.

BTW, why didn't you respond here: thread707-1727380 ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[Ebes1099]

Skip, I would love to be able to do that. But I can't access their tables in their db. Also, then we would be maintaining the same query in two different workbooks and they could potentially get out of sync.

I would much prefer to just run the macro in their workbook.

If there's no great way to bypass the user input, I could "ask" them to maybe insert a few lines of code to bypass that for my circumstance. I don't have any great ideas on this so I'm all ears if anyone has a better suggestion. I was maybe thinking they could add a few lines of code that check to see if my workbook is open, and if it is set a boolean to true, then put an if statement around their code that generates the userform. Anyone got a better idea, because I know that's not ideal.

 

[zelgar]

Could you ask the other team to set up another macro in their spreadsheet that didn't ask for the server (e.g., it's already defined in the macro)

 

[SkipVought]

But I can't access their tables in their db

It would seem to me if you could access their application, you could access their tables [highlight #FCE94F]AND QUERIES[/highlight]!!!

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[Ebes1099]

It would seem to me if you could access their application, you could access their tables AND QUERIES!!!

I'm just opening an Excel workbook they maintain. Plus, I still don't like the idea of replicating their logic instead of just using the existing logic. If they make any changes, then the changes would have to be done in their tool, and in my tool. I think it would be ideal to use what's already there.

 

[Andrzejek]

Another take on what Skip said, you ARE accessing their tables already thru this ‘other’ application.

“we would be maintaining the same query in two different workbooks and they could potentially get out of sync.”

I would talk to both: the ‘other’ application people and whoever is in charge of the SQL, and ask if the SQL could have just one query/view on the SQL side for both of you to access.


Have fun.

---- Andy

 

[Ebes1099]

Another take on what Skip said, you ARE accessing their tables already thru this ‘other’ application.

“we would be maintaining the same query in two different workbooks and they could potentially get out of sync.”

I would talk to both: the ‘other’ application people and whoever is in charge of the SQL, and ask if the SQL could have just one query/view on the SQL side for both of you to access.

I understand this. And I'm sure if I had some time and cooperative people there would be a much better way to do this. But I'm working on my own and I have tight deadlines so I'm trying to work with what I have.

The Macro called in the other team's application is pretty intensive and sends SQL code to SQL Server. It's got many sub and function calls so I don't want to go digging through it and try and replicate it. It's not quite as simple as send sql code, paste results into a range.

I know you guys want to help and give me the best approach, but right now using their existing macro is my only option.

So I either need to be able to automate the user input portion of their macro, or come up with some code that I can have them insert that will bypass it. And they have no time to be tweaking any of this on their own so I would need to give them specific direction on what to insert into their code if I'm asking them to do that.

 

[PHV]

Did you even tried the SendKeys approach ?

 

[Ebes1099]

Did you even tried the SendKeys approach ?

I haven't yet. I had never heard of it so I've just done a little reading on it. The form that pops up has radio buttons for the user to select, so I'll have to see if I can send a keystroke to select the radio button I want.

 

[Ebes1099]

Can you tell me how to use the workbook name in a Application.Run call instead of hard coding the path in there.

This works
Application.Run "'Book1 Test.xlsm'!Pub_btnLoadPlan_Click"

But this doesnt
Dim wb_Name As String
wb_Name = "Book1 Test.xlsm"
Application.Run wb_Name!Pub_btnLoadPlan_Click

I've tried other variations of the last line as well

Application.Run wb_Name & "!Pub_btnLoadPlan_Click"

 

[PHV]

Application.Run "'" & wb_Name & "'!Pub_btnLoadPlan_Click"

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[combo]

An alternative to Application.Run is adding a reference to their workbook (in VBE window) in your workbook. This requires unique vbproject name, and may require closing other workbooks and/or changing your workbook's vbproject name. Having reference added you can see and use its public procedures as vba globals. There are two rules for so linked workbooks: 1) when you open your workbook excel automatically opens reference, 2) you can't close reference as long as your workbook is opened. And it assumes you have fixed file name and path of reference.

combo