Catch the name that is chosen using dialog box

[DeThomaso]

Hi
This is my first question on this forum. I am also rather new at VBA.

I want to catch the name of the file that is chosen by the dialog popup window using this line of code:
"RetVal = Application.Dialogs(xlDialogOpen).Show("*.txt")"
As I have interpreted it filter the directory so only teextfiles are seen.
RetVal variable returns a Boolean, which is just used to see if the user is actually picking a file or just click cancel.
The original code in itself is not written by me, I just want to update it to display the particular file name that is chosen in a specific cell in the Excell workbook i.e. A1.
For the moment I just want to catch the name of the file that the user is clicking on when message box comes up.
Is there an extension to the code line above or do I need to rewritten the whole code to first catch the filename and then do the rest of the code?

Tanks ahead

 

[DeThomaso]

The code in the beginning looks like this:

Sub Import_ReferenceFile()
 Dim DestBook As Workbook, SourceBook As Workbook
 Dim DestCell As Range
 Dim RetVal As Boolean
 Dim PickFilename As String


 ' Turn off screen updating.
 Application.ScreenUpdating = False
 With Worksheets("Jura DFLASH_1pA4_Ref").Select
 Range("A3:A65536").ClearContents
 End With

 ' Set object variables for the active book and active cell.
 Set DestBook = ActiveWorkbook
 'Set DestCell = ActiveCell
 Set DestCell = Range("A4", "A300")

 'MsgBox "Please click OK and navigate to your Text File"
 ' Show the Open dialog box.
 RetVal = Application.Dialogs(xlDialogOpen).Show("*.txt")
 ' If Retval is false (Open dialog canceled), exit the procedure.
 If RetVal = False Then Exit Sub[CODE=vba]

[/CODE]

 

[DeThomaso]

I added the variable PickFilename to try to catch the name but I cannot work it out...

/Thanks

 

[Domenic]

Try the GetOpenFilename method of the Application object instead . . .

    Dim vFileName As Variant
    vFileName = Application.GetOpenFilename(FileFilter:="CSV Files (*.csv), *.csv", Title:="Select a File")
   
    If vFileName = False Then Exit Sub

    MsgBox "User selected " & vFileName, vbInformation

Hope this helps!

 

[DeThomaso]

Great i'll try that.

 

[DeThomaso]

Hi again Domenic, if still availble...
What is the purpose of the last line in your code as below?

MsgBox "User selected " & vFileName, vbInformation

 

[DeThomaso]

Try the GetOpenFilename method of the Application object instead . . .

    Dim vFileName As Variant
    vFileName = Application.GetOpenFilename(FileFilter:="CSV Files (*.csv), *.csv", Title:="Select a File")
  
    If vFileName = False Then Exit Sub

    MsgBox "User selected " & vFileName, vbInformation

Hope this helps!

This works but I get problems with Exit Sub statement if I choose a file the code gets error and want me to debug the code.
The strange part it has worked and now it does not. I have seen this before as if there is a timing issue.
When I press cancel it works fine as it should but when I choose a file it stops.
I was thinking if the file string containing charactors that is not allowed or something.
Here is the string when I have chosen a file:

C:\Temp\RMA0199-1_PST_Jura_Dump_BFLASH1.S

Is there anything wrong with the charactors in this string causing the Exit Sub to stop?

 

[DeThomaso]

Hi again Domenic, if still availble...
What is the purpose of the last line in your code as below?

MsgBox "User selected " & vFileName, vbInformation

I know now what it is..stupid me. A message popup window showing the chosen file info...
But it was actually the Exit Sub I had problem with so this confused me a little bit.

 

[DeThomaso]

Case closed All Good.
variable PickFilename or in your case vFileName needed to declared as Variant and not String.

 

[Domenic]

That's great, glad you were able to sort it out.

Cheers!