.xls file differs when opened via macro

Hello,

a customer of mine has a critical problem.

He wants to import data from a variety of excelsheets into one single excel sheet via a macro.

The problem:
While the file has the correct data when opened via mouseclick from the Windows Explorer, the file has different data when opened via macro. It seems that a value using the EOMONTH (or MONATSENDE in German) function (EDIT: from the analysis toolpak) isnt calculated when opened via Macro, while its calculated correctly when opened with a mouseclick.

The atpvbaen.xls in VBA is activated, so is the analysis toolpak and analysis toolpak (VBA) in Excel.

He is using an English Version on an english OS, so there shouldn't be a language problem.

Does anyone have a clue how to solve this ?

Thanks a lot,
Flunsi

Re: .xls file differs when opened via macro

This is the used code:

Option Explicit

Private Const base_TO_EXCEL As String = "C:\test"

'Private Const SHEET_NAME As String = "Sheet1"
Private Const SHEET_NAME As String = "DCF Model"
-----------------------------------------------------------------------
'* This macro assumes:
'* All values are in the same cells in _every_ single
'* excel file
'* All sheets in the excel files are named equally
'*
'* Open:
'* - Set base_TO_EXCEL to the path where the excel sheets
'* are stored in
'* - Change SHEET_NAME if the sheets are not called Valuation
Public Sub ExtractbaseData()
Application.DisplayAlerts = False

Dim fso As Scripting.FileSystemObject
Dim xlFile As Scripting.File
Dim iterationRow As Long
Dim xlAppl As Excel.Application
Dim xlSheet As Excel.Worksheet

Set fso = New Scripting.FileSystemObject

iterationRow = 1

'* Check if folder exists
If fso.FolderExists(base_TO_EXCEL) Then
'* iterate over each file
For Each xlFile In fso.GetFolder(base_TO_EXCEL).Files
If 3 < Len(xlFile.Name) Then
If "xls" = Right$(xlFile.Name, 3) Then
'* open with excel and read the values
Set xlAppl = New Excel.Application

'english
xlAppl.AddIns("analysis toolkit").Installed = True
'german xlAppl.AddIns("Analyse-Funktionen").Installed = True

'here I open the workbook with the usual command
xlAppl.Workbooks.Open (xlFile)

'this is just for debugging purposes
xlAppl.Visible = True

Set xlSheet = xlAppl.Worksheets(SHEET_NAME)
xlSheet.Unprotect ("xxxxxxxx")
xlSheet.Calculate

'just setting some values here

'* close excel
Set xlSheet = Nothing
xlAppl.DisplayAlerts = False
xlAppl.Workbooks.Close
Application.DisplayAlerts = False


iterationRow = iterationRow + 1
End If
End If
Next xlFile
Else
MsgBox "Folder does not exist. Please set the variable"
End If

Set fso = Nothing

End Sub

Re: .xls file differs when opened via macro

Quote from Andy Pope

Hi,

Does the workbook have an Auto_Open() routine? As this is not executed when the workbook is opened via vba code.

The workbook has no macros at all in it.

Re: .xls file differs when opened via macro

Quote from Andy Pope

:confused: If you open the book with a mouse click how does the macro you posted get executed?

The posted macro is in the destination workbook where the data is supposed to be written into. The macro is executed via the menu or from VBA Editor.
The source workbook, where the data is from, has no macros. And the source workbook is the one that differs when being opened via a macro.

Re: .xls file differs when opened via macro

Another thing I found out:
When I examine the workbook after it has been opened with the makro, i can see the analysis toolpak under add-ins, but when I open the references there is no atpvbaen.xls.

Which is rather strange.

So the main problem remains: I open the workbook with a macro and the cells that contain an EOMONTH don't calculate anything.

Re: .xls file differs when opened via macro

Problem solved.

Apparently a bug exists that leads to the EOMONTH function not being able to work when called external.
This is only with EOMONTH and not with other functions from the analysis toolpak.

My customer rewrote his formula, and everything is fine.
Only that I got neither credits nor money for my efforts