VBA help with File Dialog box

[utzja1]

I have a Access 2010 database where I want one of the fields on a form to launch the File Dialog box when the user clicks in the field. This field will contain a file path to one specific file. The File Dialog box will not open the file but rather it should only capture the path. Currently I have the user entering this directly, but a File Dialog box would make life easier for the user, who will be running Access 2007. To clarify, on some of the reports I produce from the same database, the user has the option to click on macro buttons for reference documents, and this file will be one of the targets.

I found several similar pursuits on this forum but none as simple as what I wanted. I also pulled some sample code from the MS Access Help page, but when I tried to run it, it bombed out at the following code:

Dim fd As FileDialog returned an error of "User-defined type not Defined"

Comments are greatly appreciated.

 

[pr2-eugin]

Dim fd As FileDialog returned an error of "User-defined type not Defined"

A common Error for missing references.. Try adding Microsoft Office 14.0 Object Library.. it would work just fine..

 

[jadown]

utzja1 can you please post your code. I'm trying to do the same thing. The dialog box shows up but when I select the file nothing happens. I posted a similar question a couple days ago.

Thanks!

 

[utzja1]

Thanks so much for the prompt reply! I had to do a double-take since there are Microsoft Object library and Microsoft Access Object library in the same list of references, but I've got it now.

Do you know off the top of your head if the Object Library 14.0 will create problems with someone running Access 2007?

 

[utzja1]

I'll post code when I get it working. Thanks to pr2-eugin, I've gotten the reference bug worked out but now I have to get back into it.

 

[utzja1]

When I run this module, the File Dialog box opens and I can browse to a file. When I click OK to close the dialog box, nothing happens. I would like to capture the file path and enter it into the current field. Any thoughts on what I'm missing? many thanks.


Private Sub Text74_Click()

Dim fd As FileDialog
Dim vrtSelectedItem As Variant

' Set up the File dialog box.
Set fd = Application.FileDialog(msoFileDialogFilePicker)
With fd
.AllowMultiSelect = False
.Title = "Browse to Select a File"
If .Show = True Then
Me.ActiveControl = vrtSelectedItem
Else
End If
End With

Set fd = Nothing

End Sub

 

[utzja1]

Private Sub Text74_Click()

Dim fd As FileDialog
Set fd = Application.FileDialog(msoFileDialogFilePicker)
Dim vrtSelectedItem As Variant

With fd
.AllowMultiSelect = False
.Title = "Browse to Select a File"
If .Show = -1 Then
For Each vrtSelectedItem In .SelectedItems
MsgBox "Selected item's path: " & vrtSelectedItem
Text74 = vrtSelectedItem
Next
End If
End With

Set fd = Nothing

End Sub

 

[jadown]

So did this work for you? I tried it and it shows a message box of the path then an error Object doesn't support this property or method when I click okay and the debugger points at Text47= vrtSelectedItem.

 

[utzja1]

Yes, it does work for me. Text47 is a control on a form that I'm using, so it works when I assign a value to it. Otherwise, you would get an error message since you would be assigning a value to an undeclared variable.

I set this up with an event handler so that when the User clicks in Text47, the file picker pops up automatically.

I hope this helps. I apologize for my lazy naming convention but I was so involved in trying to get this to work that I hadn't the time to come up with any witty or relevant.

Follow up if you still have problems. I'll be in the office for another 20 minutes and then back on Monday.

 

[pr2-eugin]

Sorry, I have not been around.. Have you solved the problem?

 

[jadown]

Thanks! Yes the file dialog box pops up like it's suppose to. However, I'm trying to get it to open a file when I select it and it's not quite working when I make the selection.

 

[pr2-eugin]

In the code that utzja1 has, he/she has placed it in a TextBox control.. Instead you can use the Application.FollowHyperlink to open the file..

Private Sub Text74_Click()
    Dim fd As FileDialog
    Set fd = Application.FileDialog(msoFileDialogFilePicker)
    With fd
        .AllowMultiSelect = False
        .Title = "Browse to Select a File"
        If .Show = -1 Then [URL="http://msdn.microsoft.com/en-us/library/office/aa204494%28v=office.10%29.aspx"]Application.FollowHyperlink[/URL] .SelectedItems(1)
    End With
    Set fd = Nothing
End Sub

If you look at the code I have removed the For Loop, as this is a bit pointless, as the MultiSelect is False, thus there will be only one file it needs to refer to.

 

[jadown]

pr2-eugin Thanks so much. The code that I used before utzia1 post was twice as long. This is short and straight to the point. Thanks again for your help you all.

