Sorry again for the delay. It took me some time, but i found a solution.
Somehow the file was "corrupted" and i had to extract all my data in to a new file with this method:
[https://support.microsoft.com/en-us/office/repair-a-corrupted-workbook-153a45f4-6cab-44b1-93ca-801ddcd4ea53#:~:text=Klicken%20Sie%20auf%20Datei%20%3E%20%C3%96ffnen,dann%20auf%20%C3%96ffnen%20und%20reparieren.
Now my Files work on all of our Systems just fine (as far as i can tell by now - time will tell).
But thanks to all of you for the support. This Question can be closed now.
In my case, the problem occurs with a
digital signed Excel VBA
file.
The Excel-sheet contains several
self-written VBA functions
that are used as
formulas
in cells
. If I remove the formulas with the self-written functions, then there is no automation crash.
Interestingly, the problem does not arise when the file is opened on a PC that does not require signed macros.
The problem also occurs in other Excel VBA files where I have
own code in the VBA macro "open
"-function that accesses "something" like worksheets or the Internet.
The cause seems - according to my interpretation - to be that VBA functions are already being executed during the opening process and the verification of the security certificate or macro security settings is a parallel processing that has not yet been completed.
The Excel macro file is from 2020 and has been running successfully for 2 years. The
problem first appeared in the summer of 2022
, with no code change at that time - presumably after Office updates.
Dim emptyRow As Long
Dim database As Workbook
emptyRow = WorksheetFunction.CountA(nwb.Sheets("sheet1").Range("A:A")) + 1
'Transfer Information
With nwb.Sheets("sheet1")
Cells(emptyRow, 1).Value = Me.TextDate.Value
Cells(emptyRow, 2).Value = Me.Combofactory.Value
Cells(emptyRow, 3).Value = Me.Combohours.Value
Cells(emptyRow, 4).Value = Me.Combostation.Value
Cells(emptyRow, 5).Value = Me.Combomodels.Value
Cells(emptyRow, 6).Value = Me.Combodefect.Value
Cells(emptyRow, 7).Value = Me.Textrecord.Value
End With
ActiveWorkbook.Close savechanges:=True
Application.ScreenUpdating = False
Dim cell As Range
Dim selection As Range
selection = nwb.Sheets("sheet1").Range("A1:G" & emptyRow)
For Each cell In selection
ListBox1.AddItem cell.Value
Next cell
ActiveWorkbook.Close savechanges:=1
The issue is with the macros and could be just about anything. Automation errors can occur at runtime for a variety of reasons. Without any knowledge of the macros then we would have no way of diagnosing this. These are runtime errors (network blips, odd system behavior, etc) and is unlikely to be noticeable just by looking at the code.
You're going to need to enable more verbose logging when an error occurs. Unfortunately it depends where the error is occurring as to how you do that. I might start by looking in the Event Viewer to see if it logged any useful error messages. That would be the ideal situation.
If not then I'd modify each macro to wrap the errors and display useful information (or log it somewhere) so you can review it. I'm assuming here the default of "break on unhandled error" is already set and that is why you're seeing the message box. But it doesn't tell you anything useful. Therefore you need to capture the error and display it instead. There is a lot involved in doing this so I'm going to link to
this article
on how you might do that. Given the choices I'd lean toward the
on error goto label
approach. Then use
Err.Description
to get the error but I suspect that it is that description that you're already seeing so it is not useful.
This is where you need to understand your macro. If your macro is making calls into Excel then that is likely where the error is occurring. You could wrap each separate call into Excel with a custom label but that seems like overkill. I might recommend that you create a simple step variable in the macro and update the step variable as your code executes. If an error occurs then include the step in the message so you can figure out where things are failing. A pseudo example (not valid VBA).
Dim step As String
On Error GoTo OnError
Set step = "Getting range"
Get range from Excel...
Set step = "Calculating value"
Calculating value from Excel...
Set step = "Updating spreadsheet"
Updating spreadsheet...
OnError:
MsgBox.Err.Description & vbCrLf & "Step: " & step
You can have problem with files with code then can be a new security rules.
Add new definition to control panel/internet interface like path to SharePoint or intranet servers:
Regards.
