Call Function e usar planilha já aberta na Function  [Resolvido]

Visual Basic for Aplication e macros no Excel.
Regras do fórum
Sua dúvida foi respondida? Marque como RESOLVIDO em seus tópicos, usando o botão com marca verde. Imagem

Call Function e usar planilha já aberta na Function

Mensagempor TSelem » Sáb Jan 11, 2020 6:37 pm

Prezados, boa tarde!

No meu módulo, eu crio uma cópia de uma planilha modelo, abro a cópia (.xlsx) que foi criada, adiciono uma aba, então chamo uma função (Call Function) para preencher as células nessa nova aba.

O problema é que dentro dessa Function, estou com dificuldades para declarar que já existe uma planilha aberta e que quero trabalhar nela.

Pelo que entendi, tenho que colocar as variáveis novamente, então quando faço o comando Set xlw = xl.Workbooks.Open(CurrentProject.Path & "\EXTRAÇÃO\ANEXO I - " & MunSub & " - ITEM 1 - SAA.xlsx"), começa o erro, pois esse arquivo já estava aberto em segundo plano.

Vejam abaixo:

Código: Selecionar todos
Public Function VolumesTot(MunSub As String, SubVol As String)


Dim xl As New Excel.Application
Dim xlw As Excel.Workbook

Dim rst3 As DAO.Recordset
Dim Sel3 As String

'Carrego o conjunto de registros
'--------------------------------------------------------------

Sel3 = "select distinct * from 0110_VOL where Município = '" & MunSub & "' and Subsistema ='" & SubVol & "'" 'REMOVER DISTINCT
Set rst3 = CurrentDb.OpenRecordset(Sel3)

Set xlw = xl.Workbooks.Open(CurrentProject.Path & "\EXTRAÇÃO\ANEXO I - " & MunSub & " - ITEM 1 - SAA.xlsx")

Do While Not rst3.EOF
    xlw.Application.Cells(1, 3).Value = rst3![Vendido]
    xlw.Application.Cells(5, 3).Value = rst3![Comprado]
   
    rst3.MoveNext

Loop

xlw.Close True
   
Set xlw = Nothing

rst3.Close

Set rst3 = Nothing

End Function
TSelem
Membro
Membro
 
Mensagens: 4
Registrado em: Sáb Jan 11, 2020 6:16 pm
Has thanked: 2 times
Have thanks: 0 time

{ SO_SELECT }

Re: Call Function e usar planilha já aberta na Function

Mensagempor TSelem » Dom Jan 12, 2020 12:56 am

Pessoal,

Este exemplo que coloquei abaixo define minha dúvida um pouco melhor.

O código abaixo está em um módulo do ACCESS.

No sub TESTEEXCEL() eu abro o workbook TESTE1.xlsx e chamo a Function ChamaBB("EURECA").

Na Function ChamaBB, quero preencher a célula C3 com o texto "EURECA", mas estou com dificuldade em acessar o workbook que foi aberto no sub anterior.

Código:
Código: Selecionar todos

Option Compare Database

Sub TESTEEXCEL()

Dim xl As New Excel.Application
Dim xlw As Excel.Workbook

Set xlw = xl.Workbooks.Open(CurrentProject.Path & "\TESTE1.xlsx")

Call ChamaBB("EURECA")

Set xlw = Nothing

MsgBox "Planilha gerada com sucesso!", , "Concluído!"

End Sub

____________________________________________________________________________________________
Function ChamaBB(Texto As String)

Dim xl As Excel.Application
Dim xlw As Excel.Workbook

Set xlw = xl.Workbooks.Open(CurrentProject.Path & "\TESTE1.xlsx")

xlw.Application.Cells(3, 3).Value = Texto

Set xlw = Nothing

End Function


Eu poderia fechar o workbook no Sub e mandar abrir novamente dentro da Function, porém, apesar de eu ser leigo, imagino que ficar abrindo e fechando o workbook consumiria muita memória e deixaria a macro lenta.

Não sei o que fazer.
TSelem
Membro
Membro
 
Mensagens: 4
Registrado em: Sáb Jan 11, 2020 6:16 pm
Has thanked: 2 times
Have thanks: 0 time

Re: Call Function e usar planilha já aberta na Function

Mensagempor EdsonBR » Dom Jan 12, 2020 1:51 am

Bem vindo, TSelem

Vc poderia declarar a variável WorkBook apenas uma vez mas em nível de módulo o que a tornaria disponível para as demais rotinas:
Código: Selecionar todos
Option Compare Database
Dim xlw As Excel.Workbook
Sub TESTEEXCEL()
  Dim xl As New Excel.Application
  Set xlw = xl.Workbooks.Open(currentproject.Path & "\TESTE1.xlsx")
  Call ChamaBB("EURECA")
  MsgBox "Planilha gerada com sucesso!", , "Concluído!"
End Sub
Function ChamaBB(Texto As String)
  xlw.Application.Cells(3, 3).Value = Texto
  Set xlw = Nothing
End Function


Ou poderia passar o Workbook como um argumento (por valor) à sua Function:
Código: Selecionar todos
Option Explicit
Sub TESTEEXCEL()
  Dim xl As New Excel.Application
  Dim xlw As Excel.Workbook
  Set xlw = xl.Workbooks.Open(currentproject.Path & "\TESTE1.xlsx")
  Call ChamaBB("EURECA", xlw)
  MsgBox "Planilha gerada com sucesso!", , "Concluído!"
End Sub
Function ChamaBB(Texto As String, wbk As Excel.Workbook)
  wbk.Application.Cells(3, 3).Value = Texto
  Set xlw = Nothing
End Function
Imagem

