.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,

  • 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

    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")

    'just setting some values here

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

    iterationRow = iterationRow + 1
    End If
    End If
    Next xlFile
    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


    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

    When you automate excel like this "Set xlAppl = New Excel.Application" with code none of the addins or files in alt - startup dir are loaded.
    Since you appear to be working from xl already I'm not sure why you are creating new instances but in any case to force the addin to load:

    xlAppl.AddIns("Analysis ToolPak").Installed = False
        xlAppl.AddIns("Analysis ToolPak").Installed = True

    Also you are creating new instances for each file found within the loop. This will be slowing your code down & using up comp resources. You dont indicate what data you are manipulating & how, but you will need full referencing to each App for it to work.

  • 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 :(

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!