The dialog function does not work (1 Viewer)

exaccess

Registered User.
Local time
Today, 15:38
Joined
Apr 21, 2013
Messages
287
Code:
Public Function GetCSVFile(strPath As String) As String
'----------------------------------------------------------------------------------
'Requires reference to Microsoft Office 12.0 Object Library.

   Dim fDialog As Office.FileDialog
   Dim varFile As Variant
   Dim Forname As String
   Forname = "ConFedFm"
   Dim FuncTemp As String
 
   'Set up the File Dialog.
   Set fDialog = Application.FileDialog(msoFileDialogFilePicker)
   With fDialog
      'Allow user to make multiple selections in dialog box.
      .AllowMultiSelect = False
      .InitialFileName = strPath
      'Set the title of the dialog box.
       .Title = "Please select a file"
      'Clear out the current filters, and add our own.
       .Filters.Clear
       .Filters.Add "csv files", "*.csv"
      
      'Show the dialog box. If the .Show method returns True, the
      'user picked at least one file. If the .Show method returns
      'False, the user clicked Cancel.
      
      If .Show = True Then
      Debug.Print "strPath = "; strPath; " .SelectedItems(1) = "; .SelectedItems(1)
          strPath = .SelectedItems(1)
      Else
         MsgBox "You clicked Cancel in the file dialog box."
      End If
   End With
   If strPath <> "" Then GetCSVFile = strPath
   If strPath = "" Then GetCSVFile = ""
End Function

The above code used to work. Suddenly it started giving 13 type missmatch at the line where strPath = .SElectedIteems(1)
 

JHB

Have been here a while
Local time
Today, 15:38
Joined
Jun 17, 2012
Messages
7,732
Code:
      Debug.Print "strPath = "[B][COLOR=Red];[/COLOR][/B] strPath[B][COLOR=red];[/COLOR][/B] " .SelectedItems(1) = "[B][COLOR=red];[/COLOR][/B] .SelectedItems(1)
The above code used to work. Suddenly it started giving 13 type missmatch at the line where strPath = .SElectedIteems(1)
Change the ; to &
Code:
      Debug.Print "strPath = "[B][COLOR=Red] &[/COLOR][/B] strPath [B][COLOR=red]&[/COLOR][/B] " .SelectedItems(1) = "[B][COLOR=red] &[/COLOR][/B] .SelectedItems(1)
 

isladogs

MVP / VIP
Local time
Today, 14:38
Joined
Jan 14, 2017
Messages
18,186
What does the debug line show for each item?

JHB - semicolons should also work as separators in debug line
 

exaccess

Registered User.
Local time
Today, 15:38
Joined
Apr 21, 2013
Messages
287
What does the debug line show for each item?

JHB - semicolons should also work as separators in debug line

Yes the problem is not the ;.

Debug statement gives

Code:
InFile = NOBAc_20171128_Mbrs_351_.csv InFulPath = D:\ARO\InputARO\NOBAc_20171128_Mbrs_351_.csv

and these values are correct
 

Galaxiom

Super Moderator
Staff member
Local time
Tomorrow, 01:38
Joined
Jan 20, 2009
Messages
12,849
Debug statement gives

Code:
[B]InFile =[/B] NOBAc_20171128_Mbrs_351_.csv [B]InFulPath =[/B] D:\ARO\InputARO\NOBAc_20171128_Mbrs_351_.csv

How so from :
Code:
Debug.Print [B]"strPath =[/B] "; strPath; " [B].SelectedItems(1) =[/B] "; .SelectedItems(1)
 

isladogs

MVP / VIP
Local time
Today, 14:38
Joined
Jan 14, 2017
Messages
18,186
Agreed that debug line isn't possible with your code.
However as I suspected, one of these is giving the full path and the other just the file name and type.
So whilst to you they are identical, Access thinks they are different.

Solution is to extract the file name from the full path or add the full path to the file name...or modify your code in some other way
 

Mark_

Longboard on the internet
Local time
Today, 07:38
Joined
Sep 12, 2017
Messages
2,111
strPath is a parameter. Normally you would not be using a passed variable like you are. A more normal approach would be as follows:

Code:
If .Show = True Then
      Debug.Print "strPath = "; strPath; " .SelectedItems(1) = "; .SelectedItems(1)
      GetCSVFile = .SelectedItems(1)
