Fóruns
Microsoft Excel - 2...
2024 - VBA & Macros
Erro ao importar .c...
 
Notifications
Clear all

Erro ao importar .csv [.Refresh BackgroundQuery:=False]

 
2024 - VBA & Macros
3 Posts
2 Usuários
0 Reactions
1,464 Visualizações
RSS
 hc3
(@hc3)
Posts: 8
Active Member
Topic starter
 

Iae pessoal, sou novo aqui no forum, estou automatizando uma planilha e então comecei a ver um pouco de VBA, resolvi gerar um código que importa um arquivo .csv que está em um diretório específico, depois ele faz um corte em nas colunas que não vou precisar e depois é feito algumas formatações, o problema é o seguinte, o código funciona quando quer e infelizmente não tenho conhecimento necessário na linguagem para consegui resolver sozinho vou colocar aqui o meu código e tentar explicar meu cenário desde já muito obrigado.

comentei o código, esse foi um código gerado pelo excell e eu dei uma editada de leve

Sub macroAtualizaDadosPedidos()
'
' macroAtualizaDadosPedidos Macro
' atualiza histórico de pedidos
' realiza a sequencia de passos para atualizar os pedidos

'
    Call importarPed
    Call formatarPedidos
    Call colocarFiltros
    Call formatarColunas
    Call removePedSemDev
    

End Sub

Sub formatarPedidos()
'
' formatarPedidos Macro
' deixa o arquivo de pedidos no formato padrão
' remove algumas colunas do arquivo de pedidos

'
    Range("B:B,F:F,H:H,I:I,J:J,N:N,O:O,P:P,Q:Q,R:R,S:S,T:T,U:U,X:X,Y:Y").Select
    Range("Y1").Activate
    ActiveWindow.ScrollColumn = 2
    ActiveWindow.ScrollColumn = 3
    ActiveWindow.ScrollColumn = 4
    ActiveWindow.ScrollColumn = 5
    ActiveWindow.ScrollColumn = 6
    ActiveWindow.ScrollColumn = 7
    ActiveWindow.ScrollColumn = 8
    ActiveWindow.ScrollColumn = 9
    ActiveWindow.ScrollColumn = 10
    ActiveWindow.ScrollColumn = 11
    ActiveWindow.ScrollColumn = 12
    ActiveWindow.ScrollColumn = 13
    ActiveWindow.ScrollColumn = 14
    ActiveWindow.ScrollColumn = 15
    Range( _
        "B:B,F:F,H:H,I:I,J:J,N:N,O:O,P:P,Q:Q,R:R,S:S,T:T,U:U,X:X,Y:Y,Z:Z,AB:AB,AD:AD"). _
        Select
    Range("AD1").Activate
    ActiveWindow.SmallScroll ToRight:=14
    Range( _
        "B:B,F:F,H:H,I:I,J:J,N:N,O:O,P:P,Q:Q,R:R,S:S,T:T,U:U,X:X,Y:Y,Z:Z,AB:AB,AD:AD,AK:AK,AL:AL,AP:AP,AQ:AQ,AR:AR,AS:AS" _
        ).Select
    Range("AS1").Activate
    ActiveWindow.SmallScroll ToRight:=12
    Union(Range( _
        "BH:BH,B:B,F:F,H:H,I:I,J:J,N:N,O:O,P:P,Q:Q,R:R,S:S,T:T,U:U,X:X,Y:Y,Z:Z,AB:AB,AD:AD,AK:AK,AL:AL,AP:AP,AQ:AQ,AR:AR,AS:AS,AX:AX,AY:AY,AZ:AZ,BC:BC,BD:BD,BE:BE,BF:BF" _
        ), Columns("BG:BG")).Select
    Range("BH1").Activate
    ActiveWindow.SmallScroll ToRight:=9
    Selection.Delete Shift:=xlToLeft
    ActiveWindow.ScrollColumn = 48
    ActiveWindow.ScrollColumn = 44
    ActiveWindow.ScrollColumn = 39
    ActiveWindow.ScrollColumn = 33
    ActiveWindow.ScrollColumn = 19
    ActiveWindow.ScrollColumn = 12
    ActiveWindow.ScrollColumn = 6
    ActiveWindow.ScrollColumn = 1
    Cells.Select
    Cells.EntireColumn.AutoFit
    Range("A1").Select
End Sub


Sub importarPed()
'
' importarPed Macro
' faz a importação do arquivo .csv

'
    Dim url As String
    url = "C:bancoRNPEDIDOS.CSV"
    
    With ActiveSheet.QueryTables.Add(Connection:="TEXT;" & url, Destination:=Range("$A$1"))
        .FieldNames = True
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .TextFilePromptOnRefresh = False
        .TextFilePlatform = 850
        .TextFileStartRow = 1
        .TextFileParseType = xlDelimited
        .TextFileTextQualifier = xlTextQualifierDoubleQuote
        .TextFileConsecutiveDelimiter = False
        .TextFileTabDelimiter = True
        .TextFileSemicolonDelimiter = True
        .TextFileCommaDelimiter = False
        .TextFileSpaceDelimiter = False
        .TextFileTrailingMinusNumbers = True
        .Refresh BackgroundQuery:=False
    End With

