VBA Help

[SamBo1234]

Hi All i have this code, which allows me to import multiple Workbooks into the same one with a click of a button.

The problem i have got is that i need to include somewhere in the code, a way to add the name of the file, once inported to the TAB, i.e the Footer. Im not an expert in VBA, does anyone have any tips to do this ?

Many Thanks
Sam

Sub CombineWorkbooks()
    Dim FilesToOpen
    Dim x As Integer

    On Error GoTo ErrHandler
    Application.ScreenUpdating = False

    FilesToOpen = Application.GetOpenFilename _
      (FileFilter:="Microsoft Excel Files (*.xls), *.xls", _
      MultiSelect:=True, Title:="Files to Merge")

    If TypeName(FilesToOpen) = "Boolean" Then
        MsgBox "No Files were selected"
        GoTo ExitHandler
    End If

    x = 1
    While x <= UBound(FilesToOpen)
        Workbooks.Open Filename:=FilesToOpen(x)
        Sheets().Move After:=ThisWorkbook.Sheets _
          (ThisWorkbook.Sheets.Count)
                 
        x = x + 1
    Wend

ExitHandler:
    Application.ScreenUpdating = True
    Exit Sub

ErrHandler:
    MsgBox Err.Description
    Resume ExitHandler
End Sub

 

[SamBo1234]

Hi Eric,

I tryed the following: (below)

I tryed it with the following file names as a test,

missing_timesheetsCORE100 12 September 2006 13-36-24
missing_timesheetsCORE200 12 September 2006 13-36-24
missing_timesheetsCORE300 12 September 2006 13-36-24

And i got the following message... (i suppose this is because of the spaces / characters etc in the file name).....

Make sure the name you entered does not exceed 31 characters
Make sure the name does not contain any of the following chracters : \ / ? * [ or ]
Make sure you did not leave a blank.

FYI they all have one one sheet.

If i can use this...

For i = ShCnt + 1 To .Sheets.Count 
            .Sheets(i).Range("A1") = FilesToOpen(x) 
            Next i

But change it so that for example it displays only the FileName of the Workbook...

So rather than showing C:\Windows\Documentandsettings\TestOne.XLS
It would just show TestOne.XLS

In Cell A1... then that would solve my problem, which i could make do with.

Option Explicit

Sub CombineWorkbooks()
    Dim FilesToOpen
    Dim x As Integer
    Dim ShCnt As Integer
    Dim i As Integer
    Dim file_fullname As String
    
    On Error GoTo ErrHandler
    Application.ScreenUpdating = False

    FilesToOpen = Application.GetOpenFilename _
      (FileFilter:="Microsoft Excel Files (*.xls), *.xls", _
      MultiSelect:=True, Title:="Files to Merge")

    If TypeName(FilesToOpen) = "Boolean" Then
        MsgBox "No Files were selected"
        GoTo ExitHandler
    End If

    x = 1
    While x <= UBound(FilesToOpen)
        Workbooks.Open Filename:=FilesToOpen(x)
        
        With ThisWorkbook
        ShCnt = .Sheets.Count
        Sheets().Move After:=.Sheets(ShCnt)
            For i = ShCnt + 1 To .Sheets.Count
            file_fullname = FilesToOpen(x)
            .Sheets(i).Name = extract_filename(file_fullname) & " " & .Sheets(i).Name
            Next i
        End With
        
        x = x + 1
    Wend

ExitHandler:
    Application.ScreenUpdating = True
    Exit Sub

ErrHandler:
    MsgBox Err.Description
    Resume ExitHandler
End Sub

Function extract_filename(FN As String) As String
'returns filename if fullpath is entered
'Erik Van Geit
'060921
Dim i As Integer

i = InStrRev(FN, Application.PathSeparator)

    ''elder excelversions
    'For i = Len(FN) To 1 Step -1
    'If Mid(FN, i, 1) = Application.PathSeparator Then Exit For
    'Next i
    
extract_filename = Mid(FN, i + 1, Len(FN) - i)

'without extension: enable next part
i = InStrRev(extract_filename, ".")

    ''elder excelversions
    'For i = Len(extract_filename) To 1 Step -1
    'If Mid(extract_filename, i, 1) = "." Then Exit For
    'Next i

extract_filename = Left(extract_filename, i - 1)
End Function

[/quote]

 

[Norie]

Sam

If you just want the filename use Dir.

For i = ShCnt + 1 To .Sheets.Count
      .Sheets(i).Range("A1") = Dir(FilesToOpen(x))
Next i

 

[SamBo1234]

That works fine,

In Cell A1 it now displays the Filename only without all the path attached... which is fine and does the job.,

Many Thanks everyone, im not very good at explaining... and tend to garble on too much lol.

But many thanks, I appreciate you taking time to look into this for me...

Best Wishes.
Sam