Como criar, adicionar e alterar registros de uma tabela (ou matriz) em memória no VBA ?

Question

Suponham que eu tenha um arquivo ORIGEM (TXT) de PDV, ou seja, há "n" registros do mesmo produto com quantidades e valores de um mesmo período.

A partir da leitura da ORIGEM quero gerar um DESTINO com uma listagem de código de produto e totais de quantidade e valor.

A priori, eu poderia gerar uma Tabela Dinâmica, certo ? Porém o TXT pode ter mais de 1.048.576 linhas, e ler este arquivo para o XLSX e depois gerar duas tabelas dinâmicas, por exemplo, e a partir destas uma terceira contendo o resultado tem se demonstrado muito lento!

Na macro, resolvi o problema da leitura do TXT com mais de 1.0485.76 linhas fazendo um loop e alterando apenas o STARTROW no comando OPEN:

PRILINHA = 0

"DO

Workbooks.OpenText Filename:="PDV_AAAAMM.TXT", Origin:=xlWindows, StartRow:=PRILINHA, DataType:=xlFixedWidth, FieldInfo:= _ Array(campo1, campo2, campo3.....) , TrailingMinusNumbers:=True

ULTLINHA = ActiveCell.SpecialCells(xlLastCell).Row

PRILINHA = PRILINHA + 1048576

...

...

Loop Until ULTLINHA <> 1048576".

Agora mudei o esquema de leitura conforme abaixo, e ganhei muuuuuuuito tempo: de minutos para segundos (comparando apenas leitura x leitura)! Ficou mais ou menos assim:

Do While Not EOF(1)

Line Input #1, Linha

CODPROD = CDec(Mid(Linha, 10, 14))

QTDE = CCur(Mid(Linha, 30, 13)) / 1000

VALOR = CCur(Mid(Linha, 50, 16)) / 100

...

...

Loop

Close #1

Estou com dificuldades em trabalhar com os registros e campos lidos, pois gravar os resultados para uma planilha também têm se demonstrado lentos devido a velocidade para encontrar um registro CODPROD já utilizado e, caso não tenha sido, criar um novo ao final da tabela. Esta tabela DESTINO, não ultrapassará (nem chega perto) do meu cadastro de produtos (lógico) que é de 50.000 registros aproximadamente.

Então acredito que minha saída seja trabalhar os registros lidos numa matriz/tabela em memória. Mas não sei como gerar uma com 3 colunas (CODPROD, QTDE e VALOR) por "n" linhas variáveis (não queria gerar uma com o número máximo do cadastro de produtos).

Outra dificuldade é como localizar o registro com o CODPROD que a priori não tem índice (sem problemas, não ter) e somar as quantidades e valores de um novo registro lido.

Agradeço a ajuda...

Answer 1

A discussão também está em andamento em: http://social.msdn.microsoft.com/Forums/pt-BR/vbapt/thread/c2cbc5e8-4204-4fcd-8e8a-3c7e6168e451

Answer 2

Bom, em primeiro lugar devo confessar que nunca tinha utilizado os objetos tipo Dictionary... show de bola !

Estou estudando mais sobre o assunto. A leitura de dados para rngdata é fantástica! (muito rápida)

Mas preciso adaptar alguns itens, como a leitura com Line input da ORIGEM, etc, etc...

Espero logo postar os resultados...

Muito obrigado por enquanto.

Ramos

Answer 3

MLRamos

Faltou dizer

O CODPRO que você lê

CODPROD = CDec(Mid(Linha, 10, 14))

Na minha macro equivale a

rngdata(i, 1)

Ou seja, a coluna 1 dos dados de entrada.

M.

Answer 4

Oi MLRamos

Veja se o exemplo abaixo ajuda.

Não é igual ao teu caso, pois não estou lendo linhas de um arquivo externo, mas isto parece que você já otimizou com

Do While Not EOF(1)

Line Input #1, Linha

CODPROD = CDec(Mid(Linha, 10, 14))

QTDE = CCur(Mid(Linha, 30, 13)) / 1000