End Sub



Sub colocarFiltros()
'
' colocarFiltros Macro
' coloca filtros no cabeçalho
'

'
    Range("A1:AK1").Select
    Selection.AutoFilter
End Sub

Sub formatarColunas()
'
' formatarColunas Macro
' formata o tamanho das colunas
'

'
    Cells.Select
    Cells.EntireColumn.AutoFit
End Sub


Sub removePedSemDev()
'
' removePedSemDev Macro
' remove pedidos sem devolução
' remove os pedidos sem devolução e os pedidos consignados

'
    Range("A2").Select
    ActiveWindow.SmallScroll ToRight:=17
    ActiveSheet.Range("$A$1:$AK$50117").AutoFilter Field:=27, Criteria1:=Array( _
        "170", "171", "20", "3", "5", "50", "553", "99"), Operator:=xlFilterValues
    ActiveWindow.ScrollColumn = 17
    ActiveWindow.ScrollColumn = 13
    ActiveWindow.ScrollColumn = 8
    ActiveWindow.ScrollColumn = 6
    ActiveWindow.ScrollColumn = 4
    ActiveWindow.ScrollColumn = 3
    ActiveWindow.ScrollColumn = 1
    ActiveWindow.SmallScroll ToRight:=4
    ActiveWindow.ScrollColumn = 4
    ActiveWindow.ScrollColumn = 3
    ActiveWindow.ScrollColumn = 2
    ActiveWindow.ScrollColumn = 1
    Rows("55:50392").Select
    Selection.Delete Shift:=xlUp
    ActiveWindow.SmallScroll ToRight:=13
    ActiveSheet.Range("$A$1:$AK$48897").AutoFilter Field:=27
    ActiveWindow.ScrollColumn = 13
    ActiveWindow.ScrollColumn = 12
    ActiveWindow.ScrollColumn = 10
    ActiveWindow.ScrollColumn = 8
    ActiveWindow.ScrollColumn = 6
    ActiveWindow.ScrollColumn = 3
    ActiveWindow.ScrollColumn = 2
    ActiveWindow.ScrollColumn = 1
    ActiveWindow.SmallScroll ToRight:=2
    Columns("T:T").Select
    ActiveSheet.Range("$A$1:$AK$48897").AutoFilter Field:=20, Criteria1:="0"
    Rows("2:50123").Select
    Range("C2").Activate
    Selection.Delete Shift:=xlUp
    ActiveSheet.Range("$A$1:$AK$1699").AutoFilter Field:=20
    ActiveWindow.ScrollColumn = 2
    ActiveWindow.ScrollColumn = 3
    ActiveWindow.ScrollColumn = 4
    ActiveWindow.ScrollColumn = 5
    ActiveWindow.ScrollColumn = 6
    ActiveWindow.ScrollColumn = 7
    ActiveWindow.ScrollColumn = 8
    ActiveWindow.ScrollColumn = 9
    ActiveWindow.ScrollColumn = 10
    ActiveWindow.ScrollColumn = 11
    ActiveWindow.ScrollColumn = 12
    ActiveWindow.ScrollColumn = 13
    ActiveWindow.ScrollColumn = 14
    ActiveWindow.ScrollColumn = 15
    ActiveWindow.ScrollColumn = 16
    ActiveWindow.ScrollColumn = 17
    ActiveWindow.ScrollColumn = 18
    ActiveWindow.ScrollColumn = 19
    ActiveWindow.ScrollColumn = 20
    ActiveWindow.ScrollColumn = 22
    ActiveWindow.ScrollColumn = 23
    ActiveWindow.ScrollColumn = 24
    ActiveWindow.ScrollColumn = 25
    ActiveWindow.ScrollColumn = 26
    ActiveWindow.ScrollColumn = 27
    ActiveWindow.ScrollColumn = 28
    ActiveWindow.ScrollColumn = 29
    ActiveWindow.ScrollColumn = 30
    ActiveWindow.ScrollColumn = 31
    ActiveWindow.ScrollColumn = 32
    ActiveWindow.ScrollColumn = 33
    ActiveWindow.ScrollColumn = 34
    ActiveWindow.ScrollColumn = 33
    ActiveWindow.ScrollColumn = 32
    ActiveWindow.ScrollColumn = 30
    ActiveWindow.ScrollColumn = 29
    ActiveWindow.ScrollColumn = 28
    ActiveWindow.ScrollColumn = 26
    ActiveWindow.ScrollColumn = 24
    ActiveWindow.ScrollColumn = 23
    ActiveWindow.ScrollColumn = 21
    ActiveWindow.ScrollColumn = 18
    ActiveWindow.ScrollColumn = 16
    ActiveWindow.ScrollColumn = 14
    ActiveWindow.ScrollColumn = 13
    ActiveWindow.ScrollColumn = 12
    ActiveWindow.ScrollColumn = 11
    ActiveWindow.ScrollColumn = 10
    ActiveWindow.ScrollColumn = 9
    ActiveWindow.ScrollColumn = 8
    ActiveWindow.ScrollColumn = 7
    ActiveWindow.ScrollColumn = 6
    ActiveWindow.ScrollColumn = 5
    ActiveWindow.ScrollColumn = 4
    ActiveWindow.ScrollColumn = 3
    ActiveWindow.ScrollColumn = 2
    ActiveWindow.ScrollColumn = 1
    Range("A2").Select
