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

  • Pergunta

  • 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...


    sexta-feira, 7 de dezembro de 2012 13:32

Respostas

  • Fiz uns testes e o resultado foi satisfatório.

    Para resolver seu problema, serão necessárias duas etapas: criar um arquivo TXT espelho que o PowerPivot consiga importar, e resumir a base de dados importada pelo PowerPivot numa tabela dinâmica.

    O código para criar o espelho do seu arquivo de texto é:

    Sub Exemplo()
        Dim iFFOrigem As Integer
        Dim IFFDestino As Integer
        Dim sOrigem As String
        Dim sDestino As String
        Dim sLinha As String
        Dim lDoEvents As Long
         
        sOrigem = "D:\Temp\FINAL\FINAL.txt"
        sDestino = "D:\Temp\FINAL\FINAL2.txt"
        
        iFFOrigem = FreeFile
        Open sOrigem For Input As iFFOrigem
        IFFDestino = FreeFile
        Open sDestino For Output As IFFDestino
         
        Print #IFFDestino, "CODPRO" & "," & "QTDE" & "," & "VALOR"
        Do Until EOF(iFFOrigem)
            Line Input #iFFOrigem, sLinha
            sLinha = Mid(sLinha, 1, 14) & "," _
            & Mid(sLinha, 15, 13) & "," _
            & Mid(sLinha, 28, 13)
            Print #IFFDestino, sLinha
            lDoEvents = lDoEvents + 1
            If lDoEvents Mod 500 = 0 Then DoEvents
        Loop
         
        Close #iFFOrigem
        Close #IFFDestino
         
     End Sub

    Em seguida, instale o suplemento PowerPivot para Excel. Entre na janela do PowerPivot, e no menu Início, vá até o grupo "Obter Dados Externos" e clique em "De Texto". Selecione o caminho do arquivo espelho e marque a caixa de seleção "Usar primeira linha como cabeçalhos de coluna".

    O separador utilizado é a vírgula. Depois, clique no botão concluir e no menu Início, grupo Relatórios, clique em Tabela Dinâmica. Agora personalize o relatório em tabela dinâmica como desejar.


    Felipe Costa Gualberto - http://www.ambienteoffice.com.br

    segunda-feira, 24 de dezembro de 2012 17:35
    Moderador

