Thank you in advance for trying to help me.
I have a somewhat complex programming where I use a file to initialize 2 macros into other files.
At the moment of closing the files, I open a txt file in excel, paste the contents of my spreadsheet in this new file, save the file and close as follows:
Open archivotxt.txt in excel (archivotxt.xls) Paste content in archivotxt.xls Save archivotxt.xls in txt (archivotxt.txt) Close workbook archivotxt.txt
In this last moment my Excel closes and a message appears saying that Excel is not working, followed by a message, "We are trying to restart Excel".
Below is the code where the error occurs:
Sub closure ()
Dim racffim As String
Dim nometxt As String
Dim datatratada As String
Dim final As Integer
Dim nomeplant As String
Dim b As Integer
Dim wb As Workbook
Dim Demora As Long
racffim = Environ("UserName")
Application.EnableEvents = True
Set wb = Workbooks("MTT_" & racffim & ".xlsm")
wb.Activate
wb.Save
Application.Run "MTT_" & racffim & ".xlsm!Auto_Close"
wb.Close
Application.EnableEvents = False
'Completa as atividades que não foram finalizadas com o hr de fechamento da planilha - Coment de Finalizacao Automatica
ThisWorkbook.Activate
Range("A1").Select
Do While ActiveCell.Value <> ""
If ActiveCell.Offset(0, 2) = "" Then
ActiveCell.Offset(0, 2).Value = Now
ActiveCell.Offset(0, 3).Value = ActiveCell.Offset(0, 2).Value - ActiveCell.Offset(0, 1).Value
ActiveCell.Offset(0, 5).Value = "Automatica Final"
End If
ActiveCell.Offset(1, 0).Select
Loop
Range("A1").Select
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
Application.DisplayAlerts = True
'Nome do arquivo de base de dados
datatratada = Left(Date, 2) & Mid(Date, 4, 2) & Right(Date, 4)
nometxt = Left(ThisWorkbook.Path, Len(ThisWorkbook.Path) - 7) + "Base_de_Arquivos" + "\" + racffim + "_" + datatratada
Application.DisplayAlerts = False
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
******ERRO ACONTECE DENTRO DESTE IF**********
'Se arquivo de base de dados já existe no diretório:
If Dir(nometxt & ".txt") <> vbNullString Then
'Copia todos os dados do timesheet
ThisWorkbook.Activate
Sheets("bancodados").Range("A2:F2").Select
If ActiveCell.Offset(1, 0).Value <> "" Then
Range(Selection, Selection.End(xlDown)).Select
End If
Selection.Copy
'Abre arquivo existente de base de dados
Workbooks.OpenText Filename:=nometxt & ".txt", Origin _
:=xlMSDOS, StartRow:=1, DataType:=xlDelimited, TextQualifier:=xlDoubleQuote, Tab:=True, FieldInfo:=Array(1, 1), TrailingMinusNumbers:=True
Set bkt = ActiveWorkbook
DoEvents
'Encontra a ultima linha vazia do arquivo, cola as marcações, salva o arquivo e fecha (sem janela de salvar)
Range("A1").Select
If ActiveCell.Offset(1, 0).Value <> "" Then
Selection.End(xlDown).Select
End If
ActiveCell.Offset(1, 0).Select
ActiveSheet.Paste
bkt.SaveAs Filename:=nometxt, FileFormat:=xlText, CreateBackup:=False
bkt.Close False ******O PROGRAMA TRAVA AQUI********
Set bkt = Nothing
''''''''''''''''''''''''''''''''''''''''''''''''''''''
'Se arquivo não existe:
Else
'Salva planilha bancodedados como arquivo de texto (sem janela de salvar)
ActiveWorkbook.SaveAs Filename:=nometxt, FileFormat:=xlText, CreateBackup:=False
End If
'''''''''''''''''''''''''''''''''''''
MsgBox ("Marcações Salvas")
Application.IgnoreRemoteRequests = False
'Fecha workbook caso existam outros workbooks abertos ou fecha a application caso seja o unico workbook aberto
Application.DisplayAlerts = True
ThisWorkbook.Saved = True
'If Workbooks.Count > 1 Then
ThisWorkbook.Close False
'Else
'Application.Quit
'End If
End Sub
Does anyone have any idea what's going on?
Thank you!