VALOR = CCur(Mid(Linha, 50, 16)) / 100

...

...

Loop

Close #1

Estou apenas tentando mostrar como trabalhar colocando os dados na memória.

Usei dois objetos Dicionário - um para pegar a QTDE e o outro para pegar o VALOR.

Se você não está acostumado a usar o objeto Dicionário, dê uma olhada em:

Uma introdução

http://www.techbookreport.com/tutorials/vba_dictionary.html

Excelente artigo

http://www.experts-exchange.com/Software/Office_Productivity/Office_Suites/MS_Office/A_3391-Using-the-Dictionary-Class-in-VBA.html

Depois crio uma matriz (array) de duas dimensões para armazenar os resultados em memória e, no fim, passo de uma só vez a matriz para a planilha.

O exemplo é muito simples. Imagine em Plan1

Produto	Quantidade	Valor
P1	10	150,1
P2	11	151,1
P3	12	152,1
P1	13	153,1
P2	14	154,1
P3	15	155,1
P1	16	156,1
P2	17	157,1
P3	18	158,1
P1	19	159,1
P2	20	160,1
P3	21	161,1

Rode esta macro

Sub aTest()

    Dim ProdQ As Object, ProdV As Object

    Dim QTDE As Double, VALOR As Double

    Dim firstRow As Long, lastRow As Long

    Dim rngdata As Variant, rngResult() As Variant

    Dim v As Variant, i As Long

    'Cria dois objetos Dicionário

    Set ProdQ = CreateObject("Scripting.Dictionary")

    Set ProdV = CreateObject("Scripting.Dictionary")

    'Define os dados de entrada

    With Sheets("Plan1") '<--Planilha de Dados

        firstRow = 2

        lastRow = .Cells(.Rows.Count, "A").End(xlUp).Row

        rngdata = .Range("A" & firstRow & ":C" & lastRow).Value

    End With

    For i = 1 To lastRow - firstRow + 1

        'Leitura dos dados

        QTDE = rngdata(i, 2)

        VALOR = rngdata(i, 3)

        'Verifica se o Produto já foi incluído nos dicionários

        If ProdQ.Exists(rngdata(i, 1)) Then

            'Se sim, soma as QTDE e VALOR aos valores anteriores

            ProdQ.Item(rngdata(i, 1)) = ProdQ.Item(rngdata(i, 1)) + QTDE

            ProdV.Item(rngdata(i, 1)) = ProdV.Item(rngdata(i, 1)) + VALOR

        Else

            'Se não, inclui o Produto nos dois Dicionários

            ProdQ.Add rngdata(i, 1), QTDE

            ProdV.Add rngdata(i, 1), VALOR

        End If

    Next i

    'Redimensiona o array de resultados conforme o número de Produtos

    ReDim rngResult(1 To ProdQ.Count, 1 To 3)

    i = 0

    'Faz um loop para percorrer todos os items dos Dicionarios

    ' e atribui os valores ao array de resultados

    For Each v In ProdQ.keys

        i = i + 1

        rngResult(i, 1) = v

        rngResult(i, 2) = ProdQ.Item(v)

        rngResult(i, 3) = ProdV.Item(v)

    Next v

    With Sheets("Plan2") '<--Planilha de resultados

        'Limpamdo as colunas A-C

        .Range("A:C").ClearContents

        'Colocando cabeçalhos

        .Range("A1:C1").Value = Array("Produto", "Quantidade", "Valor")

        'Passando o array de resultados de uma só vez

        .Range("A2").Resize(ProdQ.Count, 3).Value = rngResult

    End With

End Sub

E o resultado em Plan2 será

Produto	Quantidade	Valor
P1	58	618,4
P2	62	622,4
P3	66	626,4

Como eu disse, é um exemplo muito simples, mas mostra como trabalhar em memória.

Espero que ajude.

M.

Answer 5

Olá MLRamos,

Para questões envolvendo VBA, poste sua dúvida no fórum de VBA no link abaixo:

http://social.msdn.microsoft.com/Forums/pt-BR/vbapt/threads

Até mais!!!