Insert a sheet into workbook using the open dialog

Johnzea

New Member
Joined
Apr 5, 2019
Messages
23
I would like to import an xlsm sheet with data into my workbook at the end of the last tab and have the new inserted sheet/tab name the xlsm filename being inserted less the path. below is my code but I cannot get it to work right. any thought to what i am missing or have wrong?

I switched to decaf tonight so i'm a little slow....any help is very much appreciated...thank you


With Application.FileDialog(msoFileDialogFilePicker)
.AllowMultiSelect = False
.Filters.Add "Excel Files", "*.xlsx; *.xlsm; *.xls; *.xlsb", 1
.Show

fullpath = .SelectedItems.Item(1)
End With

If InStr(fullpath, ".xls") = 0 Then
Exit Sub
End If
Sheets.Add(, Sheets(Sheets.Count)).Name = fullpath
 

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
8,974
Office Version
2007
Platform
Windows
Do you need something like this:

Code:
Sub Insert_sheet()
  Dim fullpath As Variant, wPath As String, wFile As String, wb As Workbook
  
  With Application.FileDialog(msoFileDialogFilePicker)
    .AllowMultiSelect = False
    .Filters.Add "Excel Files", "*.xlsx; *.xlsm; *.xls; *.xlsb", 1
    If Not .Show Then Exit Sub
    fullpath = .SelectedItems.Item(1)
  End With
  wPath = Left(fullpath, InStrRev(fullpath, "\") - 1)
  wFile = Mid(fullpath, InStrRev(fullpath, "\") + 1)
  wFile = Left(wFile, InStrRev(wFile, ".") - 1)
  Set wb = Workbooks.Open(fullpath)
  wb.Sheets(1).Copy after:=ThisWorkbook.Sheets(ThisWorkbook.Sheets.Count)
  ThisWorkbook.Sheets(ThisWorkbook.Sheets.Count).Name = wFile
  wb.Close False
End Sub
 

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
8,974
Office Version
2007
Platform
Windows
I'm glad to help you. Thanks for the feedback.
 

Forum statistics

Threads
1,082,601
Messages
5,366,571
Members
400,902
Latest member
fathima

Some videos you may like

This Week's Hot Topics

Top