Excel complaining "Name Conflict" right after

[rudi]

Hello,

I face an Excel related error right after doing an _Excel_BookOpen. This is an EXCEL worksheet with filters defined, quite simple sheet. Once a week at Friday there is a scheduled task running on the file server "printing" the content to a PDF file for documentation puposes. As this is running 100% unattended it's a show stopper, if such dialog boxes show up.

 

Already when I did that script a month ago I faced the issue, that to dialog boxes showed up telling something about a "name conflict", once for "_FilterDatabase", and a 2nd time for "PrintingArea" (maybe _PrintingArea).

 

When some new name is entered, the script is going on with the PDF creation. But the autoit script is hanging with the _Excel_BookOpen, so I would need to start a 2nd. script to look for such bogus name conflict message boxes.

 

While trying do track down what's going on in detail, the issue vanished again, I answerd the two boxes with "xxxx" and "yyyy" for new fiel names, the file seems to have been saved by me myself without intention (or automatically by EXCEL.AU3?)

 

several other postings point into the direction of "this is an Excel Bug", seems to be not strictly Autoit related.

 

Facts:

• Windows 7 Pro x64
• Office 2010 SP2 32bit
• Localization = German (Win & Office)
• Autoit v3.3.14.5
• Excel Workbook with three sheets without any Macros: "TBx Projektliste.xlsx", just 82 kByte

 

Export-Excel-to-PDF-Projektstatus.au3

 

Any suggestions howto take care, that these "name conflicts" cannot occure?

[Nine]

Have you tried using a com error handler ?  Maybe you could trap the error when it is happening and find a way to get around it ?

[rudi]

No. I didn't, what do you suggest to use?

 

Right now I could at least reproduce the issue.

Excel is always closed when starting the script attached to the OP.

Test-01.xlsx  --> plain new Excel file with some values --> printing to PDF works fine

Test-02.xlsx --> same file, filter defined, but *NOT* used yet saved --> printing to PDF works fine

Test-03.xlsx --> filter activated, saved --> This message about "Name" _FilterDatabase is stopping the whole show.

 

 

Test-03.xlsx

Test-02.xlsx

Test-01.xlsx

Edited January 22, 2019 by rudi
attached sample XLSX files.

[Nine]

Try this, see if the handler catch the error :

Global $oMyError = ObjEvent("AutoIt.Error","MyErrFunc") ; this has to be put at the beginning of the script

;----------------------------------------------------------------------------------------------------------
; Com Error Handler
;----------------------------------------------------------------------------------------------------------

Func MyErrFunc()
Local $HexNumber
Local $strMsg

  $HexNumber = Hex($oMyError.Number, 8)
  $strMsg = "Error Number: " & $HexNumber & @CRLF
  $strMsg &= "WinDescription: " & $oMyError.WinDescription & @CRLF
  $strMsg &= "Script Line: " & $oMyError.ScriptLine & @CRLF
  MsgBox(0, "ERROR", $strMsg)

  Return SetError(1,0,0)

Endfunc

 

[rudi]

@Nine

thanks for your reply. I just cannot get where and when to call your function: I guess I do not see an COM error, I see a "regular" message box asking for an alternative name due to a name conflict for the name _FilterDatabase. This is happening *DURING* the _ExcelBookOpen() in my script.

 

When I give a "Neuer Name" (New Name), the _ExcelBookOpen() can finish "its job" and the script will continue. But within one script I think I cannot lookout for that Message Box, as the  ExcelBookOpen() doesn't "return" until the new name was given.

 

When I once specified a new name as simply "xxx", then for further runs of my script this question dialog box about the "name conflict" doesn't show up. (at least not immediately)

 

Edited January 22, 2019 by rudi

[Nine]

Ok, it is during the opening.  Yes the com error handler won't help you in any way since _ExcelBookOpen() has its own handler.  So only thing I can suggest now is to bring the _ExcelBookOpen() from the Excel.au3 file into your script, rename it _ExcelBookOpenEX () locate where exactly it is happening.  Maybe you will understand more clearly what is going on.  Good luck.

