excel for mac vba extract data from closed workbook

If there is a good way to determine which page you're on purely by text (such as the page number at the bottom of page a, followed immediately by the header at the top of page b) then you might look at copying the entire text of the document into one string, then using the clues from the text to decide which page to extract once you find a match. This is still cumbersome because you have to open each page in a new document. If InStr(1, strTxt, arrSearch(i)) > 0 Then 'Compare each element of the array to the stringįor i = LBound(arrSearch) To UBound(arrSearch) StrTxt = objData.GetText 'Now you can search the entire content of the page at once, within memory 'You may have to insert a waiting function like sleep() here to wait for the action to complete

'You can also do this with the JavaScript object: objjso.ExecMenuItem('Item Name') Public Declare PtrSafe Function CloseClipboard Lib 'user32' () As LongĪrrSearch(i - 2) = Sheets('Sheet1').Cells(1, i) Public Declare PtrSafe Function Empt圜lipboard Lib 'user32' () As Long Public Declare PtrSafe Function OpenClipboard Lib 'user32' (ByVal hwnd As Long) As Long Instead of making the system look up the two terms hundreds of billions of times, then make hundreds of billions of comparisons, put your search terms into an array, and the text of each page into a long string.Then it only has to do one look up and 200 comparisons per page. Sorry to post a quick, incomplete answer, but I think I can point you in a good direction.

Excel for mac vba extract data from closed workbook