Hi,

I'm in a new job and we're using an SAP add-in to pull data. I'm trying to create script that, when the excel file is opened, will activate the add-in, log-in, refresh the query, update the pivot tables, and email the report/excel file. I've got all of this done, but when the code activates the add-in I get prompted to log-in. When I schedule the file to open at 5:00am so the report will be ready when I get in the office, I won't be able to click cancel. While I'm sitting here testing it I click cancel and the rest of the process runs fine so I don't need to log-in at this step, but I can't figure out how to suppress this log-in prompt, or use VBA to select cancel on this prompt.


I've only been writing VBA for about a month so if it's totally obvious what the problem is, try not to give me too hard of a time.

Any help you can give will be much appreciated.

This code is on the "ThisWorkbook" object to launch the add-in when I open the file.


Option Explicit

Private Sub Workbook_Open()
Call EnableAnalysisOffice
End Sub

Private Sub EnableAnalysisOffice()
Dim addin As COMAddIn
For Each addin In Application.COMAddIns
If addin.progID = "SBOP.AdvancedAnalysis.Addin.1" Then
If addin.Connect = False Then addin.Connect = True
End If
Next
End Sub



This code is in my Module1 to log-in and refresh my query the refresh the pivot tables.



' Run full macro when file is open
Sub NoAuto()





' This prevents screen flashes casused by each step in the marco
Application.ScreenUpdating = False




' Create and assigne variables for the SAP login and refresh steps
Dim lResult As Long
Dim MyClient As String
Dim MyUser As String
Dim MyPWD As String
Dim MyLang As String


MyClient = "010"
MyUser = "myusername"
MyPWD = "mypassword"
MyLang = "EN"


' Log into SAP Analysis for Excel
iAppCall = Application.Run("SAPLogon", "DS_1", MyClient, MyUser, MyPWD, MyLang)


' Refresh the query Analysis -> Refresh All
lResult = Application.Run("SAPExecuteCommand", "Refresh", "DS_1")




' Refresh all Pivot Tables
ActiveWorkbook.RefreshAll