Import CSV with Windows File Prompt

[Snowflake68]

I have the following code which imports a CSV file from a hard coded directory and filename (which is created dynamically) but I now need to change it so that the user receives a prompt to select the file they would like to import from a hard coded directory.
This is my current code which works a treat

Dim strFilepath As String
strFilepath = [Application].[CurrentProject].[Path] & "\DE_Data\Portfolio\IN\PortFolioTemplate_" & DLookup("[CustomerFileName]", "1_CustomerProspectSelection") & "_" & DLookup("[LISTREFERENCE]", "LookupListRef") & ".CSV"

DoCmd.TransferText acImportDelim, "PortFolioTemplate_Import Specification", "tbl_Import_Portfolio_Template_ALL" ',strFilepath

The file is a CSV file in a set format so the user knows that the file needs to be in this format but they need to be able to be able to import the file of their choosing.

I found this code on the web but I don't really know how to use it.

        Dim fileName As Variant
        Dim strFileName As String
        Set fileName = Application.FileDialog(msoFileDialogOpen)
        If fileName.Show Then
            strFileName = fileName.SelectedItems(1)
            DoCmd.TransferText acImportDelim, "", "tbl_Import_Portfolio_Template_ALL", strFileName, True
        End If

Is someone able to help me please. As I replaced my Docmd.Transfertext line to that of the new code (above) but I just get an error message which I dont understand.

Run-time error '-2147467259(80004005)';
Method 'FileDialog' of object _Application' Failed

the above error points at this line of code

        Set fileName = Application.FileDialog(msoFileDialogOpen)

I am not an expert and I dont pretend to be so all help is grateful received.

 

[Mark_]

Change

Dim fileName As Variant

To

Dim FileName As Office.FileDialog

 

[theDBguy]

Hi. May I suggest creating a custom function in a Standard Module? For example, copy and paste the following into a Standard Module and save it as Module1.

Public Function BrowseFile() As String
'thedbguy@gmail.com
'12/02/2019

Dim fd As Object

Set fd = Application.FileDialog(3)

With fd
    If .Show Then
        BrowseFile = .SelectedItems(1)
    End If
End With

Set fd = Nothing

End Function

Then, you just need to modify your original code to do this.

strFilePath = BrowseFile()

Hope it helps...

 

[Gasman]

You probably need to set a reference to the correct library for the FileDialog.?

Do you compile your code?, as that would normally tell you it knows nothing about an object which means you need to add the correct reference.?

A quick google shows

https://stackoverflow.com/questions/9476268/filedialog-doesnt-work/9477733

and Microsoft Office [version number] Object Library

In references it might even show what is missing.?

HTH

 

[isladogs]

FYI, no additional references are needed to run the File Dialog.
It is part of the default Microsoft Access xx.0 Object Library automatically used with any new Access ACCDB database

 

[Snowflake68]

Hi. May I suggest creating a custom function in a Standard Module? For example, copy and paste the following into a Standard Module and save it as Module1.

Public Function BrowseFile() As String
'thedbguy@gmail.com
'12/02/2019

Dim fd As Object

Set fd = Application.FileDialog(3)

With fd
    If .Show Then
        BrowseFile = .SelectedItems(1)
    End If
End With

Set fd = Nothing

End Function

Then, you just need to modify your original code to do this.

strFilePath = BrowseFile()

Hope it helps...

thanks for this, works perfectly. Although I did have to add some further code to error trap when the user cancels out of the file Browse Filename dialog box. I just error trapper error code 2522 to exit out of the sub.

You are a star and I didnt even have to add any library references for this to work which is a much preferred method. Past history has caused issues with some on my users who are on different versions of Access. But thats another story

 

[theDBguy]

thanks for this, works perfectly. Although I did have to add some further code to error trap when the user cancels out of the file Browse Filename dialog box. I just error trapper error code 2522 to exit out of the sub.

You are a star and I didnt even have to add any library references for this to work which is a much preferred method. Past history has caused issues with some on my users who are on different versions of Access. But thats another story

Congratulations! Glad to hear you got it sorted out. We were all happy to assist. Good luck with your project.

 

[Snowflake68]

Change

Dim fileName As Variant

To

Dim FileName As Office.FileDialog

this did work after adding the library reference (as suggested by Gasman) but TheDBguy suggested a working alternative. thanks for you input though. This has taught me something new today.

 

[Gasman]

Colin,
I found that without the reference, msoFileDialogOpen failed with not defined.
Hardcoding a value worked.

Mentioned just in case it helps the o/p.

 

[Gasman]

You are a star and I didnt even have to add any library references for this to work

Apologies for giving you bum info.

 

[Snowflake68]

Congratulations! Glad to hear you got it sorted out. We were all happy to assist. Good luck with your project.

Just one last thing, is there a way to code in the VBA to default the browse dialog to a specific folder?

 

[theDBguy]

Just one last thing, is there a way to code in the VBA to default the browse dialog to a specific folder?

Yes, you can actually add a few more things. I just didn't want to over complicate it. Check out the following properties of the FileDialog object (there's more).

InitialFileName
Filters
Title
etc.

 

[Mark_]

Here is the code I use for one app that show a picture in a form related to the record.

Private Sub Btn_FindImage_Click()
Dim asFile As String
Dim afDialog As Office.FileDialog

   Set afDialog = Application.FileDialog(msoFileDialogFilePicker)
   With afDialog
       .Title = "Select Your Image"
       .InitialView = msoFileDialogViewLargeIcons
       .InitialFileName = "Your\Initial\Path\Here"
       If .Show = True Then
          asFile = Trim(afDialog.SelectedItems(1))
          Me.ImageFile = asFile
          Me.Image.Picture = asFile
       Else
           MsgBox "File selection cancelled by user"
       End If
   End With
End Sub

 

[Snowflake68]

Yes, you can actually add a few more things. I just didn't want to over complicate it. Check out the following properties of the FileDialog object (there's more).

InitialFileName
Filters
Title
etc.

thanks but I am not sure what you mean, where would I add these sorts of references. I just want to default the folder to point to for them to select the file from.

 

[theDBguy]

thanks but I am not sure what you mean, where would I add these sorts of references. I just want to default the folder to point to for them to select the file from.

Okay, what is the default folder you want to use? You can modify the code in this part:

...
With fd
[b]    .InitialFileName = "c:\foldername"[/b]
    If .Show Then
...

 

[Snowflake68]

Yes, you can actually add a few more things. I just didn't want to over complicate it. Check out the following properties of the FileDialog object (there's more).

InitialFileName
Filters
Title
etc.

You are just brilliant, thank you. works like a dream

 

[theDBguy]

You are just brilliant, thank you. works like a dream

You're welcome. Cheers!