SendKeys to Excel Input Box (VBA)

Question

What I'm trying to do is run a VBA file that opens an excel workbook, starts a macro with an input box, enters text into that input box, presses enter, and then the macro continues to run.

What I have so far is this with "GetData" being the name of the macro:

strPath = "C:\Users\Test\Desktop\ExcelFile.xlsm" 
strMacro = "GetData"

Set objApp = CreateObject("Excel.Application") 
objApp.Visible = True

Set wbToRun = objApp.Workbooks.Open(strPath) 
objApp.Run strMacro

objApp.SendKeys "Sample"
objApp.SendKeys "~"

The problem I'm having is that SendKeys doesn't activate until after the macro is done running. After the Input Box appears, it just stays there until I close it. This results in the macro doing nothing then the word "Sample" being entered into a cell then "Enter" being pressed.

My question is this: how do I get SendKeys to enter values into the Input Box?

can you Change the macro 'strmacro' into a function like strfunction(inputboxtext as string), and then when calling the function send the text you want in the Inputbox? of Course there is no Need for an Inputbox anymore then. — Max
– Max, Commented Oct 10, 2017 at 16:16

braX · Accepted Answer · 2017-10-10 15:16:15Z

0

You dont use SendKeys on an InputBox, you just specify a default value:

x = InputBox("Prompt", "Title", "Default Value")

If that does not meet your needs, then it gets a bit more complicated. Have your calling program write the value to a file before it calls the macro, and then have the macro read that file into a variable that the InputBox then uses as its default value.

But if it's also "pressing enter" then what is the point in using an InputBox in the first place? It seems pointless.

edited Oct 10, 2017 at 15:16

answered Oct 10, 2017 at 14:49

braX

11.9k5 gold badges24 silver badges38 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

SendKeys to Excel Input Box (VBA)

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related