Problems to close another spreadsheet by VBA

Asked

Viewed 569 times

2

I am running the code below to compare data from different spreadsheets. It is running correctly, but I am not able to close the source sheets of the data. I have tried with '.Close', '.Quit' and '= Nothing', anyway the error 'subscript out of range' appears".

Sub teste()

extrato = Range("B1").Value
comparativa = Range("B2").Value

Dim ws As Workbook
Set ws = Application.Workbooks.Add

    Range("B2").Select
    ActiveCell.FormulaR1C1 = "Contraparte"
    Range("C2").Select
    ActiveCell.FormulaR1C1 = "Faturamento"


Data = Replace(Replace(Now(), ":", "."), "/", "_")
ws.SaveAs ("C:\Users\leandro.lazari\Desktop\Financeiro\" & Data & ".xlsx")

'Abre Planilha extrato e copia lançamentos e valores
Workbooks.Open ("C:\Users\leandro.lazari\Desktop\Financeiro\" & extrato & ".xls")
    Range("E12:F12").Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.Copy

'Cola dados de extrato
Workbooks.Open ("C:\Users\leandro.lazari\Desktop\Financeiro\" & Data & ".xlsx")
    Range("E2").Select
    ActiveSheet.Paste

'Abre Planilha extrato e copia lançamentos e valores
Workbooks.Open ("C:\Users\leandro.lazari\Desktop\Financeiro\" & comparativa & ".xlsx")

    Range("A2:B2").Select
    Range(Selection, Selection.End(xlDown)).Select

    With Selection
        .VerticalAlignment = xlCenter
        .WrapText = False
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlLTR
        .MergeCells = False
    End With
    Selection.Copy

'Cola dados de extrato
Workbooks.Open ("C:\Users\leandro.lazari\Desktop\Financeiro\" & Data & ".xlsx")
    Range("H2").Select
    ActiveSheet.Paste


'Checa os valores que convergem e retorna as convergências
i = 2
j = 3
    Do Until Range("I" & i) = ""

        Range("K" & i).Select
            ActiveCell.FormulaR1C1 = "=IFERROR(IF(VLOOKUP(R[0]C[-2],C[-5],1,FALSE), ""ENCONTRADO""), ""NÃO ENCONTRADO"")"

            If Cells(i, 11).Value = "ENCONTRADO" Then
                Cells(i, 8).Copy
                Cells(j, 2).PasteSpecial
                Cells(i, 9).Copy
                Cells(j, 3).PasteSpecial

            j = j + 1
            End If
      i = i + 1
    Loop

'limpa dados utilizados
Range("E:Z").ClearContents
Range("A:Z").ClearFormats

'formata largura das colunas e valores como moeda
    Columns("B:B").Select
    Columns("B:B").EntireColumn.AutoFit
    Columns("C:C").Select
    Columns("C:C").EntireColumn.AutoFit
    Selection.Style = "Currency"
        Range("B2:C2").Select
    Selection.Font.Bold = True
    Selection.Font.Size = 12

ActiveWorkbook.Save
ActiveWorkbook.Close

End Sub
vba excel-vba
  • 1

    Dude, before Activeworkbook.Close, try to activate the Workbook you want to close using the Windows command ("C: archiveQueVoceQuerFechar").Activate. Test there and return us if it worked.

  • 1

    I had already tried but without windows in front. I put the way I said and it didn’t work either. Keeps popping the same error! : / Could it be because there is still an active selection of worksheets?

  • 1

    Same mistake?! I’m intrigued, I even deleted the rsrs answer. I’ll do some more tests.

  • 1

    Dude, your code works perfectly here, with test sheets that I created. I could make the spreadsheets that you’re testing available?

  • I can make it available! I just don’t know how you do it! kkk.. da?

  • I also don’t know if it’s possible here, but maybe you can use google drive or one drive.

  • That may be! Let me ask you... What is your version of excel?

  • 1

    I’m wearing the 2010.

  • 1

    Sometimes it’s because of the version.. hj will not have more time, but next week I’ll test in 2010 to see if it works and I’ll give you a feedback here! Anyway, thanks for your help

  • Let’s go continue this discussion in chat.

Show 5 more comments

1 answer

1

With this code snippet you can close any workbook.

Dim wb As Workbook

'nesse caso eu atribuiria uma referência a variável wb
Set wb = Application.Workbooks.Open("<caminho\seuArquivo.xlsx>")

'com a referência atribuida ao WorkBook você pode fechar com o .Close
wb.Close

Browser other questions tagged vba excel-vba

You are not signed in. Login or sign up in order to post.