PDA

View Full Version : File Browser In Excel To import Another Excel Spreadshead



mattster1010
10-20-2010, 07:21 AM
Afternoon,

I'm just wondering if anybody has already developed a file browser in excel that can browse to another excel spreadsheet and import the contains into a different tab within the same spreadsheet that contains the file browser?

Regards,

Mattster

mdmackillop
10-20-2010, 01:13 PM
Add to a userform containing a button and listbox



Option Explicit


Dim WB As Workbook
Dim wsTgt As Worksheet


Private Sub CommandButton1_Click()
Dim FileToOpen
Dim sh As
Worksheet


Set wsTgt = ActiveSheet
FileToOpen = Application _

.GetOpenFilename("Excel Files (*.xls), *.xls")
Application.ScreenUpdating =
False


If FileToOpen <> False Then
Set WB =
Workbooks.Open(FileToOpen)
End If
For Each sh In
WB.Sheets
ListBox1.AddItem sh.Name
Next



Application.ScreenUpdating = True



End Sub


Private Sub ListBox1_Click()
Sheets(ListBox1.Value).Cells.Copy wsTgt.Cells(1, 1)
WB.Close False
Unload UserForm1


End Sub

mattster1010
10-21-2010, 04:19 AM
Thanks for the code help mdmackillop!!

I get an error on the following line:

.GetOpenFilename ("Excel Files (*.xls), *.xls")


Invalid or unqualified reference

Do I need to add a reference within Excel?

Regards,

Mattster

Kenneth Hobs
10-21-2010, 05:32 AM
Posting can sometimes cause text flow problems. I fixed those and changed the B.Sheets to WB.Sheets.

Private Sub CommandButton1_Click()
Dim FileToOpen
Dim sh As Worksheet

Set wsTgt = ActiveSheet
FileToOpen = Application.GetOpenFilename("Excel Files (*.xls), *.xls")
Application.ScreenUpdating = False

If FileToOpen <> False Then
Set WB = Workbooks.Open(FileToOpen)
End If

For Each sh In WB.Sheets
ListBox1.AddItem sh.Name
Next

Application.ScreenUpdating = True
End Sub