I was wondering if anyone could help me, as I use a script that generates files in SAP and merges the documents into one.
The problem is that I can't get the generated files to close automatically, or at least not with another macro that does that. If I close the generated spreadsheets and reopen them, the macro closes, but I can't close them after they're generated by SAP. I've tried fixing the code using AI, but without success.
Below is my code to check: the main code and the one I use to try to close:
Sub EXP()
Dim Inicio As Date
Dim SapGuiAuto, SApplication, Connection, session, WScript
Dim i As Integer
Dim empresa, LOC_NEG, Periodo, ICMS_LAYOUT, ICMS_NOME_ARQUIVO
Dim IPI_LAYOUT, IPI_NOME_ARQUIVO, statusBarText
Dim caminho As String
Dim periodoConsolidado As String
Dim caminho_BR01 As String
Dim caminho_BR02 As String
Inicio = Now
caminho_BR01 = Sheets(1).Range("C2").Text
caminho_BR02 = Sheets(1).Range("C3").Text
' SAP Conex�o
If Not IsObject(SApplication) Then
Set SapGuiAuto = GetObject("SAPGUI")
Set SApplication = SapGuiAuto.GetScriptingEngine
End If
If Not IsObject(Connection) Then
Set Connection = SApplication.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 SApplication, "on"
End If
i = 6
Do
If Sheets(1).Cells(i, 1) = "" Then Exit Do
empresa = Sheets(1).Cells(i, 1)
LOC_NEG = Sheets(1).Cells(i, 2)
Periodo = Sheets(1).Cells(i, 3)
ICMS_LAYOUT = Sheets(1).Cells(i, 4)
ICMS_NOME_ARQUIVO = Sheets(1).Cells(i, 5)
IPI_LAYOUT = Sheets(1).Cells(i, 6)
IPI_NOME_ARQUIVO = Sheets(1).Cells(i, 7)
' Define o caminho de acordo com a empresa
If empresa = "BR01" Then
caminho = caminho_BR01
ElseIf empresa = "BR02" Then
caminho = caminho_BR02
Else
caminho = "C:\Temp"
End If
'--------- ICMS ----------
ICMS_NOME_ARQUIVO = GeraNomeUnicoArquivo(caminho, ICMS_NOME_ARQUIVO)
session.findById("wnd[0]").maximize
session.findById("wnd[0]/tbar[0]/okcd").Text = "/N/FHG/AII07"
session.findById("wnd[0]").sendVKey 0
session.findById("wnd[0]").sendVKey 4
session.findById("wnd[1]/tbar[0]/btn[17]").press
session.findById("wnd[1]/usr/tabsG_SELONETABSTRIP/tabpTAB001/ssubSUBSCR_PRESEL:SAPLSDH4:0220/sub:SAPLSDH4:0220/ctxtG_SELFLD_TAB-LOW[1,24]").Text = empresa
session.findById("wnd[1]/usr/tabsG_SELONETABSTRIP/tabpTAB001/ssubSUBSCR_PRESEL:SAPLSDH4:0220/sub:SAPLSDH4:0220/ctxtG_SELFLD_TAB-LOW[2,24]").Text = LOC_NEG
session.findById("wnd[1]/usr/tabsG_SELONETABSTRIP/tabpTAB001/ssubSUBSCR_PRESEL:SAPLSDH4:0220/sub:SAPLSDH4:0220/txtG_SELFLD_TAB-LOW[3,24]").Text = Periodo
session.findById("wnd[1]/usr/tabsG_SELONETABSTRIP/tabpTAB001/ssubSUBSCR_PRESEL:SAPLSDH4:0220/sub:SAPLSDH4:0220/txtG_SELFLD_TAB-LOW[3,24]").SetFocus
session.findById("wnd[1]/usr/tabsG_SELONETABSTRIP/tabpTAB001/ssubSUBSCR_PRESEL:SAPLSDH4:0220/sub:SAPLSDH4:0220/txtG_SELFLD_TAB-LOW[3,24]").caretPosition = 6
session.findById("wnd[1]/tbar[0]/btn[0]").press
session.findById("wnd[1]/usr/lbl[12,3]").SetFocus
session.findById("wnd[1]/usr/lbl[12,3]").caretPosition = 3
session.findById("wnd[1]").sendVKey 2
session.findById("wnd[0]/usr/ctxtP_LAYOUT").Text = ICMS_LAYOUT
session.findById("wnd[0]/usr/ctxtP_LAYOUT").SetFocus
session.findById("wnd[0]/usr/ctxtP_LAYOUT").caretPosition = 12
session.findById("wnd[0]/tbar[1]/btn[8]").press
session.findById("wnd[0]/usr/cntlCC_ALV/shellcont/shell").currentCellColumn = "ITEM-ATRDIN-DOCDAT"
session.findById("wnd[0]/usr/cntlCC_ALV/shellcont/shell").selectedRows = "0"
session.findById("wnd[0]/usr/cntlCC_ALV/shellcont/shell").contextMenu
session.findById("wnd[0]/usr/cntlCC_ALV/shellcont/shell").selectContextMenuItem "&XXL"
session.findById("wnd[1]/tbar[0]/btn[20]").press
session.findById("wnd[1]/usr/ctxtDY_PATH").Text = caminho
session.findById("wnd[1]/usr/ctxtDY_FILENAME").Text = ICMS_NOME_ARQUIVO
session.findById("wnd[1]/usr/ctxtDY_FILENAME").caretPosition = 5
session.findById("wnd[1]/tbar[0]/btn[0]").press
session.findById("wnd[0]/tbar[0]/btn[3]").press
'--------- IPI ----------
IPI_NOME_ARQUIVO = GeraNomeUnicoArquivo(caminho, IPI_NOME_ARQUIVO)
session.findById("wnd[0]/usr/ctxtP_LAYOUT").Text = IPI_LAYOUT
session.findById("wnd[0]/usr/ctxtP_LAYOUT").SetFocus
session.findById("wnd[0]/usr/ctxtP_LAYOUT").caretPosition = 11
session.findById("wnd[0]/tbar[1]/btn[8]").press
session.findById("wnd[0]/usr/cntlCC_ALV/shellcont/shell").contextMenu
session.findById("wnd[0]/usr/cntlCC_ALV/shellcont/shell").selectContextMenuItem "&XXL"
session.findById("wnd[1]/tbar[0]/btn[20]").press
session.findById("wnd[1]/usr/ctxtDY_PATH").Text = caminho
session.findById("wnd[1]/usr/ctxtDY_FILENAME").Text = IPI_NOME_ARQUIVO
session.findById("wnd[1]/usr/ctxtDY_PATH").SetFocus
session.findById("wnd[1]/usr/ctxtDY_PATH").caretPosition = 8
session.findById("wnd[1]/tbar[0]/btn[0]").press
session.findById("wnd[0]/tbar[0]/btn[3]").press
session.findById("wnd[0]/tbar[0]/btn[3]").press
statusBarText = session.findById("wnd[0]/sbar").Text
If statusBarText = "" Then
Sheets(1).Cells(i, 😎 = "Executed Successfully"
Else
Sheets(1).Cells(i, 😎 = statusBarText
session.findById("wnd[0]/tbar[0]/btn[12]").press
End If
i = i + 1
Loop
' Considera que o per�odo a consolidar � o da C3 da planilha Script
periodoConsolidado = Replace(Replace(Trim(Sheets(1).Range("C6").Text), "/", "."), "-", ".")
periodoConsolidado = Replace(periodoConsolidado, " ", "")
If Not (periodoConsolidado Like "##.####" Or periodoConsolidado Like "#.####") Then periodoConsolidado = "SEM_DATA"
' Consolida��o SEPARADA:
ConsolidaApenasUltimoSufixo "ICMS", caminho_BR01, periodoConsolidado, "BR01"
ConsolidaApenasUltimoSufixo "IPI", caminho_BR01, periodoConsolidado, "BR01"
ConsolidaApenasUltimoSufixo "ICMS", caminho_BR02, periodoConsolidado, "BR02"
ConsolidaApenasUltimoSufixo "IPI", caminho_BR02, periodoConsolidado, "BR02"
MsgBox "Processo finalizado com sucesso! Tempo de execu��o: " & VBA.Format(Now - Inicio, "hh:mm:ss")
End Sub
' Nome �nico sem FSO
Function GeraNomeUnicoArquivo(ByVal pasta As String, ByVal baseFile As String) As String
Dim i As Integer, ponto As Integer, nomeNovo As String
nomeNovo = baseFile
i = 1
Do While Len(Dir(pasta & "\" & nomeNovo)) > 0
ponto = InStrRev(baseFile, ".")
If ponto = 0 Then
nomeNovo = baseFile & "_" & i
Else
nomeNovo = Left(baseFile, ponto - 1) & "_" & i & Mid(baseFile, ponto)
End If
i = i + 1
Loop
GeraNomeUnicoArquivo = nomeNovo
End Function
' Agora com empresa informada!
Sub ConsolidaApenasUltimoSufixo(tipo As String, caminho As String, Periodo As String, empresa As String)
Dim NomePadrao As String, arq As String, pos As Integer, nomeFinal As String
Dim sufixoAtual As String, sufixoNum As Long
Dim grupoDict As Object, maiorSufixo As Long
Dim sufixosArr() As String, idx As Integer
Dim grupoArqs As String, NomeConsFinal As String, linhas As Long
Dim wbCons As Workbook, wsCons As Worksheet, wbTemp As Workbook, wsTemp As Worksheet
Set grupoDict = CreateObject("Scripting.Dictionary")
maiorSufixo = 0
If tipo = "ICMS" Then
NomePadrao = "Livro ICMS"
Else
NomePadrao = "Livro IPI"
End If
arq = Dir(caminho & "\" & NomePadrao & "*.xls*")
Do While arq <> ""
If Left(arq, Len(NomePadrao)) = NomePadrao Then
pos = InStrRev(arq, "-")
If pos > 0 Then
nomeFinal = Trim(Mid(arq, pos + 1))
nomeFinal = Left(nomeFinal, InStrRev(nomeFinal, ".") - 1)
Else
nomeFinal = Left(arq, InStrRev(arq, ".") - 1)
End If
If InStrRev(nomeFinal, "_") > 0 Then
sufixoAtual = Mid(nomeFinal, InStrRev(nomeFinal, "_") + 1)
If IsNumeric(sufixoAtual) Then
sufixoNum = CLng(sufixoAtual)
If sufixoNum > maiorSufixo Then maiorSufixo = sufixoNum
If Not grupoDict.Exists(sufixoAtual) Then
grupoDict.Add sufixoAtual, caminho & "\" & arq
Else
grupoDict(sufixoAtual) = grupoDict(sufixoAtual) & "|" & caminho & "\" & arq
End If
End If
Else
sufixoAtual = ""
If Not grupoDict.Exists(sufixoAtual) Then
grupoDict.Add sufixoAtual, caminho & "\" & arq
Else
grupoDict(sufixoAtual) = grupoDict(sufixoAtual) & "|" & caminho & "\" & arq
End If
End If
End If
arq = Dir
Loop
If maiorSufixo > 0 And grupoDict.Exists(CStr(maiorSufixo)) Then
grupoArqs = grupoDict(CStr(maiorSufixo))
ElseIf grupoDict.Exists("") Then
grupoArqs = grupoDict("")
Else
MsgBox "Nenhum arquivo para consolidar do tipo " & tipo, vbExclamation
Exit Sub
End If
sufixosArr = Split(grupoArqs, "|")
If maiorSufixo > 0 Then
NomeConsFinal = empresa & " - " & tipo & "_Consolidado." & Periodo & "_" & maiorSufixo & ".xlsx"
Else
NomeConsFinal = empresa & " - " & tipo & "_Consolidado." & Periodo & ".xlsx"
End If
NomeConsFinal = GeraNomeUnicoArquivo(caminho, NomeConsFinal)
Set wbCons = Workbooks.Add
Set wsCons = wbCons.Sheets(1)
wsCons.Name = tipo & " Consolidado"
linhas = 1
For idx = LBound(sufixosArr) To UBound(sufixosArr)
Set wbTemp = Workbooks.Open(sufixosArr(idx))
Set wsTemp = wbTemp.Sheets(1)
If idx = LBound(sufixosArr) Then
wsTemp.UsedRange.Copy wsCons.Cells(linhas, 1)
linhas = wsCons.Cells(wsCons.Rows.Count, 1).End(xlUp).Row + 1
Else
If wsTemp.UsedRange.Rows.Count > 1 Then
wsTemp.Rows("2:" & wsTemp.UsedRange.Rows.Count).Copy wsCons.Cells(linhas, 1)
linhas = wsCons.Cells(wsCons.Rows.Count, 1).End(xlUp).Row + 1
End If
End If
wbTemp.Close False
Next idx
Application.DisplayAlerts = False
wbCons.SaveAs caminho & "\" & NomeConsFinal
wbCons.Close False
Application.DisplayAlerts = True
End Sub
================== ================ ================= =============== ==================
>>> This is the one I use to close, which only works if I close Excel and reopen it:
Sub Close()
Dim wb As Workbook
For Each wb In Application.Workbooks
If wb.Name <> ThisWorkbook.Name Then
wb.Close SaveChanges:=False
End If
Next wb
End Sub
============= ======================= ====================== =========================
From what I've researched, it seems to be because the Excel opened by SAP is in a second instance, and therefore the closing code doesn't execute.
I've tried several methods (which the AI suggested), but nothing worked; it always opens in a different instance.
Does anyone have a solution or know what can be done to help? I'd be incredibly grateful!
Bluesky
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.