VBA-Makros in Excel-4.0-Dateien - Tool zum Finden betroffener Files

[[dzp]Viper]

Hallo,

wir sind auf der Suche nach einer Lösung wie wir Excel-Dateien prüfen können welche veraltete Exel 4.0 VBA Macros nutzen.
Wie so oft, gibt es sehr viele produktionsrelevante Abläufe auf Basis dieser veralteten Makros und wir möchten gerne im Voraus wissen welche produktionsrelevanten Dateien betroffen sind...

Hat jemand eine Idee/Lösung/Vorschlag/Tipp? Es kann auch Geld kosten...

Hintergrund:
Microsoft wird über kurz oder Lang die Excel 4.0 VBA Makros komplett deaktivieren... aus gutem Grund.

[Corny]

Ich kenne kein spezielles Tool, aber spontan fällt mir ein Scanner wie Open Threat Scanner ein. Eigentlich zur Malwaresuche gedacht, kann er bestimmte Strings in Dateien finden. Vielleicht haben die Excel-Dateien alle einen entsprechenden String.



c't-Artikel hinter Paywall: https://www.heise.de/select/ct/2020/16/2014013094729479952

[konkretor]

Ich habe mal ChatGPT gefragt und er spuckt mir ein Python/Powershell script aus.

Unter Windows laufen lassen. Denke da mußt dir halt etwas bauen. So 90% wirst du so sicherlich gut raus finden. Habe den Code nicht selber abgefeuert etc. Soll nur als Idee dienen.



Excel 4.0 macros (also known as XLM macros) are not the same as VBA macros and they're not stored in the same place. VBA macros are stored in VBA modules, while Excel 4.0 macros are usually stored in hidden names and very hidden sheets. Therefore, the Python script I provided earlier which scans VBA modules won't find Excel 4.0 macros.

To detect Excel 4.0 macros, you could scan for very hidden sheets and hidden names. Here's a basic example of how you might modify the earlier Python script to do this:

```python
import os
import win32com.client

def find_xlm_macros(file_path):
# Open Excel
excel = win32com.client.Dispatch("Excel.Application")
excel.Visible = False

try:
# Open the workbook
wb = excel.Workbooks.Open(file_path)

# Check for very hidden sheets
for sheet in wb.Sheets:
if sheet.Visible != -1: # -1 is the constant for 'xlSheetVisible'
print(f"Found very hidden sheet in {file_path}")
return

# Check for hidden names
for name in wb.Names:
if name.Visible == False:
print(f"Found hidden name in {file_path}")
return

# Close the workbook without saving
wb.Close(False)
except Exception as e:
print(f"Error processing {file_path}: {e}")
finally:
# Quit Excel
if excel:
excel.Quit()

def main():
# The directory to scan
directory = "path/to/your/directory"

# Iterate through the files in the directory
for filename in os.listdir(directory):
if filename.endswith(".xls") or filename.endswith(".xlsx"):
file_path = os.path.join(directory, filename)
find_xlm_macros(file_path)

if __name__ == "__main__":
main()
```

Please replace `"path/to/your/directory"` with the actual path of your directory.

This script is very basic and might not catch all Excel 4.0 macros. Excel 4.0 macros can be complex and versatile, and there might be other places in the workbook where they're stored. You might need to enhance this script further depending on the specifics of the Excel 4.0 macros you're dealing with.

The same requirements and limitations apply to this script as to the previous one: it requires the `pywin32` library, Excel installed on the computer, and trust access to the VBA project object model enabled in Excel. It won't handle password-protected workbooks or VBA projects. It will only work for .xls and .xlsx files; if you have .xlsm or other Excel file types, you'll need to add those extensions to the if condition.




Hier als Powershell





Yes, PowerShell can also be used to detect Excel 4.0 (XLM) macros, and it works similarly to the Python script using the COM interface. Here's a simple example of how you might use PowerShell to detect very hidden sheets and hidden names, which could indicate the presence of Excel 4.0 macros:

```powershell
# Specify the directory
$directory = "C:\path\to\your\directory"

# Create an Excel COM object
$excel = New-Object -ComObject Excel.Application
$excel.Visible = $false

# Process each Excel file in the directory
Get-ChildItem -Path $directory -Filter *.xls* | ForEach-Object {
try {
# Open the workbook
$workbook = $excel.Workbooks.Open($_.FullName)

# Check for very hidden sheets
foreach ($sheet in $workbook.Sheets) {
if ($sheet.Visible -ne -1) { # -1 is the constant for 'xlSheetVisible'
Write-Host "Found very hidden sheet in $($_.FullName)"
return
}
}

# Check for hidden names
foreach ($name in $workbook.Names) {
if ($name.Visible -eq $false) {
Write-Host "Found hidden name in $($_.FullName)"
return
}
}
}
catch {
Write-Error "Error processing $($_.FullName): $_"
}
finally {
# Close the workbook without saving
$workbook.Close($false)
}
}

# Quit Excel
$excel.Quit()
```

Replace `"C:\path\to\your\directory"` with the actual path to your directory.

This script has similar limitations to the Python script. It requires Excel to be installed on the computer running the script, and it won't handle password-protected workbooks or VBA projects. It will only work for .xls and .xlsx files; if you have .xlsm or other Excel file types, you'll need to add those extensions to the `-Filter` parameter in the `Get-ChildItem` command.

Note: Running scripts that manipulate COM objects can sometimes leave orphaned processes if they crash or are otherwise interrupted. If you're running this script multiple times, you might want to check the Task Manager and kill any leftover Excel processes. Also, as this script accesses Excel's object model, you should enable Trust access to the VBA project object model in Excel's options under Trust Center > Trust Center Settings > Macro Settings.

[[dzp]Viper]

Oh Coole Idee... Darauf hätte ich auch selber können können. Danke

Ich gebe das man unseren Entwicklern zur Analyse.

[Asaraki]

Das Script findet höchstens einen Bruchteil der Excel 4 Macros, just FYI. Das ist deutlich komplexer als dieses popelige Beispiel. Das findet lediglich 2 von X(!) 4.0 Dingen.

Du bräuchtest eine abschliessende Liste der 4.0 Syntax, die nicht mehr supported wird. Müsste man eigentlich von MS bekommen können, bevor man da selbst Aufwand betreibt. Dann einen Search auf all diese ansetzen und du hast ein mehr oder weniger abschliessendes Bild.

Ich warte noch auf mein erstes Prod-Ticket wo drin steht "GPT said this would be fine" :D