How to open files in excel using VBA

[dariusd7]

I am working on a project to create a user interface in excel that would have a form with various button, spin buttons, option buttons etc.

So first thing i want to learn is how to open a file in excel through VBA. This is the code I came up with but it doesn't work.
I have a folder on my desktop called notes. I am using it as my test folder to learn code I need for this project.
In the folder I have other folders one is called January the other is February.

I don't think I will use a msg box for this in the actual project. I will use a spin button for this, but for now this is what I came up with.

Code: Select all

Sub open_file()

Dim myfile
myfile = InputBox("For what month?")

Dim FileName As String
Const FilePath = "C:\users\ddempsey\Desktop\Notes\"
FileName = Dir(FilePath & myfile & "Section*.xls")

Do While FileName <> vbNullString
Workbooks.Open FileName:=FileName
FileName = Dir()

Loop

End Sub



Appreciate any help I can get.

Thanks

[Sisyphus]

I can't see where your code might be wrong. I have rearranged the sequence - constants at the top, followed by Dim statements, followed by code - but I don't think that that makes a difference to anything but my sense of good order. This code should work. If it doesn't, what error do you get?

Code: Select all

Option Explicit

Sub Open_File()

    Const FilePath = "C:\users\ddempsey\Desktop\Notes\"
   
    Dim MyFile As String
    Dim Fn As String
   
    MyFile = InputBox("For what month?")
   
    Fn = Dir(FilePath & MyFile & "Section*.xls")
    Do While Fn <> ""
        Workbooks.Open FileName:=Fn
        Fn = Dir()
    Loop
End Sub

Note that your choice of FileName as a variable (string) for the property FileName is a bit awkward and might cause the code to fail. Better don't borrow reserved words form VB to name your variables. I have also changed your test of the Dir string to be "" instead of vbNullString. I think vbNullstring should work, but I seem to recall that the handbook uses "".

Take a look at this thread. It contains code allowing you to select a file using Excel's own File Open dialog box.

Code: Select all

http://www.excelkey.com/forum/viewtopic.php?f=5&t=2746

[dariusd7]

So i check out the link you referenced, and did as it said. Wow you can do a lot with VB. So here is what I am trying to do. How can I accomplish this.

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 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..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 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

[dariusd7]

Ok so I figure I use a drop down box to control the month list and then feed that choice into a code that will then pull a list of files in that folder. However i can't quite get teh code to work and not sure if i have it in the correct event.

Code: Select all

Private Sub cboMonthNames_DropButtonClick()
 
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


When I run the program nothing happens. I able to select a month from the drop down box, but nothing seems to happen afterwards.

[Sisyphus]

This thread started by being about how to open a file. Now it is about how to createa file name. You appear stuck in the wrong groove. Try a new approach from scratch:-

• How many files do you wish to open?
  Since you can only look at one at a time the presumption is that you would need only one. Therefore you only need one file name.
• How many variables are there in the file name?
  Apparently 2, the month and the section.
  However, I suspect that you might have more, like Open, Closed and Pending. Since the total is only five it may not be worth the create an extra drop-down.
  The year appears to be much more important. in 2014 you will have 10 files to pick from. Consider creating a folder for each year with 12 folders of 5 files eah. But you would still need a drop-down to choose the year.

It would seem, then, that you need three drop-downs to pick the two variables, plus an OK button to generate the file name and open the file.
You could have these three items on a worksheet. Or you could have them on a userform which you call from a single button on the worksheet or even with a keyboard shortcut. My memory of shortcuts isn't a pleasant one. Just when you get used to them they get lost.

However, that all of the above appears to be besides the point. Where is the code?
The code must be in a workbook. Or you could plan on having it in an add-in.
It seems that you have no use for that workbook in your work flow, or do you?
So, this workbook would be like a dashboard: a single sheet with the drop-downs and an OK button. When you click OK the new workbook becomes visible and the dashboard stays in the background.
How is that?

Anyway, to make some headway, here is the code for creating the filename. I have named it FullName because 'FullFileName' is a word VB uses, and Ffn is a name that I use for FullFileName.

Code: Select all

Option Explicit

Private Sub FullName()
    ' Full file name

    Const PathName As String = "C:\Users\ddempsey\Desktop\projects\dashboard project\MSO dashboard\New folder"
   
    Dim Yr As String                    ' "Year" is a VB reserved word
    Dim Fold As String                  ' "Folder" is a VB reserved word
    Dim Fn As String                    ' File Name
   
    Yr = "2014"
    Fold = "Jan"
    Fn = "Open Jobs"
   
    FullName = WithSeparator(WithSeparator(PathName) & Fold) & _
                        Yr & Fn & ".xls"
End Sub

Private Function WithSeparator(ByVal Ffn As String, _
                               ByVal Without As Boolean) As String

    Dim Fn As String
   
    Fn = Trim(Ffn)
    Do While Right(Fn, 1) = "\" And Len(Fn) > 0
        Fn = Left(Fn, Len(Fn) - 1)
    Loop
    If Not Without Then Fn = Fn & "\"
    WithSeparator = Fn
End Function

Eventually, this sub will probably become a function that picks the variables Yr, Fold and Fn from the worksheet or UserForm and returns the FullFileName of the file you wish to open.
Observe the use of the 'WithSeparator' function which ensures that you don't have to many or too few path separators.

