Skip input box or any error message

Question

I run VBA from a temporary Excel workbook what it will do is "opens a macro enabled Excel in another path and extract value from cell C3 to the temporary Excel". But the problem is whenever I open Excel workbook from that path it will display two boxes.

1) Some error (I don't know that error name because macros are password protected in that Excel file otherwise suggest how to send "Enter" key, which skips this error.)

2) Input box (this case I need to just Enter, that's all.)

Is there any way to get data validation drop down values from other closed workbook without opening it.

Sub macro1()
 Dim wrk As Workbook
 Set wrk = Workbooks.Open("D:\Test\Test.xlsm") Workbooks("Book1.xlsm").Activate
 ActiveSheet.Range("h10") = wrk.Sheets(1).Range("c2").Validation.Formula1 wrk.Close SaveChanges:=False 
End Sub

Above is the code I am actually using now. But this code will open the file. I need values without opening the file.

Maybe look into [SendKeys](https://msdn.microsoft.com/en-us/library/aa266279(v=vs.60).aspx). — , Sep 19 '15 at 06:37
Can you share the screenshot of those two? [Here](http://stackoverflow.com/questions/16174469/unprotect-vbproject-from-vb-code/16176557#16176557) is a reliable way to deal with those boxes. It uses APIS instead of Sendkeys — Siddharth Rout, Sep 19 '15 at 09:34
Sub macro1() Dim wrk As Workbook Set wrk = Workbooks.Open("D:\Test\Test.xlsm") Workbooks("Book1.xlsm").Activate ActiveSheet.Range("h10") = wrk.Sheets(1).Range("c2").Validation.Formula1 wrk.Close SaveChanges:=False End Sub Above code i use it on temporary file. When try to open "Test.xlsm", it will show that 2 boxes I mentioned in the question. — S Balaji, Sep 20 '15 at 19:44

Gedeon · Accepted Answer · 2015-09-19T07:36:46.333

0

Try

Dim xlApp, xlBook, xlSht, my_file
  my_file= Application.GetOpenFilename
If my_file = "False" Then Exit Sub

Set xlApp = CreateObject("Excel.Application")
    xlApp.EnableEvents = False

Set xlBook = xlApp.Workbooks.Open(my_file)
Set xlSht = xlApp.ActiveSheet

'YOUR CODE HERE

xlBook.Close False
xlApp.Quit
Set xlSht = Nothing
Set xlBook = Nothing
Set xlApp = Nothing

The key is xlApp.EnableEvents = False, this will disable all macros on the other workbook.

Hope this helps.

edited Sep 19 '15 at 07:36

answered Sep 19 '15 at 06:39

Gedeon

44
6

Please see the changed answer. – Gedeon Sep 19 '15 at 07:35
**Thank You Gedeon Kheboyan.** It is working for me. However I **tested** this case in my **home**. But real work is I need to **test** this case in my **official file** that is in my **office**. In my **reel** **test** it skipped the input box. This is superb. But in my **real** **test** it will show 1 error message and 1 input box while file opening. So tomorrow I need to check this macro. – S Balaji Sep 20 '15 at 20:05
You are welcome. This code should skip any VBA code on the second workbook. So, I think it will not show you any error messages and inputboxes. – Gedeon Sep 22 '15 at 07:51
Excel cannot get data from other workbook without opening it. In my above-mentioned code excel will open the file in background so you will not see it. `xlBook.Close False xlApp.Quit Set xlSht = Nothing Set xlBook = Nothing Set xlApp = Nothing` this part will close the workbook after the process. – Gedeon Sep 22 '15 at 12:28
Gedeon Kheboyan I red about ExecuteExcel4Macro() function which will execute command in other excel files without opening the workbook. I don't know how to work with this function. That's why I asked. – S Balaji Sep 27 '15 at 06:43

Skip input box or any error message

1 Answers1