CODEWITHSUNDEEP
vbaapiexcel
noas
noas

Reputation: 71

New workbook opened by external app cause macro to stop

I use an application at work to make search on my company database. The application exports search results to an excel file in the “AppData\Local\Temp\” folder. I need this excel file for my main macro to work.

Normally I click the buttons in the application manually to make the search, then I discovered I can use windows api to make a macro to do this for me. Then i write this macro:

Public Declare Function FindWindow Lib "user32.dll" Alias "FindWindowA" (ByVal lpClassName As String, ByVal lpWindowName As String) As Long
Public Declare Function FindWindowEx Lib "user32.dll" Alias "FindWindowExA" (ByVal hWnd1 As Long, ByVal hWnd2 As Long, ByVal lpsz1 As String, ByVal lpsz2 As String) As Long
Public Declare Function SendMessage Lib "user32.dll" Alias "SendMessageA" (ByVal hwnd As Long, ByVal wMsg As Long, ByVal wParam As Long, ByRef lParam As Any) As Long
Public Declare Function PostMessage Lib "user32.dll" Alias "PostMessageA" (ByVal hwnd As Long, ByVal wMsg As Long, ByVal wParam As Long, ByVal lParam As Long) As Long
Public Const WM_CLOSE As Long = &H10
Public Const BM_CLICK As Long = &HF5&
Sub Makro1()

advanced_search = FindWindow("WindowsForms10.Window.8.app.0.33c0d9d", vbNullString)
window_group_1 = FindWindowEx(advanced_search, 0&, "WindowsForms10.Window.8.app.0.33c0d9d", vbNullString)
window_group_2 = FindWindowEx(advanced_search, window_group_1, "WindowsForms10.Window.8.app.0.33c0d9d", vbNullString)
search_button = FindWindowEx(window_group_2, 0&, "WindowsForms10.BUTTON.app.0.33c0d9d", "Search")
excel_button = FindWindowEx(window_group_2, 0&, "WindowsForms10.BUTTON.app.0.33c0d9d", "Export to Excel")

Call PostMessage(search_button, BM_CLICK, 0, 0)

Do
DoEvents
search_results_info = FindWindow("WindowsForms10.Window.8.app.0.33c0d9d", "Search Results")
Loop Until search_results_info > 0

search_results_info_button = FindWindowEx(search_results_info, 0&, "WindowsForms10.BUTTON.app.0.33c0d9d", vbNullString)

Call PostMessage(search_results_info_button, BM_CLICK, 0, 0)

Call PostMessage(excel_button, BM_CLICK, 0, 0)

Do
DoEvents
search_results_excel = FindWindow("XLMAIN", "Microsoft Excel - search_results")
Loop Until search_results_excel > 0

Call SendMessage(search_results_excel, WM_CLOSE, 0, ByVal 0&)

End Sub

The code runs until the following line executed. Call PostMessage(excel_button, BM_CLICK, 0, 0) After this line the excel file in the temp folder open and became active workbook, so my macro stop working.

I encountered the same problem when search results info window appear, but then I switched sendmessage to post message and the problem solved. Is there any method to make my macro run and ignores the excel file opening by the external application?

Edit: Changed Call SendMessage(search_results_excel, WM_CLOSE, 0, ByVal 0&) to Call PostMessage(search_results_excel, WM_CLOSE, 0, ByVal 0&) ThisWorkbook.Activate problem solved.

Upvotes: 0

Views: 93

Answers (1)

ChipsLetten
ChipsLetten

Reputation: 2953

Change your macro so it does not use ActiveWorkbook but instead uses either ThisWorkbook to refer to itself or Workbooks("book_name.xlsm") to refer to the workbook named as "book_name.xlsm". That way any cell or sheet references always refer to the correct workbook, regardless of what other books are open or which is the active workbook.

Upvotes: 1

Related Questions