Else
      MsgBox "You clicked Cancel in the file dialog box."
      GetCSVFile = ""	
End If

If you really do need to check the contents prior to returning you would normally have declared a variable to check with. Also for naming conventions, the way you are using strPath you would be better changing it to strFileName as you are passing a file name instead of the path to the file.

Please let me know if this solves your issue.

Side note: When you post if you highlight the error it makes it much clearer where the error occurs.
 

exaccess

Registered User.
Local time
Today, 15:38
Joined
Apr 21, 2013
Messages
287
strPath is a parameter. Normally you would not be using a passed variable like you are. A more normal approach would be as follows:

Code:
If .Show = True Then
      Debug.Print "strPath = "; strPath; " .SelectedItems(1) = "; .SelectedItems(1)
      GetCSVFile = .SelectedItems(1)
Else
      MsgBox "You clicked Cancel in the file dialog box."
      GetCSVFile = ""	
End If

If you really do need to check the contents prior to returning you would normally have declared a variable to check with. Also for naming conventions, the way you are using strPath you would be better changing it to strFileName as you are passing a file name instead of the path to the file.

Please let me know if this solves your issue.

Side note: When you post if you highlight the error it makes it much clearer where the error occurs.

No it does not. It fails at line .show. I tried by defining a new variable but it failed again at the same place.
 

isladogs

MVP / VIP
Local time
Today, 14:38
Joined
Jan 14, 2017
Messages
18,186
I handle the cancel event using error handling
You should find cancel causes error 5 so try this alternative code.
Changes shown in BLUE/GREEN

Code:
===============Public Function GetCSVFile(strPath As String) As String
'----------------------------------------------------------------------------------
'Requires reference to Microsoft Office 12.0 Object Library.

   Dim fDialog As Office.FileDialog
[COLOR="SeaGreen"] 
 'the next 4 lines aren't used so could be removed
 ' Dim varFile As Variant
  ' Dim Forname As String
  ' Forname = "ConFedFm"
  ' Dim FuncTemp As String[/COLOR]

[COLOR="blue"]On Error GoTo Err_Handler[/COLOR]
 
   'Set up the File Dialog.
   Set fDialog = Application.FileDialog(msoFileDialogFilePicker)
   With fDialog
      'Do not allow user to make multiple selections in dialog box.
      .AllowMultiSelect = False
      .InitialFileName = strPath
      'Set the title of the dialog box.
       .Title = "Please select a file"
      'Clear out the current filters, and add our own.
       .Filters.Clear
       .Filters.Add "csv files", "*.csv"

     [COLOR="blue"]  .Show 'show dialog box[/COLOR]
      
     '   Debug.Print "strPath = "; strPath; " .SelectedItems(1) = "; .SelectedItems(1)
         strPath = .SelectedItems(1)
   End With

   [COLOR="SeaGreen"]'If strPath <> "" Then GetCSVFile = strPath
   'If strPath = "" Then GetCSVFile = ""[/COLOR]
  
  [COLOR="blue"] GetCSVFile = Nz(strPath,"")[/COLOR]

[COLOR="blue"]Exit_Handler:
    Exit Function

Err_Handler:
    'err 5 = user clicked cancel
    If err.Number = 5 Then
        MsgBox "You clicked Cancel in the file dialog box."
    Else	
        MsgBox "Error " & err.Number & " in GetCSVFile procedure : " & vbNewLine & err.description
    End If
    Resume Exit_Handler[/COLOR]
    
End Function

Also did you mean Formname rather than Forname below?
Code:
Dim For[COLOR="red"]m[/COLOR]name As String
   For[COLOR="red"]m[/COLOR]name = "ConFedFm"

Having said that, you don't use it anyway in this code so it could be deleted

HTH
 

moke123

AWF VIP
Local time
Today, 10:38
Joined
Jan 11, 2013
Messages
3,849
I think you need to re-post the code you are using.

as Galaxiom points out

Code:
InFile = NOBAc_20171128_Mbrs_351_.csv InFulPath = D:\ARO\InputARO\NOBAc_20171128_Mbrs_351_.csv

There is no "InFile" or "InFulPath" in your code
 

Galaxiom