End Sub
 
Postado : 17/08/2016 12:39 pm
 hc3
(@hc3)
Posts: 8
Active Member
Topic starter
 

foi resolvido alterando a função de importar pedidos

Sub importarPed()
'
' importarPed Macro
'

'
    Dim url As String
    url = "C:bancoRNPEDIDOS.CSV"
    
    Sheets("dados_pedidos").Select
    Cells.Select
    Selection.ClearContents
    Range("A1").Select
    With ActiveSheet.QueryTables.Add(Connection:="TEXT;" & url, Destination:=Range("$A$1"))
        .FieldNames = True
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .TextFilePromptOnRefresh = False
        .TextFilePlatform = 850
        .TextFileStartRow = 1
        .TextFileParseType = xlDelimited
        .TextFileTextQualifier = xlTextQualifierDoubleQuote
        .TextFileConsecutiveDelimiter = False
        .TextFileTabDelimiter = True
        .TextFileSemicolonDelimiter = True
        .TextFileCommaDelimiter = False
        .TextFileSpaceDelimiter = False
        .TextFileTrailingMinusNumbers = True
        .Refresh BackgroundQuery:=False
    End With

End Sub
 
Postado : 17/08/2016 1:01 pm
Fernando Fernandes
 Fernando Fernandes
(@fernandofernandes)
Posts: 43750
Illustrious Member
 

Cara, não deu tempo de olhar tudo.

Faz um teste:

Option Explicit

Sub macroAtualizaDadosPedidos()
' macroAtualizaDadosPedidos Macro
' atualiza histórico de pedidos
' realiza a sequencia de passos para atualizar os pedidos

    Call importarPed
    Call formatarPedidos
    Call colocarFiltros
    Call formatarColunas
    Call removePedSemDev
   
End Sub

Sub importarPed()
' importarPed Macro
' faz a importação do arquivo .csv

    Dim url As String
    url = "C:bancoRNPEDIDOS.CSV"
   Sheets("dados_pedidos").Cells.ClearContents
    With ActiveSheet.QueryTables.Add(Connection:="TEXT;" & url, Destination:=Sheets("dados_pedidos").Range("$A$1"))
        .TextFileTextQualifier = xlTextQualifierDoubleQuote
        .Refresh
    End With

End Sub

Sub formatarPedidos()
' formatarPedidos Macro
' deixa o arquivo de pedidos no formato padrão
' remove algumas colunas do arquivo de pedidos

    Range("B:B,F:F,H:J,N:U,X:Z,AB:AB,AD:AD,AK:AL,AP:AS,AX:AZ,BC:BH").Delete Shift:=xlToLeft
End Sub

Sub colocarFiltros()
' colocarFiltros Macro
' coloca filtros no cabeçalho

    Range("A1:AK1").AutoFilter
End Sub

Sub formatarColunas()
' formatarColunas Macro
' formata o tamanho das colunas

    Cells.EntireColumn.AutoFit
End Sub


Sub removePedSemDev()
' removePedSemDev Macro
' remove pedidos sem devolução
' remove os pedidos sem devolução e os pedidos consignados

    Range("A2").Select
    ActiveSheet.Range("$A$1:$AK$50117").AutoFilter Field:=27, Criteria1:=Array( _
        "170", "171", "20", "3", "5", "50", "553", "99"), Operator:=xlFilterValues
    Rows("55:50392").EntireRow.Delete
    ActiveSheet.Range("$A$1:$AK$48897").AutoFilter Field:=27
    Columns("T:T").Select
    ActiveSheet.Range("$A$1:$AK$48897").AutoFilter Field:=20, Criteria1:="0"
    Rows("2:50123").EntireRow.Delete
    ActiveSheet.Range("$A$1:$AK$1699").AutoFilter Field:=20
    Range("A2").Select
End Sub

Qualquer coisa da o grito.
Abraço

Existem mil maneiras de preparar Neston. Invente a sua!
http://www.youtube.com/ExpressoExcel

 
Postado : 17/08/2016 1:17 pm
Forum Jump:
  Tópico Anterior
Próximo Tópico