Extracting & Manipulating Data from CSV

[MJaspering]

Hey All,

I am still pretty early in learning VBA and am wanting to build a reporting tool for my teams that help them to better understand their salary-to-revenue percentages. The general outline of what I am trying to build is as follows:

1. A table is exported to CSV from Workday.
2. A teammate can then click on an ActiveX button that opens the File Explorer dialogue and allows them to select the CSV export.
3. Once selected, the VBA sub would sum the numerical values from two specific columns in the CSV.
4. These sum values would then be directed to specific cells on the worksheet.

I found the below that gets step 2 done but does not offer functionality beyond that.

Any help is greatly appreciated - thank you!

Private Sub CommandButton1_Click()
Dim z As FileDialog
Dim zPath As String
Dim csv As String
Dim wsheet As String
Application.DisplayAlerts = False
    Application.StatusBar = True
    wsheet = ActiveWorkbook.Name
    Set z = Application.FileDialog(msoFileDialogFilePicker)
    z.Title = "File Selection:"
    If z.Show = -1 Then
        zPath = z.SelectedItems(1)
    Else
    Exit Sub
    End If
        If Right(zPath, 1) <> "\" Then Path = Path + "\"
        csv = Dir(Path & "*.csv")
    Do While csv <> ""
        Application.StatusBar = "Converting: " & csv
        Workbooks.Open Filename:=Path & csv
        ActiveWorkbook.SaveAs Replace(Path & csv, ".csv", ".xlsx", vbTextCompare), _
        xlWorkbookDefault
        ActiveWorkbook.Close
        Windows(wsheet).Activate
        csv = Dir
    Loop
        Application.StatusBar = False
        Application.DisplayAlerts = True
End Sub

 

[rlv01]

I found the below that gets step 2 done but does not offer functionality beyond that.

I disagree. I found that code to be broken. Here is something that will get you as far as selecting and opening the CSV file in excel. Not enough info about steps 3 & 4 to comment.

Private Sub CommandButton1_Click()
    Dim z As FileDialog
    Dim zPath As String
    Dim NewWB As Workbook
    
    Set z = Application.FileDialog(msoFileDialogFilePicker)
    z.Filters.Clear
    z.Filters.Add "CSV Files", "*.csv", 1
    z.Title = "File Selection:"
    z.AllowMultiSelect = False

    If z.Show = -1 Then
        zPath = z.SelectedItems(1)
    Else
        Exit Sub
    End If

    Set NewWB = Workbooks.Open(Filename:=zPath)
    ActiveSheet.UsedRange.Columns.AutoFit
    
    '-- Begin code for steps 3 & 4
    
    '(Your code here)
    
    '-- end code for steps 3  & 4
    
End Sub

 

[MJaspering]

This is fantastic, thank you!

As far as step three, it seems like I may potentially want to use a WorksheetDataConnection object to extract desired column ranges (i.e., D:D and F:F) into another form that I could sum and assign to variable?

 

[RoryA]

I would suggest power query. You can use a cell value as the file path parameter and set everything else up in the query. The all your code needs to do is update the file path in the cell and refresh the query.

 

[rlv01]

This is fantastic, thank you!

As far as step three, it seems like I may potentially want to use a WorksheetDataConnection object to extract desired column ranges (i.e., D:D and F:F) into another form that I could sum and assign to variable?

I'm not sure that the WorksheetDataConnection object confers any advantage over just using VBA to work with the range(s) directly to do what you want (sum, count, etc). But it is certainly another way to go if you spend the time to dive in and learn about it. And as @RoryA suggested, Power Query can also extract and manipulate.