Import CSV with Windows File Prompt (1 Viewer)

Snowflake68

Registered User.
Local time
Today, 13:30
Joined
May 28, 2014
Messages
452
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
Code:
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.

Code:
        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
Code:
        Set fileName = Application.FileDialog(msoFileDialogOpen)

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

Mark_

Longboard on the internet
Local time
Today, 06:30
Joined
Sep 12, 2017
Messages
2,111
Change
Code:
Dim fileName As Variant
To
Code:
Dim FileName As Office.FileDialog
 

theDBguy

I’m here to help
Staff member
Local time
Today, 06:30
Joined
Oct 29, 2018
Messages
21,357
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.
Code:
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.
Code:
strFilePath = BrowseFile()
Hope it helps...
 

Gasman

Enthusiastic Amateur
Local time
Today, 13:30
Joined
Sep 21, 2011
Messages
14,041
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

MVP / VIP
Local time
Today, 13:30
Joined
Jan 14, 2017
Messages
18,186
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

Registered User.
Local time
Today, 13:30
Joined
May 28, 2014
Messages
452
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.
Code:
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.
Code:
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

I’m here to help
Staff member
Local time
Today, 06:30
Joined
Oct 29, 2018
Messages
21,357
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

Registered User.
Local time
Today, 13:30
Joined
May 28, 2014
Messages
452
Change
Code:
Dim fileName As Variant
To
Code:
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

Enthusiastic Amateur
Local time
Today, 13:30
Joined
Sep 21, 2011
Messages
14,041
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.
 

Snowflake68

Registered User.
Local time
Today, 13:30
Joined
May 28, 2014
Messages
452
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

I’m here to help
Staff member
Local time
Today, 06:30
Joined
Oct 29, 2018
Messages
21,357
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_

Longboard on the internet
Local time
Today, 06:30
Joined
Sep 12, 2017
Messages
2,111
Here is the code I use for one app that show a picture in a form related to the record.
Code:
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

Registered User.
Local time
Today, 13:30
Joined
May 28, 2014
Messages
452
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

I’m here to help
Staff member
Local time
Today, 06:30
Joined
Oct 29, 2018
Messages
21,357
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:
Code:
...
With fd
[b]    .InitialFileName = "c:\foldername"[/b]
    If .Show Then
...
 

Snowflake68

Registered User.
Local time
Today, 13:30
Joined
May 28, 2014
Messages
452
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
 

Users who are viewing this thread

Top Bottom