Importing dynamic named worksheets Using a condition

[Gautham A]

I have a situation where a data source (xlsx) file has a changing sheet name.

The excel file has multiple sheets like Region,Location,ABC 1.1,ABC 2.2, ABC 2.1

I have a requirement where I need to get a single sheet based on a condition.

The condition are

1. It should contain 'ABC'

2. Now there are three sheets with name ABC. It should pick only ABC 2.2 because 2.2 is greater than 2.1 and 1.1.



So next week if the data gets refreshed and if I find a sheet ABC 3.1, it should pick that sheet and load the data in that sheet.

How to write a M code for this condition. Can someone please help?

 

[bebo021999]

Below code is sample to : select the max-sheet, or paste value to max-sheet.
From this, you can do every thing with that sheet.

Option Explicit
Sub test()
Dim i&, ws As Worksheet, id As Double, max As Double, sht As String
For i = 1 To Worksheets.Count
    If Worksheets(i).Name Like "ABC*" Then
        id = CDbl(Mid(Worksheets(i).Name, 4, 255))
        If id > max Then
            max = id
            sht = Worksheets(i).Name
        End If
    End If
Next
Worksheets(sht).Activate ' to select the max-sheet
Worksheets(sht).Range("A1").Value = 100 ' to paste value into the max-sheet
End Sub

 

[Gautham A]

Hello,
thanks for the response. But I'm working this requirement in power bi. So I require a M code for this

 

[JGordon11]

Change C:\path and filename as appropriate and try

let
    Source = Excel.Workbook(File.Contents("C:\path\filename.xlsx")),
    SheetName = List.Last(List.Sort(List.Select(Source[Name], each Text.Start(_,3)="ABC"))),
    Result = Source{[Name = SheetName]}[Data]
in
    Result