How to select or open file folder and then choose a file using forms.

[dariusd77]

So lets say i want to have a spin button that scrolls through each month of the year(or drop down button). And each month is the name of each folder.. how do I do that.. How do i select at folder level not at the file level in the folder ..Because after they select folder then i want a form to come up that the user can then use to select the file. Each month will have the same number of files with the same name.
Example
folder january
file 1
file 2
folder February
file 1
file 2
folder March
file 1
file 2
each month ..........

So basically they will first choose the month they want using a spin button or drop down box..which = a particular folder

ie. C:\Users\ddempsey\Desktop\projects\dashboard project\MSO dashboard\New folder\Jan
ie C:\Users\ddempsey\Desktop\projects\dashboard project\MSO dashboard\New folder\Feb

once they selected the folder(month).another form(using command buttons or option buttons) will show up giving them 5 choices..which are 5 files.. within each folder, and each file is named exactly the same in each folder.

ie C:\Users\ddempsey\Desktop\projects\dashboard project\MSO dashboard\New folder\Jan\2013 Open Jobs
ie C:\Users\ddempsey\Desktop\projects\dashboard project\MSO dashboard\New folder\Jan\2013 Open orders
ie C:\Users\ddempsey\Desktop\projects\dashboard project\MSO dashboard\New folder\Feb\2013 Open Jobs
ie C:\Users\ddempsey\Desktop\projects\dashboard project\MSO dashboard\New folder\Feb\2013 Open orders

 

[ChrisM]

Since all months are named the same and all files are laid out the same, why do you even have to expose the file browser to your users? Why not just use 2 dropdowns 1) Month 2) File type, then use the results of those two controls to find the file automatically?

Or the second drop down could be a grouping of Option buttons, like:

O - Open Jobs
O - Open Order

and the user can just select whichever they are interested in.

 

[dariusd77]

Since all months are named the same and all files are laid out the same, why do you even have to expose the file browser to your users? Why not just use 2 dropdowns 1) Month 2) File type, then use the results of those two controls to find the file automatically?

Or the second drop down could be a grouping of Option buttons, like:

O - Open Jobs
O - Open Order

and the user can just select whichever they are interested in.

I was thinking the same thing. So now i am trying to first do it at the month level. I created a list for a drop down box for the month. Then I have code to pull the month selected and find the file and post in a list. but its not work.. I thought I should add the code to the dropbox module in the dropButt******* event..but it doesn't work..any suggestions?

Here is the code

Private Sub cboMonthNames_DropButt*******()
 
Dim myfile
Dim strFileName As String


myfile = cboMonthNames.Value


Const FilePath = "C:\Users\ddempsey\Desktop\projects\dashboard project\MSO dashboard\New folder"
strFileName = Dir(FilePath & myfile & "\Section*.xls")


lisFilenames.Clear


Do While strFileName <> vbNullString
   lisFilenames.AddItem FilePath & myfile & strFileName
   strFileName = Dir()
Loop


End Sub

 

[ChrisM]

I still don't think you need to load files to the second listbox if the files always have the same name, but, here is your code:

Private Sub cboMonthNames_DropButt*******()
 
Dim myfile As String
Dim strFileName As String
Dim sFilePath As String

myfile = cboMonthNames.Value & "\"

sFilePath = "C:\Users\ddempsey\Desktop\projects\dashboard project\MSO dashboard\New folder"
strFileName = Dir(sFilePath & myfile & "Section*.xls")

lisFilenames.Clear

Do While strFileName <> vbNullString
   lisFilenames.AddItem sFilePath & myfile & strFileName
   strFileName = Dir()
Loop

End Sub

 

[dariusd77]

I was thinking the same thing. So now i am trying to first do it at the month level. I created a list for a drop down box for the month. Then I have code to pull the month selected and find the file and post in a list. but its not work.. I thought I should add the code to the dropbox module in the dropButt******* event..but it doesn't work..any suggestions?

Here is the code

Private Sub cboMonthNames_DropButt*******()
 
Dim myfile
Dim strFileName As String


myfile = cboMonthNames.Value


Const FilePath = "C:\Users\ddempsey\Desktop\projects\dashboard project\MSO dashboard\New folder"
strFileName = Dir(FilePath & myfile & "\Section*.xls")


lisFilenames.Clear


Do While strFileName <> vbNullString
   lisFilenames.AddItem FilePath & myfile & strFileName
   strFileName = Dir()
Loop


End Sub