[rudi]

Hm.

Well, it's fact, that not only autoit's ExceBookOpen() can trigger this situation, that Excel is complaining about name conflicts, that do *NOT* show up, when the Excel Workbook is opened by doubleclicking the WB. For VBA / Delphi / Powershell this can happen in the exactly same style. It seems to be possibly related to non English Office Versions.

Some postings elsewhere state, that this is a known Excel Bug since Office 2007, not fixed so far.

 

Here I found a suggestion to delete the Excel WB Name "_FilterDatabase" using powershell:

 

https://stackoverflow.com/questions/14644441/name-conflicts-with-filterdatabase

[rudi]

Another idea: As doubleclicking the Excel WB never causes this issue, might it be a good idea to use shellexecute() to open the exel workbook "regularly", then somehow to "attach" to the WB when it's already opened, to do the PDF print and to ProcessClose() Excel.exe afterwards?

Howto "attach" to an already opened Excel WB, if that's possible?

[Nine]

1 hour ago, rudi said:

Howto "attach" to an already opened Excel WB, if that's possible?

With _Excel_BookAttach or also _Excel_BookList

 

[rudi]

To attach to the WB unfortunately isn't an option, as this has to run as SCHEDULED TASK. Or is a WB attach somehow possible without an active logon session?

 

I've found this posting at answers.microsoft.com (first answer) to delete area names.

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

 

Any suggestions to just avoid the name conflict Excel is complaining about only, when the WB in question is opend using ExcelBookOpen() , instead of deleting all "names" in the WB?

 

<edit>

I just noticed, that this approach (of course) needs to be "packed" into a WB with macros, so XLSX cannot be used as Excel File Type, but it *MUST* be stored using XLSX format.

Is it possible to run this code against the WB using Autoit, *BEFORE* doing the ExcelBookOpen()???

</edit>

<edit 2>

I as a qnd approach I kicked all the "names" using these lines:

Sub DELRNG()
	Dim rNme As Name
	On Error Resume Next
	For Each rNme In ActiveWorkbook.Names
		rNme.Delete
	Next
	On Error GoTo 0
End Sub

A thing I didn't mention so far: This workbook is "shared for concurrent usage" (Freigegeben zur gemeinsamen Benutzung), that's a required thing. So to be able to (temprarily) use VBA macro code, this "shared" feature has to be removed first. After "name" cleanup I shared it again and saved it without macros back to its original XLSX file name.

</edit 2>

TIA, Rudi.

Edited February 11, 2019 by rudi

[Nine]

One other solution is to create a small compiled script  that is launch at the start of the main script.  Its job would be to check the occurrence of the undesired window and then close it so the main script could continue its work.  Something like :

#NoTrayIcon
Opt("WinSearchChildren", 1)                         ; 0=no, 1=search children also
Global $sWindowTitle = "Nameskonflikt"              ; Window title
Global $sWindowText  = "Name darf integriertem"
Global $i_ProcessID, $i_WinCheckTime = 1000, $i_CtrlCheckTime = 1000
; Process command line parameters
If $CmdLine[0] = 0 Then Exit 1                      ; No ProcessID was specified
$i_ProcessID = $CmdLine[1]                          ; Process ID of the main script
If Not ProcessExists($i_ProcessID) Then Exit 2
If $CmdLine[0] > 1 Then $i_WinCheckTime  = $CmdLine[2]
If $CmdLine[0] > 2 Then $i_CtrlCheckTime = $CmdLine[3]

While 1
    If WinExists($sWindowTitle, $sWindowText) Then
        While 1
            WinActivate($sWindowTitle, $sWindowText)

            ; Do what you need to do to close the window

            Sleep($i_CtrlCheckTime)
            If Not WinExists($sWindowTitle, $sWindowText) Then ExitLoop
        WEnd
    EndIf
    Sleep($i_WinCheckTime)
    If Not ProcessExists($i_ProcessID) Then Exit
WEnd

Based on water OL_Warning.au3

All you need to do is compile this script and call it from main script at the start with run (....)

