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. |
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 |
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. |
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 |
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. |
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 |
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