CODEWITHSUNDEEP
excelvbaexcel-2010
user2837847
user2837847

Reputation: 137

Return only filename in getopenfilename

is there any way i can get the file name out of this statement? I'm trying to get the file name out of this statement so i can proceed with my coding..

Basically i'm trying to open multiple workbook and open multiple sheets to according to the workbook's name.

        strWorkbookNameAndPath = Application.GetOpenFilename _
       (FileFilter:=strFilt, _
        FilterIndex:=intFilterIndex, _
        Title:=strDialogueFileTitle, _
        MultiSelect:=True)

       fileArraySize = UBound(strWorkbookNameAndPath, 1) 
       nameArr = Split(strWorkbookNameAndPath, "\")
       strName = nameArr(UBound(nameArr))
       Worksheets.Add().Name = strName

       For i = 1 To fileArraySize

       Set wkbImportedWorkbook = Workbooks.Open(strWorkbookNameAndPath(i))
       Set wksImportedWorksheet = wkbImportedWorkbook.Sheets("Sheet1")
       With wksImportedWorksheet
       lrow = .Range("A" & .Rows.count).End(xlUp).Row
       Set rngImportCopyRange = .Range("A1:A" & lrow).EntireRow
       rngImportCopyRange.Copy
       wksMasterWorksheet.Range("A" & Rows.count).End(xlUp).Offset(1, 0).PasteSpecial        xlPasteValues
End With
wkbImportedWorkbook.Close
Set wkbImportedWorkbook = Nothing
Set wksImportedWorksheet = Nothing
Next i

Upvotes: 0

Views: 1514

Answers (1)

Manuel Allenspach
Manuel Allenspach

Reputation: 12745

add this code:

dim nameArr
dim strName as string

nameArr = Split(strWorkbookNameAndPath, "\")
strName = nameArr(UBound(nameArr))

EDIT:

strWorkbookNameAndPath = Application.GetOpenFilename _
  (FileFilter:="", _
  FilterIndex:=0, _
  Title:="", _
  MultiSelect:=True)

fileArraySize = UBound(strWorkbookNameAndPath, 1)

For i = 1 To fileArraySize

  nameArr = Split(strWorkbookNameAndPath(i), "\")
  strName = nameArr(UBound(nameArr))
  Worksheets.Add().Name = strName

  Set wkbImportedWorkbook = Workbooks.Open(strWorkbookNameAndPath(i))
  Set wksImportedWorksheet = wkbImportedWorkbook.Sheets("Sheet1")
  With wksImportedWorksheet
    lrow = .Range("A" & .Rows.Count).End(xlUp).Row
    Set rngImportCopyRange = .Range("A1:A" & lrow).EntireRow
    rngImportCopyRange.Copy
    wksMasterWorksheet.Range("A" & Rows.Count).End(xlUp).Offset(1, 0).PasteSpecial xlPasteValues
  End With
  wkbImportedWorkbook.Close
  Set wkbImportedWorkbook = Nothing
  Set wksImportedWorksheet = Nothing
Next i

Upvotes: 1

Related Questions