PROCV - How to avoid the window to select external spreadsheet when non-existent

Asked

Viewed 477 times

1

I have a planilha using access to lookup of other planilhas PROCV, but they are floating formulas, that is, they change according to the input in specific fields.

This change occurs through Macro, which simply changes the planilha target of PROCV. Replacing the section of the path and name of the planilha by the equivalent of another planilha target.

For example file:

Planilha_Geral.xlsx

Briefcase:

C:\Users\usuario\Desktop

Has in one of its cells the following formula:

=procv(H1;'C:\Users\usuario\Desktop\Empresa_A_Outubro.xlsx'!$H$1:$L$300;6;falso)

I’ll have other cells to search for planilhas for the other months and on a given occasion, a macro wheel recognizing and copying the initial part of the formula:

=procv(H1;'C:\Users\usuario\Desktop\Empresa_A_

Replaces the address snippet from planilha elsewhere, for example:

Planilha B.xlsx

And finally copying the end of the formula that was already in the cell, for example:

Dezembro.xlsx'!$H$1:$L$300;6;falso)

This is because the intention is only to change the focus between companies and concentrate interesting data for all months (which are in separate spreadsheets).

Everything works perfectly, however there is the possibility of the spreadsheet that procv will point not exist, what I imagined that, in case of non-existent spreadsheet, the procv would return error. I treated this by adding at the beginning of the equation the following:

=SEERRO(

And in the end:

, "Não tem.")

However, the process when not finding a pointed worksheet, even before considering the error treatment opens a window requesting that the worksheet be correctly pointed, being necessary to press ESC FOR EACH CELL to apply the treatment SEERRO().

Considering that each spreadsheet is for a month and each month will bring 40 information from 4 sectors, we have:

40 * 4 = 160

That is, each time the macro runs and replaces the PROCV formula, for each month that there is no spreadsheet of the selected company it will be necessary to press ESC 160 times for the macro to be finalized.

Any idea how I can avoid opening this worksheet selection window?

excel exception

  • You can try to check if the file exists before, with If Len(Dir(DIRETORIO_DO_ARQUIVO+EXTENSÃO)) = 0 - In this case, if it is equal to zero, it does not exist. And then point the procv to the file itself in a dead cell; I tried to give false value in Application.DisplayAlerts,Application.EnableEvents and Application.ScreenUpdating, but nothing worked... The solution needs to be code even...

1 answer

0


Enter the line below at the beginning of the code:

Application.DisplayAlerts = False

And before the End Sub, insert:

Application.DisplayAlerts = True

Browser other questions tagged excel exception

You are not signed in. Login or sign up in order to post.