For this post the author EdsonBR thanked:
TSelem (Dom Jan 12, 2020 3:23 am)
Avatar do usuário
EdsonBR
Membro
Membro
 
Mensagens: 677
Registrado em: Qui Nov 05, 2015 11:43 pm
Localização: Joinville, SC
Has thanked: 148 times
Have thanks: 444 times

Re: Call Function e usar planilha já aberta na Function

Mensagempor TSelem » Dom Jan 12, 2020 3:23 am

Prezado EdsonBR,

Agradeço as boas-vindas e sua ajuda.

Não consegui declarar o Workbook em nível de módulo pois o Sub e a Function estão em módulos diferentes devido ao tamanho.

Usando o Workbook como argumento na Function atendeu o que eu queria, agradeço muito sua ajuda. Como pode ver pelo horário, estou há um bom tempo quebrando a cabeça num projeto.

Entretanto, surgiu um outro problema numa outra parte do meu projeto. Esse problema já tinha me incomodado antes e também não encontrei solução em lugar nenhum.

Para o meu projeto, basicamente o que preciso fazer é:
1º Criar uma cópia de um template (ex: TESTE2.xlsx)
2º Abro a cópia e crio uma quantidade variável de abas a partir de um Template.xltx
3º Insiro dados em cada uma das abas que foram criadas

O problema acontece na Sub CriarAbas(), na linha "xlw.Sheets.Add Type:="" & CurrentProject.Path & "\Template.xltx"" quando contador = 2.

Segue o código:

Código: Selecionar todos
Option Compare Database

Sub CrioPlanilha()

Dim CaminhoTemplate As String

CaminhoTemplate = CurrentProject.Path & "\TESTE1.xlsx"

FileCopy CaminhoTemplate, CurrentProject.Path & "\TESTE2.xlsx"

Call CrioAbas

MsgBox "Planilha gerada com sucesso!", , "Concluído!"

End Sub
________________________________________________________________________
Sub CrioAbas()

Dim xl As New Excel.Application
Dim xlw As Excel.Workbook
Dim contador As String

Set xlw = xl.Workbooks.Open(CurrentProject.Path & "\TESTE2.xlsx")

contador = 1

Do Until contador = 3
    xlw.Sheets.Add Type:="" & CurrentProject.Path & "\Template.xltx" '<----- PROBLEMA AQUI QUANTO contador = 2 - "A variável do objeto ou a variável do blo 'Witch' não foi definida."
    xlw.ActiveSheet.Name = "Modelo" & contador & ""
   
    Call PreenchoAbas("EURECA", xlw)
   
    contador = contador + 1
Loop

xlw.Close True

Set xlw = Nothing


End Sub

________________________________________________________________________
Function PreenchoAbas(Texto As String, wbk As Excel.Workbook)

Dim contador2 As String

contador2 = 1

Do Until contador2 = 4
    wbk.Application.Cells(3, 3).Value = Texto
    contador2 = contador2 + 1
Loop

Set wbk = Nothing

End Function
TSelem
Membro
Membro
 
Mensagens: 4
Registrado em: Sáb Jan 11, 2020 6:16 pm
Has thanked: 2 times
Have thanks: 0 time

Re: Call Function e usar planilha já aberta na Function  [Resolvido]

Mensagempor EdsonBR » Dom Jan 12, 2020 9:42 pm

1)
TSelem escreveu:Não consegui declarar o Workbook em nível de módulo pois o Sub e a Function estão em módulos diferentes devido ao tamanho.

Nesse caso, ao invés de Dim, use Public para declarar sua variável. Dessa forma, ela ficará disponível para todos os módulos do seu Projeto.

2) O erro está ocorrendo pq em sua Function PreencheAbas o argumento wbk é forçado a ser Nothing, portanto destrói a referência a ele. Elimine a linha Set wbk = Nothing no final da Function.

3) Notei que vc declarou todos os contadores como String, o que não seria o mais indicado. Alguma razão para isso? O ideal seria Byte ou Integer ou Long (preferivelmente esse último, visto que o VBA internamente acaba usando ele mesmo que sejam declarados os outros dois).
Imagem

For this post the author EdsonBR thanked:
TSelem (Ter Jan 14, 2020 7:41 pm)
Avatar do usuário
EdsonBR
Membro
Membro
 
Mensagens: 677
Registrado em: Qui Nov 05, 2015 11:43 pm
Localização: Joinville, SC
Has thanked: 148 times
Have thanks: 444 times

Re: Call Function e usar planilha já aberta na Function

Mensagempor TSelem » Ter Jan 14, 2020 7:41 pm

EdsonBR escreveu:1)
TSelem escreveu:Não consegui declarar o Workbook em nível de módulo pois o Sub e a Function estão em módulos diferentes devido ao tamanho.

Nesse caso, ao invés de Dim, use Public para declarar sua variável. Dessa forma, ela ficará disponível para todos os módulos do seu Projeto.

2) O erro está ocorrendo pq em sua Function PreencheAbas o argumento wbk é forçado a ser Nothing, portanto destrói a referência a ele. Elimine a linha Set wbk = Nothing no final da Function.

3) Notei que vc declarou todos os contadores como String, o que não seria o mais indicado. Alguma razão para isso? O ideal seria Byte ou Integer ou Long (preferivelmente esse último, visto que o VBA internamente acaba usando ele mesmo que sejam declarados os outros dois).


EdsonBR,

Muito obrigado pela sua ajuda! Consegui resolver meu problema.
TSelem
Membro
Membro
 
Mensagens: 4
Registrado em: Sáb Jan 11, 2020 6:16 pm
Has thanked: 2 times
Have thanks: 0 time


Voltar para VBA & Macros

Quem está online

Usuários navegando neste fórum: sumoner80 e 6 visitantes