Excel automation to copy and paste in to a Microsoft Teams channel

Question

I need Excel to be able to copy cell data and paste it in to a Microsoft Teams channel as an automation or flow.

When I enter in the phrase "In Progress" in to cell J1then i need the cell data in G1 and i1 to copy and paste in to a specific Microsoft Teams channel. Is this possible? Thanks in advance! :)

Answer 1

Thanks so much Cliff. :)

I still need this to trigger the event to Copy the data in the corresponding row in column G and I and paste it in a specific Teams Chat.

Need to also figure out how to run the VB script in power automate flow so it can also be ran in the web version of Excel, not just the desktop version.

I made a few adjustments to the VB code:

When a name is selected from the drop down in column L,

the time is automatically entered in to the the adjacent cell in column K and the time is adjusted depending on the name,

Then "In Progress" is automatically entered in to the adjacent cell in column J ,

        and the old cell with "In Progress" will automatically change to "Complete."

Here is the VB code I am running:

Private Sub Worksheet_Change(ByVal Target As Range)

On Error Resume Next 

Application.EnableEvents = False 

Dim startTime As Double 

startTime = Timer ' Record the start time in seconds 

Dim rng As Range 

Set rng = Intersect(Target, Me.Columns("L")) 

If Not rng Is Nothing Then 

    Dim cell As Range 

    For Each cell In rng 

        Dim currentTime As Date 

        currentTime = Now 

        ' Check elapsed time and exit loop if it's been running for more than 1 second 

        If Timer - startTime > 1 Then 

            Exit For 

        End If 

        Select Case cell.Value 

            Case "Romeo" 

                cell.Offset(0, -1).Value = currentTime + TimeValue("03:00:00") ' Offset to K column 

                cell.Offset(0, -2).Value = "In Progress" ' Offset to J column 

            Case "Shawn", "Justin" 

                cell.Offset(0, -1).Value = currentTime ' Offset to K column 

                cell.Offset(0, -2).Value = "In Progress" ' Offset to J column 

            Case "Davian", "Derek" 

                cell.Offset(0, -1).Value = currentTime + TimeValue("01:00:00") ' Offset to K column 

                cell.Offset(0, -2).Value = "In Progress" ' Offset to J column 

            Case "Jimmy", "Bobby", "Toni" 

                cell.Offset(0, -1).Value = currentTime + TimeValue("02:00:00") ' Offset to K column 

                cell.Offset(0, -2).Value = "In Progress" ' Offset to J column 

            Case Else 

                ' Do not clear contents of J and K columns if the criteria are not met 

        End Select 

        ' Update all other cells in column J with "In Progress" to "Complete" (within rows 1 to 100) 

        If cell.Offset(0, -2).Value = "In Progress" Then 

            Dim checkRange As Range 

            Set checkRange = Me.Range("J1:J100") 

            Dim inProgressAddresses() As String 

            Dim inProgressCount As Long 

            inProgressCount = 0 

            Dim checkCell As Range 

            For Each checkCell In checkRange 

                If checkCell.Address <> cell.Offset(0, -2).Address And checkCell.Value = "In Progress" Then 

                    inProgressCount = inProgressCount + 1 

                    ReDim Preserve inProgressAddresses(1 To inProgressCount) 

                    inProgressAddresses(inProgressCount) = checkCell.Address 

                End If 

            Next checkCell 

            ' Update all found "In Progress" cells to "Complete" 

            For i = 1 To inProgressCount 

                Me.Range(inProgressAddresses(i)).Value = "Complete" 

            Next i 

        End If 

    Next cell 

End If 

Application.EnableEvents = True 

End Sub

Answer 2

Dear RomeoV2024,

Thanks for your updates and kindness.

To connect to Teams, you need to use the cloud flow. However, the macro to get the local time zone in the users' environment don't work with the cloud flow.

Currently the VBA code can only be ran with the Power Automate desktop client.

Run macros on an Excel workbook - Power Automate | Microsoft Learn

You can run the desktop flow within a cloud flow, but it needs extra premium license which may need additional money.

Trigger desktop flows from cloud flows - Power Automate | Microsoft Learn

Based on my test, it is difficult for Office Script to get the local time zone as well.

That's the dilemma in your environment.

So as a possible workaround, you can let the VBA to be ran in the .xlsm file by users to populate the time zone value first. After populating all data, execute another VBA code or Office Script to a.xlsx file automatically and then let the user select one row to fill in In Progress and run the cloud flow to transfer the data to Teams.

The VBA code can be the following.

Workbooks("***.xlsm").Worksheets("***").Range("***").Copy _

Workbooks("\*\*\*.xlsx").Worksheets("\*\*\*").Range("\*\*\*")

I understand that it isn't an ideal solution. Sorry for the inconvenience.

Thanks for your effort and understanding.

Sincerely

Cliff | Microsoft Community Moderator

Answer 3

Dear RomeoV2024,

Thanks for your updates.

The Get file metadata action is an action not a trigger. It is used to get the file metadata such as file identifier, file name, file path and so on in SharePoint Online. It can't trigger the flow on the specific rows.

In your first screenshot, the trigger is Manually trigger a flow which for you to type a text, select a file or send an email and so on manually and then run the next actions in the flow.

.

The correct trigger used in your flow is For a selected row. The Run Script action is for Office Script not VBA.

Another workaround I can think may be to use the Power Automate desktop to run the macro in Excel and pop up a window for users to type the start column and row to find the row cells of the Excel file, copy the data to a newly file to the local OneDrive sync client and then trigger a cloud flow to get the data from the newly created file and then pass the data to Teams.

To get the data by the row and column in the Power Automate desktop, you can start like the following.

.

Run macros on an Excel workbook - Power Automate | Microsoft Learn

Thanks for your effort and understanding.

Sincerely

Cliff | Microsoft Community Moderator

Answer 4

Thanks Cliff. I really appreciate you.

I completely understand. I expected this process to be a tricky one.

At this point I feel it would be best to just run the VBA script in Excel Desktop.

Can we still create the trigger event to automatically copy the data in the corresponding cells and paste them in to a Teams channel?

When a cell in column J gets the "In Progress" entered in to it, then automatically copy the data from cells G and I on that row and automatically paste it in to a specific Teams channel.

I'm using SharePoint/ O365 for Business so I think I should have the correct credentials to accomplish it.

I found these images online about the "Get file metadata" to see the .xlsm file.

Can that work? If it can will you be able to give me instructions on how to set it up please!? :)

Different version of the flow??