You can use VBA to combine workbooks and add the workbook name in first column and sheet name as the second column. Here’s a basic script:

1. Open Excel and press Alt + F11 to open the VBA editor.
2. Insert a new module by right-clicking on any existing module and selecting Insert > Module.
3. Paste the following VBA code:

Sub CombineWorkbooksWithNames()
    Dim FolderPath As String
    Dim FileName As String
    Dim WS As Worksheet
    Dim TargetSheet As Worksheet
    Dim LastRow As Long
    Dim SourceWB As Workbook
    Dim i As Long

    ' Set the path to the folder containing the workbooks
    FolderPath = "C:\Your\Folder\Path\"

    ' Create a new sheet for combined data
    Set TargetSheet = ThisWorkbook.Sheets.Add
    TargetSheet.Name = "CombinedData"
    TargetSheet.Cells(1, 1).Value = "WorkbookName"
    TargetSheet.Cells(1, 2).Value = "SheetName"

    ' Start looping through all Excel files in the folder
    FileName = Dir(FolderPath & "*.xls*")
    Do While FileName <> ""
        Set SourceWB = Workbooks.Open(FolderPath & FileName)
        
        ' Loop through all sheets in the workbook
        For Each WS In SourceWB.Sheets
            LastRow = TargetSheet.Cells(Rows.Count, 1).End(xlUp).Row + 1
            
            ' Copy data from the source sheet to the target sheet
            WS.UsedRange.Copy Destination:=TargetSheet.Cells(LastRow, 3)
            
            ' Add workbook name in the first column
            TargetSheet.Cells(LastRow, 1).Resize(WS.UsedRange.Rows.Count, 1).Value = SourceWB.Name
            
            ' Add sheet name in the second column
            TargetSheet.Cells(LastRow, 2).Resize(WS.UsedRange.Rows.Count, 1).Value = WS.Name
        Next WS
        
        SourceWB.Close False
        FileName = Dir
    Loop
End Sub

4. Modify the FolderPath to match the location where your workbooks are stored.

5. Run the macro by pressing F5.