Excel InputBox

[MrK-Hans]

Hello All,

I have an Excel spreadsheet which has been given to me that requires some automation. It’s the usual stuff, like opening the spreadsheet, adding filters to columns, copying and pasting some data and lastly saving the file to a new location with today’s date.

For the most part I have successfully taken care of the above, however my stumbling block is when the spreadsheet opens. There are 3 VBA Input Boxes which are expecting numerical values (week numbers) before displaying the relevant data on Sheet1.

When the first Input Box appears, the cursor is flashing in the correct field ready for input however I don’t seem to be able to get AutoIT to send data because Excel hasn’t finished opening completely so the next step won’t process.

Has anyone else experienced something similar and managed to overcome it without manipulating the VBA in Excel?

Thanks for your time.   

[water]

Welcome to AutoIt and the forum!

If you can provide an example workbook plus the AutoIt script you run, we can play with it and might provide a solution

[MrK-Hans]

Thanks for the warm welcome and your reply. 

As requested I've attached the snippet of code my post refers to and an example spreadsheet which should help replicate the issue.

I look forward to hearing your thoughts. 

dashboard.txt

YTD Dashboard Data with Comp UK - 2018.xlsm

[faustf]

i notice  , you  use  in bad  mode .., sorry very bad mode , a naming  file  and  path  

please  read  this 

https://library.stanford.edu/research/data-management-services/data-best-practices/best-practices-file-naming

in particular :

• Try not to make file names too long, since long file names do not work well with all types of software.
• Special characters such as  ~ ! @ # $ % ^ & * ( ) ` ; < > ? , [ ] { } ' " and | should be avoided.
• When using a sequential numbering system, using leading zeros for clarity and to make sure files sort in sequential order. For example, use "001, 002, ...010, 011 ... 100, 101, etc." instead of "1, 2, ...10, 11 ... 100, 101, etc."
• Do not use spaces. Some software will not recognize file names with spaces, and file names with spaces must be enclosed in quotes when using the command line. Other options include:
  • Underscores, e.g. file_name.xxx
  • Dashes, e.g. file-name.xxx
  • No separation, e.g. filename.xxx
  • Camel case, where the first letter of each section of text is capitalized, e.g. FileName.xxx

 

second  not use send , but  before  use  Autoit v3 Info  and look  a class object , and  after use controlsend , try and  send a code 

look also a help in controlsend 

Edited November 29, 2018 by faustf

[faustf]

3 , the problem  is , when open excel and open a instance #32770 , the script stopped , and  wait your input , it is  inevitable , for bypass this problem before open excel you must  run another exe , (example name  2.exe)   and stay on listen when  is open excel and appear a windows #32770  use control send and  send a code , after this exit , and  first script restart to run , this is a  one solution 

but remember  the class #32770 is generic clas also when you open a folder for input in site  a file use the same class #32770

therfore  remember when run script  runn it alone  without other  open

  i suggest use a  virtualmachine  like  virtualbox 

good luck

 

Edited November 29, 2018 by faustf

[MrK-Hans]

Hello faustf, thanks for your reply. 

I take on board your first and second point and will amend where possible.
Your 3rd point and suggestion is a great idea, I will try that as soon as I get change and and post my findings.  

[water]

I played with your example and noticed that you can't have both at the same time. VBA automation run by Excel and AutoIt automation done by your script.
_Excel_BookOpen waits until the VBA has finished. Period.

Either do everything in VBA or remove/inactivate the VBA code and then use AutoIt.

[MrK-Hans]

Hello water, 

Thanks also to your reply. 
As suggested, in the end I had the creator of the spreadsheet allow me access and I added SendKeys statements to autocomplete the inputBox information. This works for now since the end user advises that the week numbers will remain constant (1 and 52).  

That said, I was able to successfully simulate faustf's idea of adding a second script looking out for an instance of class ID #32770 and it worked perfectly. Unfortunately the machine that will run the script has other instances of the same class id running.

If the end user changes their mind (very possible) about what week numbers need to be entered, I will invest some time and create a virtual machine for this, along with using a text file for entering the week numbers that the end user can manipulate.  

Again thank you both for your prompt replies. 

 

[water]

Glad you got it working

Having a file to store the week number is a good idea.
To solve the class ID problem I would not only look for the class ID but for some unique text in the window as well. Maybe this allows to grab the correct window to automate