Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Old December 18th 19, 10:13 PM posted to microsoft.public.excel.programming
external usenet poster
 
First recorded activity by ExcelBanter: Apr 2018
Posts: 29
Default 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.





  #2   Report Post  
Old December 19th 19, 08:38 PM posted to microsoft.public.excel.programming
external usenet poster
 
First recorded activity by ExcelBanter: Jul 2019
Posts: 36
Default 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


  #3   Report Post  
Old December 20th 19, 08:10 PM posted to microsoft.public.excel.programming
external usenet poster
 
First recorded activity by ExcelBanter: Apr 2018
Posts: 29
Default 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.
  #4   Report Post  
Old December 20th 19, 09:06 PM posted to microsoft.public.excel.programming
external usenet poster
 
First recorded activity by ExcelBanter: Jul 2019
Posts: 36
Default 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


  #5   Report Post  
Old December 20th 19, 09:12 PM posted to microsoft.public.excel.programming
external usenet poster
 
First recorded activity by ExcelBanter: Apr 2018
Posts: 29
Default 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.


  #6   Report Post  
Old December 20th 19, 11:20 PM posted to microsoft.public.excel.programming
external usenet poster
 
First recorded activity by ExcelBanter: Jul 2019
Posts: 36
Default 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


  #7   Report Post  
Old December 20th 19, 11:28 PM posted to microsoft.public.excel.programming
external usenet poster
 
First recorded activity by ExcelBanter: Apr 2018
Posts: 29
Default 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.


Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Passing SQLPlus script location using Windows Script Host Object Model Scott Spence Excel Programming 11 February 2nd 12 02:41 PM
help with EXCEL SCRIPT Nastech Excel Discussion (Misc queries) 0 October 23rd 08 09:21 AM
help with EXCEL SCRIPT Nastech Excel Discussion (Misc queries) 0 October 20th 08 04:54 AM
Excel to SQL Script Budget Programmer Excel Programming 3 July 26th 06 04:15 PM
Excel 2000/XP script to Excel97 script hat Excel Programming 3 March 2nd 04 04:56 PM


All times are GMT +1. The time now is 07:22 AM.

Powered by vBulletin® Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright 2004-2020 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Copyright © 2017