Differente .xls files generated when VBE window closed vs open

[Mark]

While debugging some generated spreadsheets that are picked up by a third
party system, we found that the actual byte code of xls workbooks generated
in vba in Excel differ depending on wether or not the VBE window is open or
closed.

Has anyone run across this? The files generated when the VBE window is open
are always about 8k larger than when the VBE window is closed. Looking at
the files with a hex editor, the files are completely different.

The problem is that for some reason the 3rd party system will not handle the
file correctly unless it gets the format that is generated when the VBE
window is open. This causes us to open the VBE window programatically before
saving the file in code.

When opening both files separately in Excel all modules, properties etc are
identical.

File is being saved with

Application.VBE.MainWindow.Visible = True ( or False)

xlNewBook.SaveAs FileName:=strOutputPath & "_" & strWorkBookName, _
FileFormat:=-4143, Password:="", WriteResPassword:="", _
ReadOnlyRecommended:=False, CreateBackup:=False, TextCodePage:=1100

Thanks in advance,
Mark

 

[Peter T]

Hi Mark,

Indeed an xls that has been 'exposed' to an open VBE is saved with
additional data, even if no code or modules ever added. Such a file will
include "_VBA_PROJECT_CUR", I posted a routine that'll check all files in a
folder for that.

http://tinyurl.com/2jn79e

As I mentioned in the thread, that string merely confirms the file has been
'exposed' to the VBE, it may or may not include code or inserted modules.

The problem is that for some reason the 3rd party system will not handle the
file correctly unless it gets the format that is generated when the VBE
window is open.

This seems curious, why not, does that third party app want to add a project
password perhaps.

Regards,
Peter T

 

[Mark]

Thanks Peter, I will take a look - the external system does some strange
things with workbooks...

 

[Mark]

Solved this in case anyone is interested. In code added a dummy module to
VBComponent...seems to force save in Biff8 format.