Extracting data from another workbook; Sheet name might change case.

[yxz152830]

Gurus,
below code sometime cannot extract data from the workbook because of sheet name letter case change eg from flt to FLT. Is there a way for me to set up different conditions so that I can at least make the extracting process more automatic?

ActiveWorkbook.queries.Add Name:=queryname, Formula:= _
        "let Source = Excel.Workbook(File.Contents(""" & getpath_vol & """), null, true), FLT_Sheet = Source{[Item=""FLT"",Kind=""Sheet""]}[Data]" & "in" & " FLT_Sheet"

 

[Strithken]

Hi there. You could loop through the sheets to get the correct case of the sheet name.

Private Function GetSheetNameThatMatchesStringCaseInsensitive(ByVal str As String) As String
    Dim sheet As Variant
    For Each sheet In ThisWorkbook.Worksheets
        If UCase(sheet.Name) = UCase(str) Then
            GetSheetNameThatMatchesStringCaseInsensitive = sheet.Name
            Exit Function
        End If
    Next sheet
End Function