Todas as Respostas

  • Você pode utilizar o ADO para fazer a leitura do TXT, já realizando a contagem com as funções de agrupamento do TSQL no caso SUM() e COUNT().

    Segue um exemplo de uso...

    http://msdn.microsoft.com/en-us/library/ms974559.aspx

    []´s


    Bruno Silva Leite
    officevb.com

    sexta-feira, 7 de dezembro de 2012 13:51
  • Poderia disponibilizar (parte) de um arquivo TXT que você usa para download? Tenho algumas ideias para propor. E sim, é possível alocar o arquivo de texto na memória, desde que ele não tenha mais de 2GB.

    Felipe Costa Gualberto - http://www.ambienteoffice.com.br

    sexta-feira, 7 de dezembro de 2012 23:20
    Moderador
  • Vamos por partes... =)

    1) Postei inicialmente em http://answers.microsoft.com/pt-br/office/forum/office_2010-excel/como-criar-adicionar-e-alterar-registros-de-uma/1df43a41-9a5a-445f-b96f-9f3ae51ece7b  e - inclusive - achei muito interessante a solução, pois meu VBA não incluía utilização de ARRAYs até então (sorry); (ah! mas me indicaram postar aqui!)

    2) Lógico... também desconheço ADO... =(

    3) Meu problema não é a leitura do arquivo ORIGEM (TXT, independente de tamanho =) que no código acima é rápido e não precisaria alocar na memória... mas o tratamento do resultado DESTINO sim! Pois é onde está o gargalo... Acho eu que até o ARRAY seria uma solução mais simples, não ?

    4) Posso gerar um TXT exemplo, mas não sei como disponibilizá-lo aqui no fórum.

    Grato a todos!


    • Editado MLRamos segunda-feira, 10 de dezembro de 2012 11:22 correção de texto
    segunda-feira, 10 de dezembro de 2012 11:21
  • Para disponibilizar um arquivo no fórum (ou em qualquer outro lugar), basta fazer upload do arquivo no SendSpace.com. O site te fornecerá um link de download, que você postará aqui. O mesmo é válido para o SkyDrive.com

    Felipe Costa Gualberto - http://www.ambienteoffice.com.br

    segunda-feira, 10 de dezembro de 2012 21:29
    Moderador
  • Espero que funcione:

    https://skydrive.live.com/redir?resid=58B754533F2CD6F!176

    CODPROD 14 caracteres

    QTDE 13 caracteres (com 3 decimais)

    VALOR 13 caracteres (com 2 decimais)

    Grato novamente.

    terça-feira, 11 de dezembro de 2012 09:50
  • Funcou !? 

    Estou aguardando (ansiosamente) as ideias e propostas...

    =D

    quarta-feira, 12 de dezembro de 2012 15:29
  • Olá,

    Ainda estou verificando a melhor forma de se resolver este problema. Creio que seja com PowerPivot. Para tal, será necessário o uso do Excel 2010.


    Felipe Costa Gualberto - http://www.ambienteoffice.com.br

    domingo, 16 de dezembro de 2012 21:17
    Moderador
  • Não se sinta pressionado! 

    (tá pronto!? =)

    Já vou estudando o assunto... retorno de férias em 11/Jan até lá deve estar pronto né ?

    =P

    Grato novamente.

    quinta-feira, 20 de dezembro de 2012 17:17
  • Fiz uns testes e o resultado foi satisfatório.

    Para resolver seu problema, serão necessárias duas etapas: criar um arquivo TXT espelho que o PowerPivot consiga importar, e resumir a base de dados importada pelo PowerPivot numa tabela dinâmica.

    O código para criar o espelho do seu arquivo de texto é:

    Sub Exemplo()
        Dim iFFOrigem As Integer
        Dim IFFDestino As Integer
        Dim sOrigem As String
        Dim sDestino As String
        Dim sLinha As String
        Dim lDoEvents As Long
         
        sOrigem = "D:\Temp\FINAL\FINAL.txt"
        sDestino = "D:\Temp\FINAL\FINAL2.txt"
        
        iFFOrigem = FreeFile
        Open sOrigem For Input As iFFOrigem
        IFFDestino = FreeFile
        Open sDestino For Output As IFFDestino
         
        Print #IFFDestino, "CODPRO" & "," & "QTDE" & "," & "VALOR"
        Do Until EOF(iFFOrigem)
            Line Input #iFFOrigem, sLinha
            sLinha = Mid(sLinha, 1, 14) & "," _
            & Mid(sLinha, 15, 13) & "," _
            & Mid(sLinha, 28, 13)
            Print #IFFDestino, sLinha
            lDoEvents = lDoEvents + 1
            If lDoEvents Mod 500 = 0 Then DoEvents
        Loop
         
        Close #iFFOrigem
        Close #IFFDestino
         
     End Sub

    Em seguida, instale o suplemento PowerPivot para Excel. Entre na janela do PowerPivot, e no menu Início, vá até o grupo "Obter Dados Externos" e clique em "De Texto". Selecione o caminho do arquivo espelho e marque a caixa de seleção "Usar primeira linha como cabeçalhos de coluna".

    O separador utilizado é a vírgula. Depois, clique no botão concluir e no menu Início, grupo Relatórios, clique em Tabela Dinâmica. Agora personalize o relatório em tabela dinâmica como desejar.


    Felipe Costa Gualberto - http://www.ambienteoffice.com.br

    segunda-feira, 24 de dezembro de 2012 17:35
    Moderador