View Full Version : Coding for Browse Button

09-06-2005, 12:52 AM
I have a form in which there is a button browse.... on click of which file open dialog window should be opened for selecting excel workbook.Once the excel workbook is selected then that workbookname should me displayed in the textbox and all the names of spreadsheets in that workbook will be displayed in a listbox.

Folowing code is for selecting a access databse . I want to select excel workbook. as soon as the workbook is selected all the respective sheeet names should be displayed in a list box.

Private Sub CmdBrowse_Click()
Dim i As Integer
Dim mypath As String
Dim multiselect As Boolean

If IsAppOpen("Access", "Application") Then
MsgBox "Please close all Access applications"
Exit Sub
End If

'Set the multiselect files value - only one file can be chosen
multiselect = True
filetype = "Access database"
FileFilter = "Access databases" & Chr(0) & "*.mdb*" & Chr(0)

fileArray = GetFilesBrowse(objBrowseButton:=CmdBrowse, _
objFileList:=txtFileLocation, _
strFileType:=filetype, _

List1.Enabled = True
Dim strFormName As Access.Form
Dim file As String

If txtFileLocation.Text = "" Then
Exit Sub
End If
file = Right(txtFileLocation.Text, Len(txtFileLocation.Text) - InStrRev(txtFileLocation.Text, "\"))

ShowAccessforms myDatabase:=filenames, myList:=List1, myFileLocation:=txtFileLocation

End Sub

Jacob Hilderbrand
09-06-2005, 01:02 AM
Try this example. I put the sheet names into a collection, in the example I just display the sheet names in a message box, but you can easily add them to a ListBox using AddItem.

Option Explicit

Sub WorkbookInformation()

Dim FName As String
Dim Msg As String
Dim SheetList As Collection
Dim i As Long
Dim n As Long
Dim ws As Worksheet
Dim Wkb As Workbook

Application.EnableEvents = False
Application.ScreenUpdating = False
Application.DisplayAlerts = False
Set SheetList = New Collection
FName = Application.GetOpenFilename("Excel Files (*.xls),*.xls")
If FName <> "False" Then
Set Wkb = Workbooks.Open(Filename:=FName)
End If
For Each ws In Wkb.Worksheets
SheetList.Add ws.Name
Wkb.Close SaveChanges:=False

n = SheetList.Count
For i = 1 To n
Msg = Msg & vbNewLine & SheetList(i)
Next i
Msg = FName & vbNewLine & vbNewLine & Msg
MsgBox Msg, vbInformation, "Workbook Information"

Application.EnableEvents = True
Application.ScreenUpdating = True
Application.DisplayAlerts = True

Set SheetList = Nothing
Set ws = Nothing
Set Wkb = Nothing

End Sub

09-06-2005, 11:33 AM
You're question is in the pay for answer forum.

I'll move your question to the Integration/Automation of Office Applications Help section of the forum so someone can help you there. :whistle: