Excelde Ad Çakışması Sorunu Çözümü

[keenimo]

Excelde ad çakışması , aynı excel içerisindeki bir sayfanın kopyasını oluştururken bazen karşımıza çıkar. Eğer çok sayıda çakışan ad varsa her ad için onay ister bazen bu 1000 lerce olabilir. hepsi için entera basmak zorunda kalabilir ya da görev yöneticisinden exceli kapatmak zorunda kalabilirsiniz.

Bunun normalde çözümü Excel - Formüller - Ad yöneticisi içeriğini tamamen silip bu haliyle kaydetmektir.
Ama bazı excel dosyalarında Ad yöneticisi temiz olmasına rağmen yine sayfa kopyalamasında ad çakışması oluşabiliyor. gizli adlardan dolayı.

Bunun çözümü için normalde excel makro penceresinde bir kod yazılıyor ama hiç çalışmadı denediğimde.

Kesin çözüm olarak excel dosyasının xls yada xlsx uzantısını zip olarak değiştirmek gerekiyor. bu zip dosyasının içinde XL klasörü var. XL klasörünün içinde de workbook.xml dosyası var.
bu workbook.xml dosyasını not defteriyle açıp <defined names> ile </defined names> arası tüm satırların silinmesi (defined names dahil) gerekiyor.ve yeni workbook.xml dosyasının zip içindeki diğer dosyayla değiştirilmesi gerekiyor. zip dosyası bu şekilde kaydedildikten sonra uzantı tekrar eski haline çevrilince ad yöneticisi sorunu tamamen ortadan kalkmış oluyor.

Bu işlemin karmaşası her sorunlu excel için zaman alabiliyor. çeşitli argümanlar vererek ChatGPT den bunun için bir batch kodu yazmasını istedim. onlarca denemeden sonra işe yarayan bir kod oluşturdum.

Kod: Tümünü seç

setlocal enabledelayedexpansion

color 0e

:: Set the screen buffer size (width x height)
mode con: cols=160 lines=20

:: Set the window size (width x height)
mode con: cols=40 lines=10

:: Check if a file path is provided as an argument
if "%~1"=="" (
    echo No file path provided.
    pause
    exit
)

:: Get the path, name, and extension of the provided Excel file
set "excelFile=%~1"
for %%i in ("!excelFile!") do set "excelPath=%%~dpi"
for %%i in ("!excelFile!") do set "excelName=%%~ni"
for %%i in ("!excelFile!") do set "excelExt=%%~xi"

:: Check if the file extension is .xls
if /I "!excelExt!"==".xls" (
    :: Convert the .xls file to .xlsx using Microsoft Office's default tools
    echo Converting the Excel file to XLSX format...
    "C:\Program Files\Microsoft Office\Office16\excelcnv.exe" -oice "!excelFile!" "!excelPath!!excelName!.xlsx"

    :: Update the file path and extension variables
    set "excelFile=!excelPath!!excelName!.xlsx"
    set "excelExt=.xlsx"
)

:: Create a new Excel file name with _temiz_ prefix
set "newExcelName=_temiz_!excelName!"

:: Duplicate the original Excel file and rename it with _temiz_ prefix
copy "!excelFile!" "!excelPath!!newExcelName!!excelExt!"

:: Rename the copy to .zip
ren "!excelPath!!newExcelName!!excelExt!" "!newExcelName!.zip"

:: Create a temporary directory
set "tempDir=%TEMP%\ExcelTemp"
mkdir "!tempDir!" 2>nul

:: Extract the contents of the Excel file (now a .zip)
cd "!tempDir!"
powershell -command "Expand-Archive -Path '!excelPath!!newExcelName!.zip' -DestinationPath '!tempDir!' -Force"

:: Check if the xl\workbook.xml file exists
if exist "!tempDir!\xl\workbook.xml" (
    :: Clear defined names in workbook.xml
    powershell -command "(Get-Content -Path '!tempDir!\xl\workbook.xml') -replace '(?s)<definedNames>.*?</definedNames>', '' | Set-Content -Path '!tempDir!\xl\workbook.xml'"

    :: Repack the contents into a new .zip
    cd "!tempDir!"
    powershell -command "Compress-Archive -Path '*' -DestinationPath '!excelPath!!newExcelName!.zip' -Force"
    
    :: Rename the file extension back to .xlsx
    ren "!excelPath!!newExcelName!.zip" "!newExcelName!.xlsx"
) else (
    echo Workbook.xml not found in the Excel file.
)

:: Clean up temporary directory
cd ..
rmdir /s /q "!tempDir!"

bu kodu not defterine kopyalayıp uzantısını bat olarak değiştirin. bu bat dosyasının yanına 7zip kurulumda bulunan 7z.exe yi kopyalayın. Herhangi bir sorunlu excel dosyasını bu bat dosyasına sürüklediğinizde 2 dosya oluşturur. uzantısı _ANA olan dosya hiç bozulmamış halidir. diğer dosya ise Ad çakışması olmayan yeni dosyadır. ( Bat dosyası ve 7z.exe dosyası mutlaka aynı klasörde olmalı)
Sorun olarak bize gelen herşey çözüldüğü zaman insana ayrı bir zevk veriyor.

[velociraptor]

Eline sağlık , başıma gelmedi lakin bu sorunu yaşayanlara çözüm olacaktır.

[keenimo]

Kodu XLS uzantılı dosyalar için tekrar düzenledim. XLSX uzantılı dosyalar zip şeklinde açılabiliyor ama XLS dosyaları aynı şekilde çalışmıyormuş. Dolayısıyla kodda önce XLS uzantısı varsa XLSX uzantısına çevirme kısmını ekledim.Daha sonra temizliğine kaldığı yerden devam ediyor.

[velociraptor]

Eline sağlık