Super Moderator
Staff member
Local time
Tomorrow, 01:38
Joined
Jan 20, 2009
Messages
12,849
I think you need to re-post the code you are using.

There is no "InFile" or "InFulPath" in your code

Yes. Perhaps the function posted is not the one that is actually running.

It is possible for the same function name to be used in two different object modules. Which one you get depends on where it is called from.
 

Galaxiom

Super Moderator
Staff member
Local time
Tomorrow, 01:38
Joined
Jan 20, 2009
Messages
12,849
strPath is a parameter. Normally you would not be using a passed variable like you are.

Code:
Public Function GetCSVFile(strPath As String) As String

          strPath = .SelectedItems(1)

Yes. Moreover, since the argument is not declared ByVal the parameter will be passed ByRef meaning that reassignment of the value will be reflected to any variable used as a parameter in the call.
 

Mark_

Longboard on the internet
Local time
Today, 07:38
Joined
Sep 12, 2017
Messages
2,111
No it does not. It fails at line .show. I tried by defining a new variable but it failed again at the same place.

If it is failing on .Show, how would you ever be able to get the error you posted in post #1? You are referencing a spot AFTER you were using .show in your own code?

Or did you not pick up that you should be putting the sample I put up at the same spot in your code?
 

exaccess

Registered User.
Local time
Today, 15:38
Joined
Apr 21, 2013
Messages
287
Here is the latest version of the code where I respected all your recommendations.

Code:
'----------------------------------------------------------------------------------
Public Function GetCSVFile(strPath As String) As String
'----------------------------------------------------------------------------------
'Requires reference to Microsoft Office 12.0 Object Library.
On Error GoTo Err_Handler
   Dim fDialog As Office.FileDialog
   
   'Set up the File Dialog.
   Set fDialog = Application.FileDialog(msoFileDialogFilePicker)
   With fDialog
      'Allow user to make multiple selections in dialog box.
      .AllowMultiSelect = False
      .InitialFileName = strPath                         'strPath
      'Set the title of the dialog box.
      .Title = "Please select a file"
      'Clear out the current filters, and add our own.
      .Filters.Clear
      .Filters.Add "csv files", "*.csv"
      
      .Show  'show dialog box
      Debug.Print "strPath = "; strPath; "   .SelectedItems(1) = "; .SelectedItems(1)
      strPath = .SelectedItems(1)
   End With
  
     GetCSVFile = Nz(strPath, "")
Exit_Handler:
    Exit Function

Err_Handler:
    'err 5 = user clicked cancel
    If Err.Number = 5 Then
        MsgBox "You clicked Cancel in the file dialog box."
    Else
        MsgBox "Error " & Err.Number & " in GetCSVFile procedure : " & vbNewLine & Err.Description
    End If
    Resume Exit_Handler
 
End Function

Here the removed items as you say were not needed. The function is called by another sub in the module. The strPath points to the folder opened to pick up the correct csv file. The purpose of the function is to give the calling sub the whole path + file name.
in this case the input is strPath ="D:\ARO\AROInput\ and output is GetCSV File - strpath+ FileNAme selected.
 

isladogs

MVP / VIP
Local time
Today, 14:38
Joined
Jan 14, 2017
Messages
18,186
Glad its working

As you almost exactly used my code, thought I'd just mention that the comment line is misleading. I wrote:

'Do not allow user to make multiple selections in dialog box.

and of course the Debug line can be disabled
 

exaccess

Registered User.
Local time
Today, 15:38
Joined
Apr 21, 2013
Messages
287
Glad its working

As you almost exactly used my code, thought I'd just mention that the comment line is misleading. I wrote:



and of course the Debug line can be disabled

I think my message somewhat misleading. It is still NOT working.
 

Mark_

Longboard on the internet
Local time
Today, 07:38
Joined
Sep 12, 2017
Messages
2,111
I think my message somewhat misleading. It is still NOT working.

If the code is still not working, where is it not working and what error are you getting?
 

isladogs

MVP / VIP
Local time
Today, 14:38
Joined
Jan 14, 2017
Messages
18,186
It did sound like it was working though I was surprised.
As Mark & Galaxiom both pointed out using strPath like this is confusing

For info here is some similar code I use to select a folder:

Code:
Private Sub cmdBrowse2_Click()

