Converting Excel Macro-Fields Worksheets into PowerBI Visualisat

Hello Jens,

Thank you for your last message.

Would you be able to explain the code (pasted below), which indicates a Macro function for "Refreshing List" with items in my Excel file. And would be possible to convert this code via DAX? So in theory, I'm thinking about creating the button inside PowerBI which would connect with the server via Macros and kick off the outcome in my table. Is this is something that it can be done?

There is also an easier solution, to give an "Action" to the button and link the button via external URL, which would connect with my Excel file via sharepoint.


But please let me know if the first solution (translating VBA code into DAX), can be also an option?

Here is the code:

Sub Update_QuoteStatus()

Application.ScreenUpdating = False

Dim lastrow As Long
Dim Item As Range
Dim cs As Variant
Dim i As Long

If Sheet6.Range("AI5").Value > 0 Then GoTo ErrorHandler1

lastrow = Sheet6.Cells(Rows.Count, 28).End(xlUp).Row

For i = 8 To lastrow
cs = Sheet6.Cells(i, 28).Value
With Sheet2
Set Item = .Cells.Find(What:=cs, After:=.Cells(1, 1), _
LookIn:=xlValues, lookat:=xlPart, SearchOrder:=xlByRows, _
SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False)
End With

If Sheet6.Cells(i, 57) = 1 Then
Sheet2.Range(Item.Address).Offset(0, 1) = Sheet6.Cells(i, 8)
End If

If Sheet6.Cells(i, 67) = 1 Then
Sheet2.Range(Item.Address).Offset(0, 22) = Sheet6.Cells(i, 18)
End If

If Sheet6.Cells(i, 68) = 1 Then
Sheet2.Range(Item.Address).Offset(0, 13) = Sheet6.Cells(i, 19)
End If

If Sheet6.Cells(i, 71) = 1 Then
Sheet2.Range(Item.Address).Offset(0, 16) = Sheet6.Cells(i, 22)
End If

If Sheet6.Cells(i, 72) = 1 Then
Sheet2.Range(Item.Address).Offset(0, 17) = Sheet6.Cells(i, 23)
End If

If Sheet6.Cells(i, 74) = 1 Then
Sheet2.Range(Item.Address).Offset(0, 2) = Sheet6.Cells(i, 25)
End If

If Sheet6.Cells(i, 75) = 1 Then
Sheet2.Range(Item.Address).Offset(0, 43) = Sheet6.Cells(i, 26)
End If

Next i

Application.ScreenUpdating = True
Sheet6.Range("H7").Select

MsgBox ("Quote Data Updated")

Call Refresh_QuoteList

Exit Sub

ErrorHandler1:

Application.CutCopyMode = False
Application.ScreenUpdating = True
Sheet6.Range("H7").Select

MsgBox Prompt:="DATA REQUIRED" & vbNewLine & "Please update cells highlighted in yellow"


End Sub

Sub Refresh_QuoteList()

Application.ScreenUpdating = False

Dim pt1 As PivotTable
Dim Field1 As PivotField
Dim NewCat1 As String

Set pt1 = Sheet6.PivotTables("PivotTable1")
Set Field1 = pt1.PivotFields("Category1")
NewCat1 = Sheet6.Range("E20").Value

On Error GoTo ErrorHandler2

With pt1
Field1.ClearAllFilters
Field1.CurrentPage = NewCat1
pt1.RefreshTable
End With

Sheet6.Range("AK8:BC506").Copy
Sheet6.Range("H8:Z506").PasteSpecial Paste:=xlPasteValues

Application.CutCopyMode = False
Application.ScreenUpdating = True
Sheet6.Range("H7").Select

Exit Sub

ErrorHandler2:

With pt1
Field1.ClearAllFilters
Field1.CurrentPage = "(blank)"
pt1.RefreshTable
End With

Sheet6.Range("AK8:BC506").Copy
Sheet6.Range("H8:Z506").PasteSpecial Paste:=xlPasteValues

Application.CutCopyMode = False
Application.ScreenUpdating = True
Sheet6.Range("H7").Select

MsgBox Prompt:=("No '" & Sheet6.Range("E20").Value & "' items to list")

End Sub

Sub QuoteData_Revert()

Application.ScreenUpdating = False

Sheet6.Range("AK8:BC506").Copy
Sheet6.Range("H8:Z506").PasteSpecial Paste:=xlPasteValues

Application.CutCopyMode = False
Application.ScreenUpdating = True
Sheet6.Range("H7").Select

End Sub




Kind Regards,

Piotr Jedrzejczyk