Edited February 11, 2019 by Nine

[rudi]

@Nine

Thanks, I'll give it a try to check, if that work's as scheduled task (not running with a "regular" logon session)

The strange thing about this issue, that there seem to be lot of people struggling with this problem, but a real solution doesn't seem to exist. Some posts claim, that this is a "well-known-bug" of Excel for over a decade (Office 2007 or even maybe 2003)

[Nine]

23 minutes ago, rudi said:

I'll give it a try to check, if that work's as scheduled task (not running with a "regular" logon session)

I don't see a single reason why it wouldn't work as a scheduled task, since the start of the second script is done in the first...

[Bert]

I may have the answer. There is a office bug where a file will be marked as read only when it really isn't. Your code may be saying it wants to save it as a new file because the file you want to overwrite is read only. A simple test to see if this is happening is simply try to open the fine in Excel right before the script runs, make a small change then try to save it. IF you get the read only message, then you know the problem. 

JTLYK - I've had to deal with this bug myself. I was never able to fix it. 

[rudi]

@Bert

thanks for your reply. The issue is *NOT*, that the file "wants" to be saved, the ExceBookOpen() is failing, as *EXCEL* is complaining about "non-unique-Names", explicitely _FilterDatabase and PrintingArea (maybe _PrintingArea).

Opening the file (double click) and saving it back doesn't give any trouble.

 

Deleting all "region names" (Area names?) with the VBA script some posts up is solving the issue, for "right now", it will come back randomly, when filtering is used inside one of the WB's sheets. (Not always, so using filtering is not reliable to reproduce this bullsh**)

 

I already took some while to dig into the EXCEL.UDF [by SEO (Locodarwin), DaLiMan, Stanley Lim, MikeOsdx, MRDev, big_daddy, PsaltyDS, litlmike, water, spiff59, golfinhu, bowmore, GMX, Andreu, danwilli] but I definitely have much too little knowlege about Object Handling to even guess, how this might be avoided.

 

And once again, this is a general Excel constraint, that is giving trouble with Excel Automation in several other languages as well.

 

 

 

Edited February 15, 2019 by rudi

[rudi]

Hello,

After the issue was not showing up for unknown reasons I figured out, howto reproduce the issue several times in a row:

 

1. Create an empty Excel Workbook (Here: Office 2010 SP2, German localization)
2. Fill 2 collumns with some data, values don't matter
3. Filter Data (Columns)
4. set some filter, safe and close the file.
5. Running my script does *NOT* give any trouble
6. Open the WB with Excel, "share" the WB for concurrent editing:

XL2PDF-test.xlsx

 

Safe and close the WB, From now on my Script will complain about "Name Conflict". Entering some new name will make it continue fine, *BUT* the new "Region Name" will be stored in the WB permanently. Okay, I could create a copy of the WB in the first place, print that one, then delete the copy, leaving the original WB untouched.

 

But I would prefer to *SOLVE* this issue, that's basically an Excel issue (not an autoit issue, at least I honestly believe so)

To kick the random names save the attached Excel file

1. Open the WP
2. Stop the "Share this WB"
3. Save it
4. Open VBA Editor (XLSX files cannot save macros, so it's not included in the file)
5. Drop the macro in the VBA, run it to delete *ALL* names
6. stop any filtering
7. save the WB

 

now my script will run nicely again. This will continue, as long as not both options were used: 1.) "Shared (concurrent?) Editing for this WB", Filtering set and used.

CU, Rudi.

 

 

[Norm73]

Maybe it will help someone.
The problem with name conflict also happens very often when AutoFiler is used.
At the end, before saving you have to delete all names
I solved it like this:

$oExcel = _Excel_Open()
$oBook_BFSM = _Excel_BookOpen($oExcel, @ScriptDir &"\EXCEL\BFSM.xlsx")
;
;
; 
;
;
For $rNme In $oBook_BFSM.Worksheets(1).Names
    $rNme.Delete
Next
_Excel_BookSave($oBook_BFSM)

 

Edited February 27 by Norm73