ExcelBanter

ExcelBanter (https://www.excelbanter.com/)
-   Excel Programming (https://www.excelbanter.com/excel-programming/)
-   -   Help with VBA script for Excel (https://www.excelbanter.com/excel-programming/454512-help-vba-script-excel.html)

programmernovice[_2_]

Help with VBA script for Excel
 
I'm trying to write a script to navigate to the Solver check-box under References in the Editor. So far I have:

application.sendkeys (%)
application.sendkeys (L)
application.sendkeys (V)

That gets to the VBA Editor.

I cannot figure out how to code the next steps:

Click on "Tools"
Click on "References"
Click in the Solver box.

Any help or suggestion would be appreciated.





Peter T[_8_]

Help with VBA script for Excel
 
"programmernovice" wrote in message
I'm trying to write a script to navigate to the Solver check-box under
References in the Editor. So far I have:

application.sendkeys (%)
application.sendkeys (L)
application.sendkeys (V)

That gets to the VBA Editor.

I cannot figure out how to code the next steps:

Click on "Tools"
Click on "References"
Click in the Solver box.

Any help or suggestion would be appreciated.


I assume you're trying to add a Solver reference to your file..

Dim ref As Object ' Reference
Dim vbp As Object ' VBProject
Dim ad As AddIn

Set vbp = ThisWorkbook.VBProject
On Error Resume Next
Set ref = vbp.References("Solver")
If ref Is Nothing Then
Set ad = Application.AddIns("Solver Add-in")
Set ref = vbp.References.AddFromFile(ad.FullName)
End If

If this for distributing would need more than just this.

Requires trust access to the VBA Project in security settings.

Peter T



programmernovice[_2_]

Help with VBA script for Excel
 
On Thursday, December 19, 2019 at 1:38:47 PM UTC-6, Peter T wrote:
"programmernovice" wrote in message
I'm trying to write a script to navigate to the Solver check-box under
References in the Editor. So far I have:

application.sendkeys (%)
application.sendkeys (L)
application.sendkeys (V)

That gets to the VBA Editor.

I cannot figure out how to code the next steps:

Click on "Tools"
Click on "References"
Click in the Solver box.

Any help or suggestion would be appreciated.


I assume you're trying to add a Solver reference to your file..

Dim ref As Object ' Reference
Dim vbp As Object ' VBProject
Dim ad As AddIn

Set vbp = ThisWorkbook.VBProject
On Error Resume Next
Set ref = vbp.References("Solver")
If ref Is Nothing Then
Set ad = Application.AddIns("Solver Add-in")
Set ref = vbp.References.AddFromFile(ad.FullName)
End If

If this for distributing would need more than just this.

Requires trust access to the VBA Project in security settings.

Peter T


Thanks Peter. Purpose is, indeed, to add Solver reference. Is your code to be added to what I started with, or it's the whole thing? I appreciate it.

Peter T[_8_]

Help with VBA script for Excel
 
"programmernovice" wrote in message
On Thursday, December 19, 2019 at 1:38:47 PM UTC-6, Peter T wrote:
"programmernovice" wrote in message
I'm trying to write a script to navigate to the Solver check-box under
References in the Editor. So far I have:

application.sendkeys (%)
application.sendkeys (L)
application.sendkeys (V)

That gets to the VBA Editor.

I cannot figure out how to code the next steps:

Click on "Tools"
Click on "References"
Click in the Solver box.

Any help or suggestion would be appreciated.


I assume you're trying to add a Solver reference to your file..

Dim ref As Object ' Reference
Dim vbp As Object ' VBProject
Dim ad As AddIn

Set vbp = ThisWorkbook.VBProject
On Error Resume Next
Set ref = vbp.References("Solver")
If ref Is Nothing Then
Set ad = Application.AddIns("Solver Add-in")
Set ref = vbp.References.AddFromFile(ad.FullName)
End If

If this for distributing would need more than just this.

Requires trust access to the VBA Project in security settings.

Peter T


Thanks Peter. Purpose is, indeed, to add Solver reference. Is your code
to be added to what I started with, or it's the whole thing? I appreciate
it.


Your SendKeys wouldn't work, but no point trying to correct as at best
SendKeys is flakey and only for use as a last resort. Instead, with some
caveats, you can add and remove references with code, also just about
everything relating to a VBA-project. So bin the SendKeys and yes - 'it's
the whole thing'!

Or rather, it's the base essentials and might need more work, hence my
comment re if distributing.

Peter T



programmernovice[_2_]

Help with VBA script for Excel
 
On Friday, December 20, 2019 at 2:06:14 PM UTC-6, Peter T wrote:
"programmernovice" wrote in message
On Thursday, December 19, 2019 at 1:38:47 PM UTC-6, Peter T wrote:
"programmernovice" wrote in message
I'm trying to write a script to navigate to the Solver check-box under
References in the Editor. So far I have:

application.sendkeys (%)
application.sendkeys (L)
application.sendkeys (V)

That gets to the VBA Editor.

I cannot figure out how to code the next steps:

Click on "Tools"
Click on "References"
Click in the Solver box.

Any help or suggestion would be appreciated.

I assume you're trying to add a Solver reference to your file..

Dim ref As Object ' Reference
Dim vbp As Object ' VBProject
Dim ad As AddIn

Set vbp = ThisWorkbook.VBProject
On Error Resume Next
Set ref = vbp.References("Solver")
If ref Is Nothing Then
Set ad = Application.AddIns("Solver Add-in")
Set ref = vbp.References.AddFromFile(ad.FullName)
End If

If this for distributing would need more than just this.

Requires trust access to the VBA Project in security settings.

Peter T


Thanks Peter. Purpose is, indeed, to add Solver reference. Is your code
to be added to what I started with, or it's the whole thing? I appreciate
it.


Your SendKeys wouldn't work, but no point trying to correct as at best
SendKeys is flakey and only for use as a last resort. Instead, with some
caveats, you can add and remove references with code, also just about
everything relating to a VBA-project. So bin the SendKeys and yes - 'it's
the whole thing'!

Or rather, it's the base essentials and might need more work, hence my
comment re if distributing.

Peter T


OK. Does the Solver reference apply to all macros on all Workbooks, or does it have to be added on each Workbook? Many thanks again for helping me out.

Peter T[_8_]

Help with VBA script for Excel
 

"programmernovice" wrote in message
...
On Friday, December 20, 2019 at 2:06:14 PM UTC-6, Peter T wrote:
"programmernovice" wrote in message
On Thursday, December 19, 2019 at 1:38:47 PM UTC-6, Peter T wrote:
"programmernovice" wrote in message
I'm trying to write a script to navigate to the Solver check-box
under
References in the Editor. So far I have:

application.sendkeys (%)
application.sendkeys (L)
application.sendkeys (V)

That gets to the VBA Editor.

I cannot figure out how to code the next steps:

Click on "Tools"
Click on "References"
Click in the Solver box.

Any help or suggestion would be appreciated.

I assume you're trying to add a Solver reference to your file..

Dim ref As Object ' Reference
Dim vbp As Object ' VBProject
Dim ad As AddIn

Set vbp = ThisWorkbook.VBProject
On Error Resume Next
Set ref = vbp.References("Solver")
If ref Is Nothing Then
Set ad = Application.AddIns("Solver Add-in")
Set ref = vbp.References.AddFromFile(ad.FullName)
End If

If this for distributing would need more than just this.

Requires trust access to the VBA Project in security settings.

Peter T

Thanks Peter. Purpose is, indeed, to add Solver reference. Is your
code
to be added to what I started with, or it's the whole thing? I
appreciate
it.


Your SendKeys wouldn't work, but no point trying to correct as at best
SendKeys is flakey and only for use as a last resort. Instead, with some
caveats, you can add and remove references with code, also just about
everything relating to a VBA-project. So bin the SendKeys and yes - 'it's
the whole thing'!

Or rather, it's the base essentials and might need more work, hence my
comment re if distributing.

Peter T


OK. Does the Solver reference apply to all macros on all Workbooks, or
does it have to be added on each Workbook? Many thanks again for helping
me out.


You can't add references for individual macros or modules, only to
workbooks. So add the reference to all workbooks that include code that
refers to routines in the Solver workbook.

Did you try the example I posted?

PT



programmernovice[_2_]

Help with VBA script for Excel
 
On Friday, December 20, 2019 at 4:20:30 PM UTC-6, Peter T wrote:
"programmernovice" wrote in message
...
On Friday, December 20, 2019 at 2:06:14 PM UTC-6, Peter T wrote:
"programmernovice" wrote in message
On Thursday, December 19, 2019 at 1:38:47 PM UTC-6, Peter T wrote:
"programmernovice" wrote in message
I'm trying to write a script to navigate to the Solver check-box
under
References in the Editor. So far I have:

application.sendkeys (%)
application.sendkeys (L)
application.sendkeys (V)

That gets to the VBA Editor.

I cannot figure out how to code the next steps:

Click on "Tools"
Click on "References"
Click in the Solver box.

Any help or suggestion would be appreciated.

I assume you're trying to add a Solver reference to your file..

Dim ref As Object ' Reference
Dim vbp As Object ' VBProject
Dim ad As AddIn

Set vbp = ThisWorkbook.VBProject
On Error Resume Next
Set ref = vbp.References("Solver")
If ref Is Nothing Then
Set ad = Application.AddIns("Solver Add-in")
Set ref = vbp.References.AddFromFile(ad.FullName)
End If

If this for distributing would need more than just this.

Requires trust access to the VBA Project in security settings.

Peter T

Thanks Peter. Purpose is, indeed, to add Solver reference. Is your
code
to be added to what I started with, or it's the whole thing? I
appreciate
it.

Your SendKeys wouldn't work, but no point trying to correct as at best
SendKeys is flakey and only for use as a last resort. Instead, with some
caveats, you can add and remove references with code, also just about
everything relating to a VBA-project. So bin the SendKeys and yes - 'it's
the whole thing'!

Or rather, it's the base essentials and might need more work, hence my
comment re if distributing.

Peter T


OK. Does the Solver reference apply to all macros on all Workbooks, or
does it have to be added on each Workbook? Many thanks again for helping
me out.


You can't add references for individual macros or modules, only to
workbooks. So add the reference to all workbooks that include code that
refers to routines in the Solver workbook.

Did you try the example I posted?

PT


I did and it works perfectly. Not that I would expect less from you. You obviously have high expertise, but more importantly, you are willing to share it with others.


All times are GMT +1. The time now is 03:01 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com