VBA: Getting the document name when using workbook.open

[pheeelicxz]

Hi,

I am trying to import data from multiple workbooks onto this master workbook and then have the imported document name entered onto a list starting vertically from C9.
I have the code to import the files but have not figured out how to get the name of the doc onto the list from C9.
This is what i have got at the moment:

Sub ImportDatafromotherworksheet()
'Activate "Global Spend" worksheet
Worksheets("Global Spend").Activate
 
    Dim wkbCrntWorkBook As Workbook
    Dim wkbSourceBook As Workbook
    Dim rngSourceRange As Range
    Dim rngDestination As Range
    Dim filename As String
    Dim xCell As Range
    Dim lastRow As Long
    Set wkbCrntWorkBook = ActiveWorkbook
    lastRow = Range("A" & Rows.Count).End(xlUp).Row
        With Application.FileDialog(msoFileDialogOpen)
        .Filters.Clear
        .Filters.Add "Excel 2007-13", "*.xlsx; *.xlsm; *.xlsa"
        .AllowMultiSelect = False
        .Show
        If .SelectedItems.Count > 0 Then
            Workbooks.Open .SelectedItems(1)
            Set wkbSourceBook = ActiveWorkbook
            Set rngSourceRange = Application.InputBox(prompt:="Select source range", Title:="Source Range", Default:="A2", Type:=8)
            wkbCrntWorkBook.Activate
            Set rngDestination = Application.InputBox(prompt:="Select destination cell", Title:="Select Destination", Default:="A" & lastRow + 1, Type:=8)
            rngSourceRange.Copy rngDestination
            rngDestination.CurrentRegion.EntireColumn.AutoFit
            wkbSourceBook.Close False
        End If
    End With
   
'Input name of file in list "C9". Header at C9, next entry is one row down.
???
   
 
 
'Go back to "Start" worksheet
Worksheets("Start").Activate
'Confirmation messagebox
MsgBox ("You have successfully imported the file you have choosen. Please check content in 'Global Spend' worksheet.")
   
End Sub

Any help is appreciated.
Many thanks!

 

[NdNoviceHlp]

Set wkbSourceBook = ActiveWorkbook
 temp= wkbSourceBook.Name
'then later...
ThisWorkbook.Sheets("Your sheetName").Cells(9,"C") = temp

HTH. Dave

 

[pheeelicxz]

Set wkbSourceBook = ActiveWorkbook
 temp= wkbSourceBook.Name
'then later...
ThisWorkbook.Sheets("Your sheetName").Cells(9,"C") = temp

HTH. Dave

Thanks Dave for replying. However the code would not generate a new line for every imported file.
I have done the following code, which does the job, but i wonder if it can be improved? As im dealing with a large dataset, I would rather optimise it then doing it the long winded way. Any thoughts?
p.s. i should probably use your method to generate a the file name rather than the entire dir path...

Dim wkbCrntWorkBook As Workbook
    Dim wkbSourceBook As Workbook
    Dim rngSourceRange As Range
    Dim rngDestination As Range
    Dim filename As String
    Dim xCell As Range
    Dim lastRow As Long
    Dim SelectedFileItem As String
    Set wkbCrntWorkBook = ActiveWorkbook
    lastRow = Range("A" & Rows.Count).End(xlUp).Row
        With Application.FileDialog(msoFileDialogOpen)
        .Filters.Clear
        .Filters.Add "Excel 2007-13", "*.xlsx; *.xlsm; *.xlsa"
        .AllowMultiSelect = False  
        If .Show = -1 Then
        'if user clicks OK
        SelectedFileItem = .SelectedItems(1)
        'Open the selected file
        Workbooks.Open (SelectedFileItem)
            Set wkbSourceBook = ActiveWorkbook
            Set rngSourceRange = Application.InputBox(prompt:="Select source range", Title:="Source Range", Default:="A2", Type:=8)
            wkbCrntWorkBook.Activate
            Set rngDestination = Application.InputBox(prompt:="Select destination cell", Title:="Select Destination", Default:="A" & lastRow + 1, Type:=8)
            rngSourceRange.Copy rngDestination
            rngDestination.CurrentRegion.EntireColumn.AutoFit
            wkbSourceBook.Close False
       
        Else
        'if user clicks Cancel
        End If
    End With
'Input name of file in list "C9" ****Limited to 900 rows from C9
    Worksheets("Start").Select
    lastRow = Range("C" & Rows.Count).End(xlDown).Row
    'Range("C9").Select
    For Each xCell In ActiveSheet.Range("C9:C900").Cells
        If Len(xCell) = 0 Then
            xCell.Select
            Exit For
        End If
    Next
'Input importing file name in list C9
ActiveCell.FormulaR1C1 = SelectedFileItem
 
'Go back to "Start" worksheet
Worksheets("Start").Activate
'Confirmation messagebox
MsgBox ("You have successfully imported the file you have choosen. Please check content in 'Global Spend' worksheet.")

Thanks

 

[Norie]

You should set a reference to the workbooks you are importing from when you open them, not after they've been opened.

If you have that then it's straightforward to get the file name later on.

As for copying that name down column C you could try something like this.

wkbCrntWorkBook.ActiveSheet.Range("C" & Rows.Count).End(xlUp.Offset(1).Resize(rngSourceRange.Rows.Count = wkbSourceBook.Name

 

[NdNoviceHlp]

Some code adjustments...

Else
'if user clicks Cancel
MsgBox "Pick a file!!!"
Exit Sub
End If
End With
'Input name of file in list "C9" ****Limited to 900 rows from C9
' Worksheets("Start").Select
lastRow = Sheets("Start").Range("C" & Rows.Count).End(xlDown).Row
If lastRow > 900 Then
MsgBox "More than 900 rows!!"
Exit Sub
End If
'Range("C9").Select
'For Each xCell In ActiveSheet.Range("C9:C900").Cells
'    If Len(xCell) = 0 Then
'        xCell.Select
'       Exit For
'   End If
' Next
'Input importing file name in list C9
'ActiveCell.FormulaR1C1 = SelectedFileItem

Sheets("Start").Range("C" & lastRow + 1).Value = SelectedFileItem
'****** OR Sheets("Start").Range("C" & lastRow + 1).Value = wkbSourceBook.Name

'Go back to "Start" worksheet
'Worksheets("Start").Activate
'Confirmation messagebox
MsgBox ("You have successfully imported the file you have choosen. Please check content in 'Global Spend' worksheet.")

Dave