Personal Workbook Locked For Editing *Only* When Running Macro and Not When Stepping Through

bvbull200

New Member
Joined
Jul 29, 2013
Messages
24
I have a macro that interfaces with SAP. The SAP portion is for it to run a report and open it in Excel. Excel takes over and activates the workbook, then saves it in a location and converts the file type from .MHTML to .xlsx.

When I step through the macro using F8 along the way, I have no issues. The macro works as expected. When I run the macro, however, it hits an error because my personal workbook somehow gets opened as read-only and I have to negotiate the message box saying that my personal workbook is locked for editing. Again, this is ONLY when I run the macro and does not appear when I step through it.

Here is the code:

Code:
Sub SAPScript()Application.DisplayAlerts = False
If Not IsObject(SAPGuiApp) Then
   Set SapGuiAuto = GetObject("SAPGUI")
   Set SAPGuiApp = SapGuiAuto.GetScriptingEngine
End If
If Not IsObject(connection) Then
   Set connection = SAPGuiApp.Children(0)
End If
If Not IsObject(session) Then
   Set session = connection.Children(0)
End If
If IsObject(WScript) Then
   WScript.ConnectObject session, "on"
   WScript.ConnectObject Application, "on"
End If
session.findById("wnd[0]").resizeWorkingPane 167, 36, False
session.findById("wnd[0]/tbar[0]/okcd").text = "/NZSD_INFOREQ_RPT"
session.findById("wnd[0]").sendVKey 0
session.findById("wnd[0]/usr/radP_ALL").select
session.findById("wnd[0]/usr/ctxtSO_DATES-LOW").text = Date - 30
session.findById("wnd[0]/usr/ctxtSO_DATES-HIGH").text = Date
session.findById("wnd[0]/usr/radP_ALL").setFocus
session.findById("wnd[0]").sendVKey 8
session.findById("wnd[0]").resizeWorkingPane 167, 36, False
session.findById("wnd[0]/usr/cntlGRID1/shellcont/shell").contextMenu
session.findById("wnd[0]/usr/cntlGRID1/shellcont/shell").selectContextMenuItem "&XXL"
session.findById("wnd[1]/tbar[0]/btn[0]").press
session.findById("wnd[1]/usr/ctxtDY_PATH").text = "I:\Bryan Voskuil\Product Operations\Product Service Representatives\Workflow Reporting\"
session.findById("wnd[1]/usr/ctxtDY_FILENAME").text = "export.MHTML"
session.findById("wnd[1]/tbar[0]/btn[11]").press
Application.Wait (Now + TimeValue("00:00:05"))
Workbooks("Export.MHTML").Activate
    ActiveWorkbook.SaveAs Filename:= _
        "I:\Product Service Representatives\Workflow Reporting\Power BI Data\Rolling_30_Workflow_PowerBI.xlsx" _
        , FileFormat:=xlOpenXMLWorkbook, CreateBackup:=False
Application.DisplayAlerts = True
ActiveWorkbook.Close
End Sub
Here is where the problem occurs:

Code:
session.findById("wnd[1]/usr/ctxtDY_PATH").text = "I:\Bryan Voskuil\Product Operations\Product Service Representatives\Workflow Reporting\"session.findById("wnd[1]/usr/ctxtDY_FILENAME").text = "export.MHTML"
[B][COLOR=#0000ff]session.findById("wnd[1]/tbar[0]/btn[11]").press[/COLOR][/B]
Application.Wait (Now + TimeValue("00:00:05"))
[COLOR=#ff0000][B]Workbooks("Export.MHTML").Activate[/B][/COLOR]
The blue piece of code is where SAP goes ahead and saves the file which opens automatically. When the file opens at that point, I get the dialog box saying the the personal workbook is locked for editing. Since that message box is open, I cannot perform the bit of code that is highlighted in red, which is technically where the macro fails.

Any insight as to why that is and how to get around it?

I have a number of macros in my personal workbook that need to be preserved and on hand for other tasks, for whatever that is worth.

Thank you for your assistance.
 

Some videos you may like

Excel Facts

How to calculate loan payments in Excel?
Use the PMT function: =PMT(5%/12,60,-25000) is for a $25,000 loan, 5% annual interest, 60 month loan.

Watch MrExcel Video

Forum statistics

Threads
1,102,731
Messages
5,488,543
Members
407,645
Latest member
suyoggore

This Week's Hot Topics

  • Timer in VBA - Stop, Start, Pause and Reset
    [CODE=vba][/CODE] Option Explicit Dim CmdStop As Boolean Dim Paused As Boolean Dim Start Dim TimerValue As Date Dim pausedTime As Date Sub...
  • how to updates multiple rows in muliselect listbox
    Hello everyone. I need help with below code. code is only chaning 1st row in mulitiselect list box. i know issue with code...
  • Delete Row from Table
    I am trying to delete a row from a table using VBA using a named range to find what I need to delete. My Range is finding the right cell. In the...
  • Assigning to a variable
    I have a for each block where I want to assign the value in column 5 of the found row to the variable Serv. [CODE=vba] For Each ws In...
  • Way to verify information
    Hi All, I don't know what to call this formula, and therefore can't search. I have a spreadsheet with information I want to reference...
  • Active Cell Address – Inactive Sheet
    How to use VBA to get the cell address of the active cell in an inactive worksheet and then place that cell address in a location on the current...
Top