Excel VBA or Function to extract Workbook name and data from workbook.

Question

Is there any way to extract the workbook name, but then extract only a part of it. Any version of excel would be fine preferably 2003.

For example

"Help_TicketID123456788.xls"  
"Help_TicketID563565464.xls" 

...

So then I'd like to extract the ID numbers and put them into a column on a master worksheet in another workbook.

Additionally I'd like to extract some data from specific columns (Always the same columns) from each workbook, and put that into the master worksheet too.

Thank you!!

Answer 1

In your master spreadsheet you can write a VBA procedure to loop over all the xls files in a directory, extract the ID Number from each filename, and then open each file to extract the other data. This should get you started:

Sub RunCodeOnAllXLSFiles()
Dim lCount As Long
Dim wbResults As Workbook
Dim wbCodeBook As Workbook

Application.ScreenUpdating = False
Application.DisplayAlerts = False
Application.EnableEvents = False

On Error Resume Next
Set wbCodeBook = ThisWorkbook
    With Application.FileSearch
        .NewSearch
        'Change path to suit
        .LookIn = "C:\MyDocuments\TestResults"
        .FileType = msoFileTypeExcelWorkbooks
        'Optional filter with wildcard
        .Filename = "Help_TicketID*.xls"
            If .Execute > 0 Then 'Workbooks in folder
                For lCount = 1 To .FoundFiles.Count 'Loop through all
                    'Extract ticket #
                    '.FoundFiles(lCount) is the filename

                    'Open Workbook x and Set a Workbook variable to it
                    Set wbResults = Workbooks.Open(Filename:=.FoundFiles(lCount), UpdateLinks:=0)

                    'Read the data from wbResults and write to your master spreadsheet

                    wbResults.Close SaveChanges:=False
                Next lCount
            End If
    End With
On Error GoTo 0
Application.ScreenUpdating = True
Application.DisplayAlerts = True
Application.EnableEvents = True
End Sub

Source: http://www.ozgrid.com/VBA/loop-through.htm