v_gyku
09-06-2005, 02:54 AM
I am trying this code (given by DRJ):
THere is a browse button on the form.
When user will click it.. file open window will come.
User will select a workbook
Respective worksheet names will be displayed in a list box....
THis code is giving error as object variable or propertynot set
NOt adding the sheetnames to the list box...
Private Sub cmdbrowsebook_Click()
Dim list1 As ListBox
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
xtFileLocation = FName
For Each ws In Wkb.Worksheets
' list1.AddItem (ws.name)
SheetList.Add ws.name
Next
Wkb.Close SaveChanges:=False
n = SheetList.Count
For i = 1 To n
list1.AddItem (SheetList(i))
'Msg = Msg & vbNewLine & SheetList(i)
Next i
'Msg = vbNewLine & vbNewLine & Msg
' MsgBox Msg, vbInformation, "Workbook Information"
'txtFileLocation = FName
application.EnableEvents = True
application.ScreenUpdating = True
application.DisplayAlerts = True
Set SheetList = Nothing
Set ws = Nothing
Set Wkb = Nothing
End Sub
THere is a browse button on the form.
When user will click it.. file open window will come.
User will select a workbook
Respective worksheet names will be displayed in a list box....
THis code is giving error as object variable or propertynot set
NOt adding the sheetnames to the list box...
Private Sub cmdbrowsebook_Click()
Dim list1 As ListBox
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
xtFileLocation = FName
For Each ws In Wkb.Worksheets
' list1.AddItem (ws.name)
SheetList.Add ws.name
Next
Wkb.Close SaveChanges:=False
n = SheetList.Count
For i = 1 To n
list1.AddItem (SheetList(i))
'Msg = Msg & vbNewLine & SheetList(i)
Next i
'Msg = vbNewLine & vbNewLine & Msg
' MsgBox Msg, vbInformation, "Workbook Information"
'txtFileLocation = FName
application.EnableEvents = True
application.ScreenUpdating = True
application.DisplayAlerts = True
Set SheetList = Nothing
Set ws = Nothing
Set Wkb = Nothing
End Sub