[dariusd7]

OK. to answer some questions here. I plan to use a userform, that will open up other workbooks. so yes the form would fade inthe background until they are finish withthe workbook and there will be a button on the workbook page that they can click to get back to the form menu

Right now I have two dropboxes. One selects the month, and the other will select the actual file.
the actual files names ares the following

1. Adjustments
2. Closed Jobs
3. FSO Open Report
4. MSO Open Report
5. MWO Open Report
6. Productivity Report

So a full file name or path would be

"C:\Users\ddempsey\Desktop\projects\dashboard project\MSO dashboard\New folder\March\MSO Open Report"

Not sure why I would need 3 dropboxes. since there are really only two variable the month and file name.
So I would drop the yr part of the code you created, and finally I believe I should put this code in the command button as such? or should I put the code somewhere else?

Code: Select all

Private Sub btnOpenFile_Click()
 code
End sub


Thanks again for you help Sisyphus

[Sisyphus]

You wrote,

I plan to use a userform, that will open up other workbooks. so yes the form would fade inthe background until they are finish withthe workbook and there will be a button on the workbook page that they can click to get back to the form menu

But the question I raised was what will create the userform? If there is a button in the workbook, which is the workbook that has the button in it? Presumably, that will also be the one that has the code for the user form. It can't be any of the 60 files, I argue. Therefore it must be a dedicated workbook that does nothing else but have this button. I have further argued that, if it only has one button to call the userform, why not let it have 3 drop-downs plus a button and no userform?

Absolutely correct. If you don't have a year then you won't need more than 2 drop-downs. But whether you have two drop-downs or three really makes preciously little difference, since they all work the same way.

The code requires the folder and the file name before it can work. Both will be retrieved from either a user form or a cell in a worksheet. Retrieving these variables form a user form will be more intricate because you have to manage the form and the controls in it before you can retrieve data from them. Using cells in a worksheet is easier. But, either way, the location from where you wish to retrieve the data must exist before you can write code - and test it - that does the retrieving. The question which button to press to run the code is of comparatively less importance. So, the next step is to physically create the drop-downs.

[dariusd7]

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.

Code: Select all

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


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.

[Sisyphus]

OK. Learn basic testing.
There is one rule, and there are two ways (actually more, but I want to explain only two).
This is the rule: Never presume that any of your code does what it is supposed to do until you have tested it.
Applied to your code that means that you should test this line of your code:
myfolder = cboMonthNames.Value
Does the variable MyFolder really contain the expected value?

Here are the two ways:-

1. Use a MsgBox
   The code is MsgBox MyFolder
2. Print to the Immediate Window
   The code is Debug.Print MyFolder
   You may have to make the Immediate Window visible in the VB Editor.

Actually, if the variable is empty the Immediate Window won't show anythign and you may wonder whether the Debug.Print command was really carried out. In that case, add a word to it, like, Debug.Print "MyFolder = "; MyFolder. The semicolon indicates that the next item should also be printed. Use a comma if you would like to have a tab space before the next item.
So, this is what your code should look like:

Code: Select all

Private Sub CommandButton2_Click()

    Const FilePath = "C:\Users\ddempsey\Desktop\projects\dashboard project\MSO dashboard\New folder"

    Dim MyFolder
    Dim myfile
    Dim strFileName As String

    MyFolder = cboMonthNames.Value
    Debug.Print MyFolder
'    myfile = cboFileName.Value

'    strFileName = Dir(FilePath & myfolder & myfile & ".xls")
'    Workbooks.Open ("strFileName")
End Sub

Continue like that with MyFile and strFileName. Only after strFileName looks to you like it should be correct do you need to actually try to open the file.

Observe that I moved your declaration of the constant to the top of your code. It is good practice to separate declarations from the code. Some declarations must be at the top of the module. Within a procedure the rules aren't as strict, but how can you see the code if it is cluttered up with declarations?

Please study this line of your code, strFileName = Dir(FilePath & myfolder & myfile & ".xls"). Why is it different from the code I provided? And what effect does the difference have?
Actually, FilePath & myfolder & myfile & ".xls" already is supposed to be the file name (except that you omitted both, the backslash at the end of the path constant AND the function I gave you that would make sure it is there). Using that file name in the Dir function would just return the same file name. However, if the file doesn't exist the Dir function will return nothing, "".
So, you arrive at different code for that part.

Code: Select all

    strFileName = FilePath & myfolder & myfile & ".xls"
    If Len(Dir(strFileName)) Then
        ' proceed to open the file
    End If

Of course, follow rule #1 laid out above. Test both, strFileName and Dir(strFileName) before you do anything with these constructs.

[dariusd7]

Sisyphus It is working, the reason the code looks a little different is because instead of using a list box, I am using two comboxes. One for the month, and the other for the file. It is working great. Initially I thought it wasn't working but what I found out was that since I was actually in the folder it would not access it. Once I got out of the file and ran the code it worked. Now I am working on how to reopen the form from the workbook, because I have it set up so that when the workbook is open the form is closed or hidden. So now I am trying to create a code so that when the user clicks on the button on the worksheet it will take them back to the form. The problem I am having now is that since the form exist in a different workbook it will not open it. I tried the following code but it does not work.

The error I'm getting Run-time error '424':

Code: Select all

Private Sub CommandButton2_Click()
frmuserFomrExample.Show
End Sub