Hello
I am trying to get combobox loaded with all worksheets of Each different files or workbook with file extension *.xlsx and to read its few range contents in multiline textbox
there is a listbox which is listFiles and command button for file dialog to display files or workbooks
so when clicked on listFiles i get path of the workbook and filename and able to load the names of worksheets in combox cmbSheetnames.
I am not able to move on when cliked on sheet names. So when clicked on combobox of sheetnames with particular sheet name . The workbook opens. so again this becomes rather irritating as how many workbooks could be opened and i get subscript out of range with following syntax
Set wks = Workbooks(txtFilePath.Text).Worksheets(cmbSheetNames.Text)
Any smoother way for better referencing a workbook with path with repsective worksheets Loaded in combobox of each workbook and to get contents of of worksheets of file when clicked on any worksheet of that workbook.
Code Follows
Thanks in advance
NimishK
I am trying to get combobox loaded with all worksheets of Each different files or workbook with file extension *.xlsx and to read its few range contents in multiline textbox
there is a listbox which is listFiles and command button for file dialog to display files or workbooks
so when clicked on listFiles i get path of the workbook and filename and able to load the names of worksheets in combox cmbSheetnames.
I am not able to move on when cliked on sheet names. So when clicked on combobox of sheetnames with particular sheet name . The workbook opens. so again this becomes rather irritating as how many workbooks could be opened and i get subscript out of range with following syntax
Set wks = Workbooks(txtFilePath.Text).Worksheets(cmbSheetNames.Text)
Any smoother way for better referencing a workbook with path with repsective worksheets Loaded in combobox of each workbook and to get contents of of worksheets of file when clicked on any worksheet of that workbook.
Code Follows
VBA Code:
Option Explicit
Public wkBook As Excel.Workbook
Private Sub cmdDisplayFiles_Click()
listFiles.Enabled = True
Call FolderPicker
End Sub
Public Sub FolderPicker()
listFiles.Clear
Dim sFile As String
Dim sWorkbook As String
Dim gsFilter As String
Dim gsFolderPath As String
Dim xFileArray As Variant
gsFilter = "*.xlsx"
sFile = Dir(gsFolderPath & "\" & gsFilter)
With Application.FileDialog(msoFileDialogFolderPicker)
.Title = "Select the Folder..."
If .Show Then
gsFolderPath = .SelectedItems(1)
txtFilePath.Text = gsFolderPath
Else
MsgBox "No Folder Path Selected"
Exit Sub
End If
End With
If Dir(gsFolderPath & "\" & gsFilter) = "" Then
MsgBox "There are no files of the type:" & vbCrLf & _
gsFolderPath & "\" & gsFilter
Exit Sub
Else
sFile = Dir(gsFolderPath & "\" & gsFilter)
Do While sFile <> ""
listFiles.AddItem gsFolderPath & "\" & sFile
sFile = Dir
Loop
End If
End Sub
Private Sub listFiles_Click()
Dim wks As Excel.Worksheet
Dim wksCnt As Integer
txtFilePath.Text = listFiles.Text
listFiles.Clear
Set wkBook = Workbooks.Open(Filename:=txtFilePath.Text)
For wksCnt = 1 To wkBook.Worksheets.Count
cmbSheetNames.AddItem wkBook.Worksheets(wksCnt).Name
Next wksCnt
wkBook.Close
End Sub
Private Sub cmbSheetNames_Click()
Dim wks As Excel.Worksheet
Dim strSheetName As String, gsFilter As String , ContentStr As String
gsFilter = "*.xlsx"
[B][I]''i get subscript out of range with following syntax [/I][/B]
Set wks = Workbooks(txtFilePath.Text).Worksheets(cmbSheetNames.Text)
[I]''[B]and could not move on to get contents of few values ranges of each worksheet[/B][/I]
End Sub
NimishK