On Error GoTo Err_cmdBrowse2_Click

    Set F = Application.FileDialog(msoFileDialogFilePicker)
    F.title = "Locate the common files folder and click on 'Open'"
    
    ' Clear out the current filters, and add our own.
     ' F.AllowMultiSelect = False 'can be omitted if false
      F.Filters.Clear
      F.Filters.Add "", "*.*"
      
    ' Set the start folder
    'if a value has already been found this opens the browse window to that folder - otherwise to the root c:\ folder
      If Me.txtCommonFilesFolder <> "" Then
            F.InitialFileName = Me.txtCommonFilesFolder
      Else
            F.InitialFileName = "C:\"
      End If
    
    ' Call the Open dialog routine.
      F.Show
    
    ' Return the path and file name.
      strPath = F.SelectedItems(1)
      
    ' Update setting - not relevant to you
     'This is just what I do with the info in my example
      Me.txtCommonFilesFolder = GetPathWithoutFilename(strPath)
      txtCommonFilesFolder_AfterUpdate


Exit_cmdBrowse2_Click:
    Exit Sub

Err_cmdBrowse2_Click:
    'err 5 = user clicked cancel
    If Err.Number <> 5 Then
        MsgBox "Error " & Err.Number & " in cmdBrowse2_Click procedure : " & vbNewLine & Err.description
    End If
    Resume Exit_cmdBrowse2_Click
    
End Sub
 

exaccess

Registered User.
Local time
Today, 15:38
Joined
Apr 21, 2013
Messages
287
Code:
Sub Main11()
    Dim ForName As String
    Dim strPath As String
    Browser ForName, strPath
End Sub

Code:
Sub Browser(ForName, strPath As String)
On Error GoTo Err_Function
    Dim NSlash As String, InCommaCSV As String, InSEmiColonCSV As String
    Dim TNO As Integer, TACT As Integer, TFile As String, TLINE As String, BUF As String
    Dim strFolder As String
    Dim strSelect As String
    strFolder = "D:\ARO\AROInput\"
    GetCSVFile strFolder, strSelect              'Full path+Infile
    InFulPath = strSelect
    NSlash = InStrRev(InFulPath, "\", -1)

    InFile = Mid(InFulPath, NSlash + 1)
    Dim I As Integer, J As Integer
    Debug.Print "InFile = "; InFile; "InFulPath = "; InFulPath

'   InFulPath is the name of input filepath+name

'   InFile is input file
'   Verify InFile
    Forms(ForName).CSVs = InFulPath   'Display file name with path 1
..............
Code:
Function GetCSVFile(strFolder As String, strSelect)

On Error GoTo GetCSVFile
    Dim F As FileDialog
    Set F = Application.FileDialog(msoFileDialogFilePicker)


    F.Title = "Locate the common files folder and click on 'Open'"
    
    ' Clear out the current filters, and add our own.
     ' F.AllowMultiSelect = False 'can be omitted if false
      F.Filters.Clear
      F.Filters.Add "Please specify file to input", "*.csv"
      
    ' Set the start folder
    'if a value has already been found this opens the browse window to that folder - otherwise to the root c:\ folder
      F.InitialFileName = strFolder
      
      
    
    ' Call the Open dialog routine.
      F.Show
    
    ' Return the path and file name.
      strSelect = F.SelectedItems(1)
      MsgBox "strSelect= " & strSelect
    ' Update setting - not relevant to you
     'This is just what I do with the info in my example
     ' Me.txtCommonFilesFolder = GetPathWithoutFilename(strPath)
     ' txtCommonFilesFolder_AfterUpdate


Exit_GetCSVFile:
    Exit Function

GetCSVFile:
    'err 5 = user clicked cancel
    If Err.Number <> 5 Then
        MsgBox "Error " & Err.Number & " in cmdBrowse2_Click procedure : " & vbNewLine & Err.Description
    End If
    Resume Exit_GetCSVFile
    
End Function

Above is the whole story. I tested the GetCSVFile function once by using a dummy sub called Main11 and then as a part of the application. I am publishing the complete result so that people having similar problems can use it The trick was to put the output of the file as a parameter and not try to assign the name of the function as an output parameter. Riddle and others thank you all. Also I have used your code Thanks for all.
 

Users who are viewing this thread

Top Bottom