Ok but where should I put the code..? in the dropbox module? Under which event?

 

[ChrisM]

I assume when you say dropbox you really mean combobox? There is no control officially called a dropbox. You have comboboxes and listboxes. Assuming you meant combobox, the code should go in:

Private Sub ComboBox1_Change()

End Sub

and that code should be in the worksheet module for the sheet where the combobox is located.

 

[dariusd77]

I assume when you say dropbox you really mean combobox? There is no control officially called a dropbox. You have comboboxes and listboxes. Assuming you meant combobox, the code should go in:

Private Sub ComboBox1_Change()

End Sub

and that code should be in the worksheet module for the sheet where the combobox is located.

Yes I meant combobox.. people on Youtube call it everything from combobox to dropbox..and other things..

Thank you..

 

[dariusd77]

Yes I meant combobox.. people on Youtube call it everything from combobox to dropbox..and other things..

Thank you..

I tried to do it this way. I have two dropboxes. 1 is for the month =cboMonthNames the other is for the file = cboFileName
I have a command button right now just titled commandbutton2.
Here is the code I used but it is not working. I believe I am somehow using the Workbooks.Open incorrectly in my code.

<dl class="codebox" style="margin-right: 0px; margin-left: 0px; padding: 3px; border: 1px solid rgb(201, 210, 216); font-size: 13.333333015441895px; color: rgb(0, 0, 0); font-family: 'Lucida Grande', 'Trebuchet MS', Verdana, Helvetica, Arial, sans-serif; line-height: 18.19999885559082px;"><dt style="margin: 0px 0px 3px; padding: 0px; text-transform: uppercase; border-bottom-width: 1px; border-bottom-style: solid; border-bottom-color: rgb(204, 204, 204); font-size: 0.8em; font-weight: bold;">CODE: SELECT ALL</dt><dd style="margin: 0px; padding: 0px;"><code style="margin: 2px 0px; padding: 5px 0px 0px; overflow: auto; display: block; height: auto; max-height: 600px; font-size: 0.9em; font-family: Monaco, 'Andale Mono', 'Courier New', Courier, mono; line-height: 1.3em; color: rgb(46, 139, 87);">Private Sub CommandButton2_Click()

Dim myfolder
Dim myfile
Dim strFileName As String
myfolder = cboMonthNames.Value
myfile = cboFileName.Value

Const FilePath = "C:\Users\ddempsey\Desktop\projects\dashboard project\MSO dashboard\New folder"
strFileName = Dir(FilePath & myfolder & myfile & ".xls")


Workbooks.Open ("strFileName")


End Sub
</code></dd></dl>

The error I'm getting is run-time 1004

strFileName.xlsx could not be found. Check the spelling of the file name, and verify that the file location is correct.

 

[ChrisM]

Just take the quotes out:

Workbooks.Open (strFileName)

There's no reason to declare FilePath as a Constant. it doesn't hurt, but it doesn't really make sense. Just Dim it as a String. And Dim myfolder and myfile as Strings as well, not Variants. Again it doesn't hurt, but it's the correct way to do it.

You also don't need to use the Dir() function since you are building the entire path yourself. For that matter, you don't even need to use the Filepath variable, just put it all in strFileName:

strFileName = "C:\Users\ddempsey\Desktop\projects\dashboard project\MSO dashboard\New folder" & "\" & myfolder & "\" & myfile & ".xls"

 

[dariusd77]

Just take the quotes out:

Workbooks.Open (strFileName)

There's no reason to declare FilePath as a Constant. it doesn't hurt, but it doesn't really make sense. Just Dim it as a String. And Dim myfolder and myfile as Strings as well, not Variants. Again it doesn't hurt, but it's the correct way to do it.

You also don't need to use the Dir() function since you are building the entire path yourself. For that matter, you don't even need to use the Filepath variable, just put it all in strFileName:

strFileName = "C:\Users\ddempsey\Desktop\projects\dashboard project\MSO dashboard\New folder" & "\" & myfolder & "\" & myfile & ".xls"

We are getting close.. Now I am getting the following error.

Run time error 1004 C:\Users\ddempsey\Desktop\projects\dashboard project\MSO dashboard\New folder\July\FSO Open Report.xls could not be found. check the spelling of the file name, and veiry that the file location is correct.

This is the exact path to that file C:\Users\ddempsey\Desktop\projects\dashboard project\MSO dashboard\New folder\July\FSO Open Report.xls

Something is wrong. Could it be case sensative.