Excel name conflict with _FilterDatabase
Update: Found a better solution. Posted at the bottom.
Have been facing issues with some Excel sheets created by our German colleagues that gives the messages in the images below when opened with Excel in UK (UK or US localisation).
At this link I came across the following tips to find and delete these hidden tables. After that the file would open but it had deleted hundreds of hidden tables and the Excel file was effectively useless. But someone may find this useful if there is only those few tables causing the issue that this script deletes. You may also be able to use the VB code to find and go through the hidden tables. Remember to test this on a copy so as not to lose data.
– Open the file by creating a new name.
– Press fn + option + F11 on Mac OR Alt + F11 on Windows
– Right click on the tab you are having issues with in the list on the left
– Click on ‘display the code’ (using French version so not sure what the English version would be)
– Paste the code below in the window that appears in the center
– Run the code by clicking on the run button
– A window will pop out, click delete for all issues
– Once done close the window and save the file
– Close the file and reopen
Code:
Sub DELRNG()
Dim rNme As Name
On Error Resume Next
For Each rNme In ActiveWorkbook.Names
If (MsgBox(“Delete the name? ” & rNme.Name, vbYesNo) = vbYes) Then
rNme.Delete
End If
Next
On Error GoTo 0
End Sub
Update: So the above code was asking to delete hundreds of tables and was not usable. While investigating this further myself and a colleague came across an interesting solution that (at least) solved the issue for us.
The issue seems to be related to the Print Area selection saved. We simply deleted those as per the below images and it resolved the issue without having to do anything with _FilterDatabase.
Delete the Print Area related stuff as per below. Please make sure you do it on a test copy or after creating a backup copy.
Hopefully this will resolve the issue for you guys as well.
------------------------------
O nome Database é reservado do MS-Excel na versão em inglês, sendo utilizando quando ativamos, por exemplo, o Filtro avançado.
No MS-Excel em português este mesmo nome é Banco_de_Dados.
Quando utilizamos o nome criado automaticamente pelo MS-Excel quando ativamos o recurso do Filtro Avançado, o produto se encarrega e alternar o nome entre o português e o inglês, da mesma forma que faz com as funções (exceto as Ferramentas de Analise).
Para resolver o seu problema, acesse a caixa de dialogo de Gerenciador de Nomes (Ctrl + F3) e troque o nome da área definida como Database para qualquer outro nome, como por exemplo BaseDados
-----
fonte: https://randomtecharticles.com/excel-name-conflict-with-_filterdatabase/
0 comentários:
Postar um comentário