Capturing Path from a FileDialog Open Procedure

[mlr0911]

Hello All,

I am trying to open a file from a dialog box and then count the records in that file. I am able to accomplish this by actually hard coding the path, however, I need to be able to search for the file, then have the code look at the file and count the about of records that are in it.

I am trying to use this code to look for the file and then process it, however, it isn't working. Can anyone help?

I need to have this code:

Open "C:\Desktop\File 1.txt" For Input As # 1

Actually do this:

Application.FileDialog(msoFileDialogOpen).show For Input As # 1

Thanks in advance for your help.

 

[gemma-the-husky]

not fully sure of the syntax without checking, but
something like

dim filename as string

and then

filename = Application.FileDialog(msoFileDialogOpen)
open filename for Input As # 1

 

[mlr0911]

Thanks for the reply gemma-the-husky...

I have tried what you have suggested, however, I am getting a Run-Time error '53 stating: File Not Found.

Any thoughts on what I am doing wrong?


I am thinking that I may have to capture the path with code, but I am not sure.......


Here is my code (with your suggestion):

Dim InputData
Dim Field1 As String
Dim StmtCount As Double
Dim Msg, Title, Response
Dim filename As String
StmtCount = 0

filename = Application.FileDialog(msoFileDialogOpen).Show

Open filename For Input As #1


'open For Input As #1 ' Open file for input.
Do While Not EOF(1) ' Check for end of file.
InputData = ""
Field1 = ""
Line Input #1, InputData ' Read line of data.
Field1 = Mid([InputData], 2, 7)
If [Field1] = "Test" Then StmtCount = StmtCount + 1
Loop
Close #1 ' Close file.
Msg = "Number of Statements = " & [StmtCount] ' Define message.
Title = "Count Statement Results" ' Define title.
Response = MsgBox(Msg, , Title)

 

[gemma-the-husky]

after

filename = Application.FileDialog(msoFileDialogOpen).Show

put a msgbox(filename) - you need to be sure you have set the correct path with the filedialog syntax.

i presume the open is failing, as the filename you have doesn't exist.

 

[mlr0911]

geeme-the-husky, thanks again for your help. I still can't seem to get this thing to work....lol.

This is the code that I added to my project.

Data = Application.FileDialog(msoFileDialogOpen).Show
MsgBox (Data)

Open Data For Input As #1

I am able to have the dialog box show and I can select a file, but in the msgbox, I am getting a -1. What else would you suggest?

Thanks again for your help.

 

[gemma-the-husky]

-1 is true

hence your call to the filedialog is simply returning a value of true. I am sure .show is the wrong method or property for what you are trying to achieve, which is where your error is coming from. but i don't know the msofieldialog, so i don't know which property you do need.

 

[mlr0911]

Gemma-the-husky

I have figured out an alternative. I placed a text box (hidden) on my form and used the .selectedItems.count method and am able to achieve the desired results. Here is the final code snippet........again, thanks for your help, you moved me in the right direction with the msgbox code.

Dim InputData
Dim Field1 As String
Dim StmtCount As Double
Dim Msg, Title, Response
Dim dlgOpen As FileDialog
Dim intsub As Integer
StmtCount = 0


Set dlgOpen = Application.FileDialog(msoFileDialogOpen)

dlgOpen.Show

For intsub = 1 To dlgOpen.SelectedItems.Count
'display the selected filename
Me.Text4 = dlgOpen.SelectedItems(intsub)
Next

Open Me.Text4 For Input As #1


'open For Input As #1 ' Open file for input.
Do While Not EOF(1) ' Check for end of file.
InputData = ""
Field1 = ""
Line Input #1, InputData ' Read line of data.
Field1 = Mid([InputData], 2, 7)
If [Field1] = "Test" Then StmtCount = StmtCount + 1
Loop
Close #1 ' Close file.
Msg = "Number of Statements = " & [StmtCount] ' Define message.
Title = "Count Statement Results" ' Define title.
Response = MsgBox(Msg, , Title)

 

[PeterF]

A code snippet from the access help for FileDialogFilters should point you in the right direction.

Sub Main()

'Declare a variable as a FileDialog object.
Dim fd As FileDialog

'Create a FileDialog object as a File Picker dialog box.
Set fd = Application.FileDialog(msoFileDialogFilePicker)

'Declare a variable to contain the path
'of each selected item. Even though the path is a String,
'the variable must be a Variant because For Each...Next
'routines only work with Variants and Objects.
Dim vrtSelectedItem As Variant

'Use a With...End With block to reference the FileDialog object.
With fd

'Change the contents of the Files of Type list.
'Empty the list by clearing the FileDialogFilters collection.
.Filters.Clear

'Add a filter that includes all files.
.Filters.Add "All files", "*.*"

'Add a filter that includes GIF and JPEG images and make it the first item in the list.
.Filters.Add "Images", "*.gif; *.jpg; *.jpeg", 1

'Use the Show method to display the File Picker dialog box and return the user's action.
'The user pressed the action button.
If .Show = -1 Then

'Step through each String in the FileDialogSelectedItems collection.
For Each vrtSelectedItem In .SelectedItems

'vrtSelectedItem is a String that contains the path of each selected item.
'You can use any file I/O functions that you want to work with this path.
'This example simply displays the path in a message box.
MsgBox "Path name: " & vrtSelectedItem

Next vrtSelectedItem
'The user pressed Cancel.
Else
End If
End With

'Set the object variable to Nothing.
Set fd = Nothing

End Sub