Use userform collected data to open a file

**gophins** · 10-29-2009, 12:08 PM

Hi, I'm trying to use a userform to open a previously saved file to avoid having the users dig through the folder and maybe work on the wrong file.

I have a file I have created to test if the code works, but the macro keeps giving me the reponse I set in case it doesn't find the file.

The data for Account (Account1 and Subaccount1) is coming from a text box, as well as for category (Category1). The data for Company is an optionbutton frame with 4 options for the user to select from. Thanks in advance for any help!!!

Private Sub CommandButton1_Click()

Dim Company As String
Dim Account As String
Dim Category As String

Account = Val(Account1.Text) & "_" & Val(SubAccount1.Text)
Category = Val(Category1.Text)

If Account1.Text = "" Then
    MsgBox "You must enter an account number."
    Unload UserForm2
    UserForm2.Show
    Exit Sub
End If

If SubAccount1.Text = "" Then
    MsgBox "You must enter a sub-account number."
    Unload UserForm2
    UserForm2.Show
    Exit Sub
End If

If Category1.Text = "" Then
    MsgBox "You must enter a category number."
    Unload UserForm2
    UserForm2.Show
    Exit Sub
End If

'Transfer company number
If OptionCo10.Value = True Then Company = Val("10")
If OptionCo30.Value = True Then Company = Val("30")
If OptionCo56.Value = True Then Company = Val("56")
If OptionCo11.Value = True Then Company = Val("11")

'Open file

If Dir("J:\Finance\xls\" & _
        Company & " - " & Account & " - " & Category & ".xls") <> "" Then
        
        Workbooks.Open "J:\Finance\xls\" & _
        Company & " - " & Account & " - " & Category & ".xls", ReadOnly:=False
        Windows("2010 Plan Builder.xls").Activate
        Application.DisplayAlerts = False
        ActiveWorkbook.Close
        Application.DisplayAlerts = True

Else

Dim Msg As String, Title As String
Msg = "The file you have requested could not be located."
Msg = Msg & vbNewLine & vbNewLine
Msg = Msg & "Please make sure you have entered the correct information."
MsgBox Msg, , Title

End If

End Sub

**royUK** · 10-29-2009, 12:11 PM

Store the path & name in a hidden sheet

**gophins** · 10-29-2009, 12:19 PM

Roy, thanks for the response.

I'm not quite sure I understand, do you mean I must transfer the data I collect in the userform to a cell and then it would find the file?

Thank you.

**royUK** · 10-29-2009, 02:03 PM

I mant you could store the path in a worksheet then retrieve it with the code.

can you attach a sample workbook

**gophins** · 10-29-2009, 03:06 PM

Here is the first tab of the spreadsheet. The "Continue on a previously saved account" has the macro in question attached to it. I had removed other tabs but it should not have an impact.

Thank you!

**royUK** · 10-29-2009, 03:48 PM

I can't really heck this but it looks like the name is incorrect try checking it by adding a messagebox

'Open file
MsgBox "J:\Finance\xls\" & _
        Company & " - " & Account & " - " & Category & ".xls"
If Dir("J:\Finance\xls\" & _
        Company & " - " & Account & " - " & Category & ".xls") <> "" Then