Load CSV data into a specific excel spreadsheet in a workbook

Question

I'm having issues loading CSV data into the current active workbook, but in a separate sheet.

Right now I'm downloading temporary data into this separate (hidden) sheet, and will refer to it in other sheets. The spreadsheet is approx 4MB, and is updated daily.

How can I get VBScript to load this CSV into a staticly named sheet that would be cleared prior to loading?

 URLDownloadToFile 0, fileURL, "%tmp%\tmpExchDBData.csv", 0, 0

Dim dbSheet As Worksheet Dim targetSheet As Worksheet

Workbooks.Open Filename:="%tmp%\tmpExchDBData.csv", _ Format:=2 ' use comma delimiters Set dbSheet = ActiveSheet

Set targetSheet = Workbooks("Book1").Sheets(3) ' wherever you want to move it to

dbSheet.Move After:=targetSheet ' dbSheet is now in your workbook.

' Hide it. Set dbSheet = ActiveSheet dbSheet.Visible = xlSheetHidden

Answer 1

Importing CSV, especially regularly importing the same CSV file, can be done by defining it as a data source. Select the sheet you want it in, Data Ribbon, From Text.

Once defined you have spectacularly useful options in the connection properties such as "Refresh data when opening" or "Refresh every x minutes".

Answer 2

I've had a similar issue. If it helps, here's the piece of code I ended up with:

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