Sheets.("SheetName*") Sheets Wildcard?

[LocarAce]

Hi,

I have the below that import files from a folder that works well.

The wildcard works well for filenames:

    Dim strPath As String, xStrPath As String, xStrName As String, xStrFName As String
    Dim xWS As Worksheet, xTWB As Workbook, DestSheet As Worksheet, FileName As String
    Dim xStrAWBName As String, Sh1 As Worksheet, FolderName As String, sItem As String
    Dim FolderPath As String, fldr As FileDialog, Lr As Long, Lc As Long, Lr2 As Long
    
    On Error Resume Next
    
    Set xTWB = ThisWorkbook
    Set DestSheet = xTWB.ActiveSheet
    Debug.Print DestSheet.Name
    
    Set fldr = Application.FileDialog(msoFileDialogFolderPicker)
        With fldr
            .Title = "Select a Folder"
            .AllowMultiSelect = False
            .InitialFileName = Application.DefaultFilePath
            If .Show <> -1 Then GoTo NextCode
            sItem = .SelectedItems(1)
        End With
    
NextCode:
        FolderName = sItem
        Set fldr = Nothing
        FolderPath = FolderName & "\"
   
    FileName = Dir(FolderPath & "*FILENAME*")

but the worksheet does not:

Set Sh1 = ActiveWorkbook.Sheets("SHEET*")
    xStrName = Sh1.Name

Hope if you can assist?

Full Code Below:

Sub ImportFiles()

    Dim strPath As String, xStrPath As String, xStrName As String, xStrFName As String
    Dim xWS As Worksheet, xTWB As Workbook, DestSheet As Worksheet, FileName As String
    Dim xStrAWBName As String, Sh1 As Worksheet, FolderName As String, sItem As String
    Dim FolderPath As String, fldr As FileDialog, Lr As Long, Lc As Long, Lr2 As Long
    
    On Error Resume Next
    
    Set xTWB = ThisWorkbook
    Set DestSheet = xTWB.ActiveSheet
    Debug.Print DestSheet.Name
    
    Set fldr = Application.FileDialog(msoFileDialogFolderPicker)
        With fldr
            .Title = "Select a Folder"
            .AllowMultiSelect = False
            .InitialFileName = Application.DefaultFilePath
            If .Show <> -1 Then GoTo NextCode
            sItem = .SelectedItems(1)
        End With
    
NextCode:
        FolderName = sItem
        Set fldr = Nothing
        FolderPath = FolderName & "\"
   
    FileName = Dir(FolderPath & "*FILENAME*")

    Application.ScreenUpdating = False
    Application.Calculation = xlCalculationManual
    Application.DisplayAlerts = False

    Do While FileName <> ""
    Workbooks.Open FileName:=FolderPath & FileName, ReadOnly:=True
    xStrAWBName = ActiveWorkbook.Name

    Set Sh1 = ActiveWorkbook.Sheets("SHEET*")
    xStrName = Sh1.Name

    For Each xWS In ActiveWorkbook.Sheets

    If xWS.Name = xStrName Then
        Lr = DestSheet.Range("B" & Rows.Count).End(xlUp).Row
        Lr2 = xWS.Range("B" & Rows.Count).End(xlUp).Row
        Lc = Cells(1, Columns.Count).End(xlToLeft).Column
        If Lr = 1 Then
            'DestSheet.Range("A1") = FileName   'Only copies filename to the first row of the data
            Range(xWS.Cells(1, 1), xWS.Cells(Lr2, Lc)).Copy DestSheet.Range("B1")
        Else
            'DestSheet.Range("A" & Lr + 1) = FileName   'Only copies filename to the first row of the data
            Range(xWS.Cells(2, 1), xWS.Cells(Lr2, Lc)).Copy DestSheet.Range("B" & Lr + 1)
        End If
        With DestSheet
            .Range(.Cells(Lr + 1, "A"), .Cells(Rows.Count, "B").End(xlUp).Offset(0, -1)) = FileName
        End With
    End If
    
    Next xWS
    Workbooks(xStrAWBName).Close
    FileName = Dir()
    Loop
    
    'xTWB.Save 'Save the File

'End script import
    Application.ScreenUpdating = True
    Application.DisplayAlerts = True
    Application.Calculation = xlCalculationAutomatic
    
    Range("A1") = "Source File"
        
End Sub

 

[rlv01]

Set Sh1 = ActiveWorkbook.Sheets("SHEET*")

Wildcards like that are not legal syntax and cannot be made legal.

Perhaps if you explained what you are trying to do with xStrName, we can see if there is another way to accomplish it.

Set Sh1 = ActiveWorkbook.Sheets("SHEET*")
    xStrName = Sh1.Name

 

[LocarAce]

Set Sh1 = ActiveWorkbook.Sheets("SHEET*")

Wildcards like that are not legal syntax and cannot be made legal.

Perhaps if you explained what you are trying to do with xStrName, we can see if there is another way to accomplish it.

Set Sh1 = ActiveWorkbook.Sheets("SHEET*")
    xStrName = Sh1.Name

Ultimately, the entire code purpose is to extract data from the first sheet of all Excel files in a folder that matches the FILENAME.

Perhaps we can alter Sh1 into the first sheet instead of declaring the worksheet's actual name?

Hope you can help.

 

[LocarAce]

Figuredout the answer to my question and just replaced

Sh1 = ActiveWorkbook.Sheets("SHEET*")

with

Sh1 = ActiveWorkbook.Sheets(1)

to refer to the first sheet always on the left. Thanks.