Loop through files in a folder based on date

[creyn]

I am trying to loop through Files in Folder based upon date that is entered in the Form. Below is the code I am using. I am having an issue with getting the files to be visible. Anyone help is greatly appreciated.

With fDialog
.Title = ""
.AllowMultiSelect = False
Intialfilename = ""

If IsNull(Me.Text6.Value) Then
MsgBox "Date cannot be empty. Please select date in order to proceed with this process."
ElseIf .Show = True Then
varfile = .SelectedItems(1) & "*\data*\ " & Me.Text6.Value
strFolder = varfile
Else
rtn = SysCmd(4, "Action aborted by user")
Exit Sub
End If

 

[Micron]

Micron, he's trying NOT to use a file picker.

I know, but he wants to see files. AFAIK, the options are limited with the mso picker. It's a common complaint that one cannot see the files to ensure the chosen folder is correct. That's why coders will often start the dialog off at a predetermined place, i.e. where the files needed have been allocated by business decisions. If the db users can live with the mandate that these files must be kept in a particular folder or root, that can work. What I did was write the last path chosen by a user against their EmplID, minus everything from the last slash (/) so that the picker started in that folder.

 

[creyn]

Yes, I need to the Folder Picker because of the sub folders within the main the Folder

 

[Micron]

Yes, I need to the Folder Picker because of the sub folders within the main the Folder

The code solution I posted does allow you to pick the folder only, while exposing the files.

 

[creyn]

This is the code I am using in its entirety. I will incorporate the code you gave into it.

Option Compare Database

Public Sub Command0_Click()
Dim MyFolder As String
Dim MyFile As String
Dim MyFolder2 As String
Dim DQ As String
Dim MyData As String
Dim myQueryName As String
Dim myExportFileName As String
Dim fso As Object
Set fso = VBA.CreateObject("Scripting.FileSystemObject")
Set fDialog = Application.FileDialog(msoFileDialogFolderPicker)
With fDialog
.Title = ""
.AllowMultiSelect = False
Intialfilename = ""

If IsNull(Me.Text6.Value) Then
MsgBox "Date cannot be empty. Please select date in order to proceed with this process."
ElseIf .Show = True Then
varfile = .SelectedItems(1) & "*\data*\* " & Me.Text6.Value
strFolder = varfile
Else
rtn = SysCmd(4, "Action aborted by user")
Exit Sub
End If
DoCmd.SetWarnings False
MyFolder = varfile
MyFile = Dir(MyFolder)
Call fso.CopyFile(MyFolder, MyFile & ".txt")
MyFile = Dir(MyFolder & ".txt")
DoCmd.SetWarnings False
DoCmd.TransferText acLinkDelim, "Link Specification", "Import_Data", MyFile, True
DoCmd.RunSQL "INSERT INTO Import_Data2 ( Field1) SELECT Import_Data.Field1 FROM Import_Data;"
DoCmd.RunSQL "INSERT INTO SpecialCharacter_Errors ( Field1) SELECT Special_Characters.Field1 FROM Special_Characters;"
DoCmd.RunSQL "UPDATE SpecialCharacter_Errors SET FileName = '" & MyFile & "', [Date] = '" & Date & "' WHERE Field1 IS NOT NULL;"
DoCmd.OpenQuery "Qry_Import_Data"
DoCmd.Close acQuery, "Qry_Import_Data"
DoCmd.OpenQuery "Column_Export"
DoCmd.Close acQuery, "Column_Export"
DoCmd.TransferText acExportDelim, "Export Specification", "Column_Export", MyFile, False
DoCmd.SetWarnings False
MsgBox "Process Completed"
Set fso = Nothing
DoCmd.SetWarnings False
DoCmd.RunSQL "Delete * from Import_Data2"
DoCmd.SetWarnings False
DoCmd.DeleteObject acTable, "Import_Data"
End With
End Sub

 

[Micron]

Will look it over later. Out now. BUT you should have Option Explicit at the top of every module.

 

[creyn]

Thanks

 

[Micron]

Some pointers, if you please. First, it makes your code more readable if you use code tags and indentation. Plus, forums often insert a space after a set number of characters, so you could end up with words split and not notice. Then we are commenting on something that doesn't really exist. Not sure if this forum does.

- you don't seem to be using any of these variables, so why declare them?
Dim MyFolder2 As String
Dim DQ As String
Dim MyData As String
Dim myQueryName As String
Dim myExportFileName As String
- you only need to turn off warnings once in a procedure and they remain off
- you need to turn them back on! All other procedures that run afterwards are affected. Not sure if this is reset when the affected db closes and reopens.
- there is no error handler in your procedure. Should it err out, warnings remain turned off even if you have written to turn them on IF this is not written to happen after dealing with an error.
- it has been said by Access gurus much smarter than I that if you do not have Option Explicit in every module, you deserve what you get. I agree. The default setting is to turn off "Require Variable Declaration". I always turn it on.
- rtn and varFile are undeclared variables
- the date is a text value?? ('" & Date & "')
- opening, then immediately closing a query can produce inconsistent results. IIRC, the expression service can run asynchronously with Access JET. This means code can continue before other things finish, in some cases. If you can, I would open a query, then close it at the end of the whole process. If not, a pause might be a good idea, even if for only one or two seconds.
- you should destroy all objects that have been SET (fdialog)
- I would not write anything that did not relate to an object or property inside a With block (like running queries). You're basically saying, With the dialog, set or do this and this to it. The rest is not related to the dialog, so it really shouldn't be in there. It's just better form and will make your code easier to follow; not just for others, but for you too when you come back to this months from now. Besides, you'll appear more astute as a code writer!

How I would write a typical error handler, where this follows my Dim statements: On Error GoTo errHandler
At the end of the procedure stuff that performs the task, a line lable:

exitHere:
Now clean up, like -
turn on warnings;
close recordsets, queries;
destroy objects (SET to Nothing), then
Exit Sub (or Function, else code execution will continue to the error handler). Then,

errHandler:
msgbox "Error " & Err.Number & ": " & Err.Description (result like Error 91: Invalid use of Null)
Resume exitHere (this will direct to the exitHere line, to clean up. If there is no error, cleanup still occurs after the exitHere line label, and the Exit Sub line terminates code).