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

dariusd77

New Member
Joined
Jan 24, 2013
Messages
26
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

Well-known Member
Joined
Jun 11, 2002
Messages
2,129
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

New Member
Joined
Jan 24, 2013
Messages
26
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
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

Well-known Member
Joined
Jun 11, 2002
Messages
2,129
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:

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

New Member
Joined
Jan 24, 2013
Messages
26
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
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

Well-known Member
Joined
Jun 11, 2002
Messages
2,129
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:

Code:
Private Sub ComboBox1_Change()

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

dariusd77

New Member
Joined
Jan 24, 2013
Messages
26
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:

Code:
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

New Member
Joined
Jan 24, 2013
Messages
26
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

Well-known Member
Joined
Jun 11, 2002
Messages
2,129
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

New Member
Joined
Jan 24, 2013
Messages
26
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.
 

Forum statistics

Threads
1,081,574
Messages
5,359,707
Members
400,545
Latest member
Damntheman30

Some videos you may like

This Week's Hot Topics

  • VBA (Userform)
    Hi All, I just would like to know why my code isn't working. Here is my VBA code: [CODE=vba]Private Sub OKButton_Click() Dim i As Integer...
  • List box that changes fill color
    Hello, I have gone through so many pages trying to figure this out. I have a 2020 calendar that depending on the day needs to have a certain...
  • Remove duplicates and retain one. Cross-linked cases
    Hi all I ran out of google keywords to use and still couldn't find a reference how to achieve the results of a single count. It would be great if...
  • VBA Copy and Paste With Duplicates
    Hello All, I'm in need of some input. My VBA skills are sub-par at best. I've assembled this code from basic research and it works but is...
  • Macro
    is it possible for a macro to run if the active cell value is different to the value above it
  • IF DATE and TIME
    I currently use this to check if date has passed but i also need to set a time on it too. Is it possible? [CODE=vba]=IF(B:B>TODAY(),"Not...
Top