Search code examples
excelvbainputbox

Skip input box or any error message

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.

Solution

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