Any smoother way for better referencing a workbook with path with repsective worksheets Loaded in combobox of each workbook

NimishK

Well-known Member
Joined
Sep 4, 2015
Messages
688
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
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
Thanks in advance
NimishK
 

Excel Facts

When they said...
When they said you are going to "Excel at life", they meant you "will be doing Excel your whole life".
When your code gets to the row of code:
Set wks = Workbooks(txtFilePath.Text).Worksheets(cmbSheetNames.Text)
... and goes into break mode, do you actually look at what txtFilePath.Text and cmbSheetNames.Text have as values?
 
Upvote 0
Glenn

Thanks for the response.

Did you try the code at your end ?
When your code gets to the row of code:
Set wks = Workbooks(txtFilePath.Text).Worksheets(cmbSheetNames.Text)
... and goes into break mode, do you actually look at what txtFilePath.Text and cmbSheetNames.Text have as values?

txtFilePath,Text shows the path of the folder and the file and cmbSheetnames.Text displays the sheet. and Combobox displays the list of sheets

It is just when clicking on another sheet of cmbsheetnames Errors are generated

NimishK
 
Upvote 0
You said " i get subscript out of range with following syntax" ... with the line of code you highlighted ... I asked for the values of the variables so I could ask whether the items existed. You have not answered, and have instead explained what the variables are for. If you are getting "subscript out of range", then you need to work out which subscript is causing the error ... that will be one of those variables not equalling an actual object.
 
Upvote 0
''i get subscript out of range with following syntax
Set wks = Workbooks(txtFilePath.Text).Worksheets(cmbSheetNames.Text)
That error message indicates that you want to make a reference to a NOT opened workbook. Such an attempt always fails.
 
Upvote 0
GlennUK
You said " i get subscript out of range with following syntax" ... with the line of code you highlighted ... I asked for the values of the variables so I could ask whether the items existed. You have not answered, and have instead explained what the variables are for. If you are getting "subscript out of range", then you need to work out which subscript is causing the error ... that will be one of those variables not equalling an actual object.
? The Values in the Error Subscript out of Range when hilited in Yellow for Workbooks(txtFilePath.Text). showed full Path withFile name Worksheets(cmbSheetNames.Text) displayed the Name of the sheet and combobox was loaded with sheet names
HTC

GWteb
That error message indicates that you want to make a reference to a NOT opened workbook. Such an attempt always fails.
Appreciate Your Remark. Yes The workbook is not Opened. What do you suggest for smoother operation ?

Thanks
NimishK
 
Upvote 0
Short answer: open your workbook, for example: Set wks = Workbooks.OPEN(txtFilePath.Text).Worksheets(cmbSheetNames.Text)

Less short answer: if you want to set a reference to an object it has to be "alive", so loaded into memory otherwise there is nothing to refer to.
 
Upvote 0
Short answer: open your workbook, for example: Set wks = Workbooks.OPEN(txtFilePath.Text).Worksheets(cmbSheetNames.Text)

Less short answer: if you want to set a reference to an object it has to be "alive", so loaded into memory otherwise there is nothing to refer to.
ie I've no choice but to Set wks = Workbooks.OPEN(txtFilePath.Text).Worksheets(cmbSheetNames.Text)
VBA Code:
Set wks = Workbooks.Open(txtFilePath.Text).Worksheets(cmbSheetNames.Text)

With wks
.Activate
End With
Where do you think ideally i can incoroprate wkBook.CLOSE

2nd thing: As the file is opened with Run Time with respective Sheet Name clicked . File does not close when clicked on Close button of file Also i tried to write text in a cell but it did not.

NimishK
 
Upvote 0
I have not studied your code exceptionally well, but from what you tell us, you are using a modal userform. In such a case, the possibilities are indeed limited within the user interface (UI) of Excel. A modal userform is waiting for input and blocks the UI, similar with the workbook open file dialog (ribbon > file > open > computer > browse) which is also a modal window.
The purpose of a userform determines the choice of te programmer if he should use a modal or a modeless userform. It is not entirely clear to me what you're ultimately trying to achieve so it's difficult to give you good advice.
 
Upvote 0
you are using a modal userform
Good Observation . Changed to vbModeless or rather to showModal = False in Property Value of Userform1

It is not entirely clear to me what you're ultimately trying to achieve so it's difficult to give you good advice.
As #1
to get contents of of worksheets of file when clicked on any worksheet of that workbook.
? Actually wanted to get get contents of worksheet when clicked on sheet but without opening workbook. so as per your reply #7. will it be possible to load file into memory and get sheet names and its respective contents

NimishK
 
Upvote 0

Forum statistics

Threads
1,224,522
Messages
6,179,297
Members
452,903
Latest member
Knuddeluff

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top