Possible conflict with Active Workbook?

[SQLBI]

I'm having a nightmare with the following code, which is executed by the BeforeSave event.

The workbook in question is opened by another workbook, modified and then saved, firing off the BeforeSave event, however it errors when it gets to the bold line.

Now i'm assuming that it's not activating the sheet in question, however this all works 100% when the code is executed separately so maybe theres a conflict with the workbook that opens/saves this file.

Public Sub Retrieve()

Dim strUser As String
Dim strPassword As String
Dim intMsg As Integer

'intMsg = MsgBox("Are you sure you want to update the Essbase source data?", vbYesNo + vbExclamation, "Update Source Data")
    
    'If intMsg = 6 Then
        
    strUser = "automate"
    strPassword = "password"
        
        If AddIns("Arbor Essbase OLAP Server DLL").Installed = False Then
        
            AddIns("Arbor Essbase OLAP Server DLL").Installed = True
            GoTo Data
            
        End If
         
Data:
        
ActiveWorkbook.sheets("Essbase").Select

ActiveSheet.Range("GetData").Select

*Falls over here

sts = EssVConnect("Essbase", strUser, strPassword, "Nexus", "JV_DB", "JV_Store")
            
            Application.ScreenUpdating = True
            Application.Goto Reference:="GetData"
            X = EssVSetSheetOption(Null, 11, True)
            sts = essmenuvretrieve
            Application.Goto Reference:="GetData"
            
            sts = essvdisconnect("Sheet1")
            
        
        ActiveSheet.Range("Home").Select
        
        ActiveWorkbook.sheets("KPI").Select
        ActiveSheet.Range("KPIData").Select
        
            sts = EssVConnect("KPI", strUser, strPassword, "Athena", "KPI2000E", "KPI2000")
            
            Application.ScreenUpdating = True
            Application.Goto Reference:="KPIData"
            X = EssVSetSheetOption(Null, 11, True)
            sts = essmenuvretrieve
            Application.Goto Reference:="KPIData"
        
        Cells(1, 1).Select
        
        AddIns("Arbor Essbase OLAP Server DLL").Installed = False
        
        'MsgBox "Essbase data updated", vbOKOnly + vbInformation, "Done"
        
    'Else
    
        'Exit Sub
    
    'End If

End Sub

Any one got any ideas as this is starting to hurt my head.

Thanks in advance.


Leigh Moore
Solutions 4 MS Office Ltd

http://www.solutions4msoffice.co.uk

 

[TonyJollans]

Hi Leigh,

Just a thought. Is the Workbook you are saving the ActiveWorkbook? If you have a sheet called "Essbase" in your other Workbook, the Sheet Select statement before would be OK, but you might not be referencing the sheet you thought you were.

Enjoy,
Tony