What's a good book to learn VBA? One that starts at the very basic because after 10 years of not using this stuff you really forget everything.

 

[jadown]

pr2-eugin quick question for you. I added some code to what you posted to change the column headings in the file but it's not working. Here is the code.

Private Sub cmdTestButton_Click()
    Dim fd As FileDialog
    Set fd = Application.FileDialog(msoFileDialogFilePicker)
    With fd
        .AllowMultiSelect = False
        .Title = "Browse to Select a File"
        If .Show = -1 Then Application.FollowHyperlink .SelectedItems(1)
        
    End With
    
    
    'Change the column headings on the spreadsheet
    
    Dim FileName As String
    FileName = fd.SelectedItems(1)
    Workbooks.Open (FileName)
    Range("C1") = "Cost elem"
    Range("D1") = "Cost element descr"
    Range("E1") = "Per"
    Range("F1") = "Year"
    Range("G1") = "RefDocNo"
    Range("H1") = "User"
    Range("I1") = "Name"
    Range("J1") = "PartnerObj"
    Range("K1") = "Purchdoc"
    Range("L1") = "Descr"
    Range("M1") = "Material"
    Range("N1") = "Sales doc"
    Range("O1") = "Partner order"
    Range("P1") = "Postg date"
    Range("Q1") = "Value COCurr"
    Range("R1") = "Quantity"
    Range("S1") = "Quantity2"
    
    Workbooks.Close
    
     
    Set fd = Nothing
        
End Sub

 

[pr2-eugin]

Okay, you want to change the Content of the Opened file.. Good.. But you have no where in your code actually referred to the file.. I just thought you needed to open the file.. and do something else, if you need to manipulate the content of the file via Access VBA.. You first need to create or set the object then change use it.. something along the lines of..

Private Sub cmdTestButton_Click()
    Dim fd As FileDialog, FileName As String
    Set fd = Application.FileDialog(msoFileDialogFilePicker)
    With fd
        .AllowMultiSelect = False
        .Title = "Browse to Select a File"
        If .Show = -1 Then [COLOR=Red]FileName = .SelectedItems(1)[/COLOR]
    End With
 
    [COLOR=Green]'Change the column headings on the spreadsheet[/COLOR]
   [COLOR=Red] If Len(FileName) = 0 Then Exit Sub[/COLOR]
    
    [COLOR=Red]Dim xlObj As Excel.Application
    Set xlObj = New Excel.Application
    With xlObj[/COLOR]
        [COLOR=Red].[/COLOR]Workbooks.Open FileName
       [COLOR=Red] .[/COLOR]Visible = True
        [COLOR=Red].[/COLOR]Range("C1") = "Cost elem"
        [COLOR=Red].[/COLOR]Range("D1") = "Cost element descr"
        [COLOR=Red].[/COLOR]Range("E1") = "Per"
        [COLOR=Red].[/COLOR]Range("F1") = "Year"
        [COLOR=Red].[/COLOR]Range("G1") = "RefDocNo"
        [COLOR=Red].[/COLOR]Range("H1") = "User"
        [COLOR=Red].[/COLOR]Range("I1") = "Name"
        [COLOR=Red].[/COLOR]Range("J1") = "PartnerObj"
        [COLOR=Red].[/COLOR]Range("K1") = "Purchdoc"
        [COLOR=Red].[/COLOR]Range("L1") = "Descr"
        [COLOR=Red].[/COLOR]Range("M1") = "Material"
        [COLOR=Red].[/COLOR]Range("N1") = "Sales doc"
        [COLOR=Red].[/COLOR]Range("O1") = "Partner order"
        [COLOR=Red].[/COLOR]Range("P1") = "Postg date"
        [COLOR=Red].[/COLOR]Range("Q1") = "Value COCurr"
        [COLOR=Red].[/COLOR]Range("R1") = "Quantity"
        [COLOR=Red].[/COLOR]Range("S1") = "Quantity2"
       [COLOR=Red] .ActiveWorkbook.Save[/COLOR]
       [COLOR=Red] .[/COLOR]Workbooks.Close
        [COLOR=Red][I][B].Quit[/B][/I]
    End With[/COLOR]
    Set fd = Nothing
    [COLOR=Red]Set xlObj = Nothing[/COLOR]
End Sub

The highlighted code is what I have added.. I have not tested this, but in theory should work.. You can delete the italic code if you want.. Make sure that you add Microsoft Excel reference, Tools ->Reference -> Microsoft Excel **.* Object Library

 

[jadown]

Thank you so much. It works with no issues.

Thanks again all!