首页 文章

将CSV数据加载到工作簿中的特定Excel电子表格中

提问于
浏览 次
1

我在将CSV数据加载到当前活动工作簿时遇到问题，但是在单独的工作表中 .

现在我正在将临时数据下载到这个单独的（隐藏）工作表中，并将在其他工作表中引用它 . 电子表格大约4MB，每天更新 .

如何让VBScript将此CSV加载到静态命名的工作表中，该工作表在加载之前将被清除？

URLDownloadToFile 0，fileURL，“％tmp％\ tmpExchDBData.csv”，0,0
Dim dbSheet As Worksheet Dim targetSheet As Worksheet Workbooks.Open文件名：=“％tmp％\ tmpExchDBData.csv”，_格式：= 2'使用逗号分隔符设置dbSheet = ActiveSheet设置targetSheet =工作簿（“Book1”） . 表（3） ）'无论你想将它移动到dbSheet.Move之后：= targetSheet'dbSheet现在都在你的工作簿中 . ' 把它藏起来 . 设置dbSheet = ActiveSheet dbSheet.Visible = xlSheetHidden

excel excel-vba vbscript vba

2 回答

  • 4

    导入CSV，特别是定期导入相同的CSV文件，可以通过将其定义为数据源来完成 . 选择所需的工作表，数据功能区，从文本 .

    一旦定义，您就可以在连接属性中使用非常有用的选项，例如“打开时刷新数据”或“每隔x分钟刷新一次” .

    回复于
  • 1

    我有类似的问题 . 如果它有帮助，这是我最终得到的代码片段：

    Sub Atualizar_B_BR_QSA_IC()

    Application.ScreenUpdating = False  'speed up macro execution
    Application.EnableEvents = False    'turn off other macros for now
    Application.DisplayAlerts = False   'turn off system messages for now

'Atualizar base B_BR_QSA_IC
    Dim base_1 As Workbook
    Dim plan_1 As Worksheet
    Dim nome_arquivo_1 As String
    Dim destino_1 As Worksheet

    nome_arquivo_1 = Application.ActiveWorkbook.Path & "\BR_QSA_Report_CC.csv"
    Set destino_1 = ThisWorkbook.Worksheets("B_BR_QSA_IC")
    Set base_1 = Workbooks.Open(Filename:=nome_arquivo_1, Local:=True)
    Set plan_1 = base_1.Worksheets(1)

'Limpar a última linha, que é uma linha de totais e não queremos usar
    plan_1.Cells(Rows.Count, "A").End(xlUp).EntireRow.Delete

'Caso o excel já não entenda corretamente na abertura, vamos fazer Texto para Colunas do csv
    plan_1.Range("A1:A" & plan_1.Cells(Rows.Count, "A").End(xlUp).Row).TextToColumns _
        Destination:=Range("A1"), DataType:=xlDelimited, Comma:=True, FieldInfo:=Array(1, 4)

'Agora vamos copiar a base para o arquivo Master (após limpá-lo) e fechar a base sem salvar
    destino_1.Range("B2:T" & destino_1.Cells(Rows.Count, "B").End(xlUp).Row).Clear
    plan_1.Range("A3:S" & plan_1.Cells(Rows.Count, "A").End(xlUp).Row).Copy
    destino_1.Range("B2").PasteSpecial xlPasteValuesAndNumberFormats
    base_1.Close savechanges:=False

'Se não há fórmula nas colunas A e B após plugar a base, precisamos colocar até o tamanho da base
    If IsEmpty(destino_1.Range("A" & destino_1.Cells(Rows.Count, "B").End(xlUp).Row)) Then
    destino_1.Range("A2").Copy Destination:=destino_1.Range("A" & (destino_1.Cells(Rows.Count, "A").End(xlUp).Row + 1) _
        & ":" & "A" & destino_1.Cells(Rows.Count, "B").End(xlUp).Row)

'Se já há, precisamos limpar até o tamanho da base
    ElseIf Not IsEmpty(destino_1.Range("A" & (destino_1.Cells(Rows.Count, "B").End(xlUp).Row + 1))) Then
    destino_1.Rows((destino_1.Cells(Rows.Count, "B").End(xlUp).Row + 1) & ":" & destino_1.Cells(Rows.Count, "A") _
        .End(xlUp).Row).EntireRow.Delete
    End If

'Maquiagem
    destino_1.Cells.Font.Name = "Calibri"
    destino_1.Cells.Font.Size = 8
    destino_1.Rows.RowHeight = 11.25

    Application.DisplayAlerts = True    'turn system alerts back on
    Application.EnableEvents = True     'turn other macros back on
    Application.ScreenUpdating = True   'refreshes the screen

End Sub
    回复于

相关问题