Good afternoon folks,
I am using MS Access 2000 and I would like to learn/find out how to write some code that will do the following:
I have two drive locations as follows:
\\prdfs01\prodreg (B: Drive) and \\qwima400\prodreg (E: Drive)
The file structure under both directories is a number of folders all ending in “.fof” i.e. creda.fof, a12345.fof etc.
In each of the .fof folders a text file will reside at some point and they all have a file extension of “.txt”, there will never be any other file type under these folders.
What I would like to do is write code that checks the \\prdfs01\prodreg directory folders for any text files and if any text files exist then to check if a text file in the corresponding .fof folder on the \\qwima400\prodreg drive exists and if it doesn’t then to move that text file from that folder under that directory to the corresponding .fof folder under the directory \\qwima400\prodreg . If a text file does exist in the corresponding .fof folder on the \\qwima400\prodreg drive then stop the process and report back via a msgbox that the “ScanData(TRScanC3) program has failed”
I have the following code that with the help of an advanced MS Access expert was written to check if text files existed in the directory \\prdfs01\prodreg (B: Drive) and then populated a table with details about each text file as shown below:
There clearly is a part of this code that I will not need for the purposes of what I would like to achieve above, that part being the population of a table in the database.
Any assistance would be most appreciated.
Regards.
John
I am using MS Access 2000 and I would like to learn/find out how to write some code that will do the following:
I have two drive locations as follows:
\\prdfs01\prodreg (B: Drive) and \\qwima400\prodreg (E: Drive)
The file structure under both directories is a number of folders all ending in “.fof” i.e. creda.fof, a12345.fof etc.
In each of the .fof folders a text file will reside at some point and they all have a file extension of “.txt”, there will never be any other file type under these folders.
What I would like to do is write code that checks the \\prdfs01\prodreg directory folders for any text files and if any text files exist then to check if a text file in the corresponding .fof folder on the \\qwima400\prodreg drive exists and if it doesn’t then to move that text file from that folder under that directory to the corresponding .fof folder under the directory \\qwima400\prodreg . If a text file does exist in the corresponding .fof folder on the \\qwima400\prodreg drive then stop the process and report back via a msgbox that the “ScanData(TRScanC3) program has failed”
I have the following code that with the help of an advanced MS Access expert was written to check if text files existed in the directory \\prdfs01\prodreg (B: Drive) and then populated a table with details about each text file as shown below:
There clearly is a part of this code that I will not need for the purposes of what I would like to achieve above, that part being the population of a table in the database.
Any assistance would be most appreciated.
Regards.
John
Code:
[FONT=Calibri][SIZE=2][COLOR=blue]Function [/COLOR]TextFilesExist()[/SIZE][/FONT]
[FONT=Calibri][SIZE=2] DoCmd.Echo [COLOR=blue]False[/COLOR], "Running Program - Checking B Drive For Text Files" [COLOR=#00b050]'Indicates in the progress bar the program is running[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=2] DoCmd.Hourglass [COLOR=blue]True[/COLOR] [COLOR=#00b050]'Turn on the Hourglass[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=2] DoCmd.SetWarnings [COLOR=blue]False[/COLOR] [COLOR=#00b050]'Turn off warnings[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=2] [/SIZE][/FONT]
[FONT=Calibri][SIZE=2] [COLOR=blue]Dim [/COLOR]filenum [COLOR=blue]As [/COLOR]Integer [COLOR=#00b050]'Declare the filenum variable[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=2] [COLOR=blue]Dim [/COLOR]Count [COLOR=blue]As Long[/COLOR] [COLOR=#00b050]'Declare the count variable as Long[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=2] [COLOR=blue]Dim [/COLOR]tmp [COLOR=blue]As String[/COLOR] [COLOR=#00b050]'Declare the tmp variable as string[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=2] [COLOR=blue]Dim [/COLOR]FS [COLOR=blue]As [/COLOR]FileSystemObject [COLOR=#00b050]'Declares the File System Object[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=2] [COLOR=blue]Dim [/COLOR]Folder [COLOR=blue]As [/COLOR]Folder [COLOR=#00b050]'Declares the Folder Object[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=2] [COLOR=blue]Dim [/COLOR]subFolder [COLOR=blue]As [/COLOR]Folder [COLOR=#00b050]'Declare the SubFolder Object[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=2] [COLOR=blue]Dim [/COLOR]file [COLOR=blue]As [/COLOR]file [COLOR=#00b050]'Declares the File Object[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=2] [COLOR=blue]Dim [/COLOR]TextFilePath [COLOR=#00b050]'Declares the TextFilePath variable[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=2] [COLOR=blue]Dim [/COLOR]NameOfFile [COLOR=#00b050]'Declare the NameOfFile variable[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=2] [COLOR=blue]Dim [/COLOR]FileNameWithExt [COLOR=#00b050]'Declare the FileNameWithExt variable[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=2] [COLOR=blue]Dim [/COLOR]strTemp [COLOR=blue]As String[/COLOR] [COLOR=#00b050]'Declare the strTemp variable[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=2] [COLOR=blue]Dim [/COLOR]FileLoc [COLOR=blue]As String[/COLOR] [COLOR=#00b050]'Declare the FileLoc variable[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=2] [COLOR=blue]Dim [/COLOR]rst [COLOR=blue]As [/COLOR]DAO.Recordset [COLOR=#00b050]'Declare rst as DAO Recordset[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=2] [COLOR=blue]Dim [/COLOR]DB [COLOR=blue]As [/COLOR]Database [COLOR=#00b050]'Declare DB as the Database[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=2] [COLOR=blue]Dim [/COLOR]sUser [COLOR=blue]As String[/COLOR] [COLOR=#00b050]'Declare the sUser as String[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=2] [/SIZE][/FONT]
[FONT=Calibri][SIZE=2] sUser = Environ("UserName") [COLOR=#00b050]'Get the Logged on Users name[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=2] [/SIZE][/FONT]
[FONT=Calibri][SIZE=2] [COLOR=blue]Const [/COLOR]ForReading = 1 [COLOR=#00b050]'Declare the FoReading Constant[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=2] CurDate = Date[/SIZE][/FONT]
[FONT=Calibri][SIZE=2] [/SIZE][/FONT]
[FONT=Calibri][SIZE=2] [COLOR=blue]Set [/COLOR]FS = CreateObject("Scripting.FileSystemObject") [COLOR=#00b050]'Set the File System Object[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=2] [COLOR=blue]Set [/COLOR]DB = CurrentDb[/SIZE][/FONT]
[FONT=Calibri][SIZE=2] [/SIZE][/FONT]
[FONT=Calibri][SIZE=2] TextFilePath = "B:\" [COLOR=#00b050]'Set the text file path here [the location where text files are being stored][/COLOR][/SIZE][/FONT]
[COLOR=#00b050][FONT=Calibri][SIZE=2] [/SIZE][/FONT][/COLOR]
[FONT=Calibri][SIZE=2] [COLOR=blue]Set [/COLOR]Folder = FS.GetFolder(TextFilePath) [COLOR=#00b050]'Open the text file folders[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=2] [/SIZE][/FONT]
[FONT=Calibri][SIZE=2] [COLOR=blue]For Each [/COLOR]subFolder [COLOR=blue]In [/COLOR]Folder.SubFolders [COLOR=#00b050]'Loops through the Folders looking for SubFolders[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=2] [COLOR=blue]For Each [/COLOR]file [COLOR=blue]In [/COLOR]subFolder.Files [COLOR=#00b050]'Loops through Subfolders looking for Files[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=2] NameOfFile = GetAttr("File.Name") [COLOR=#00b050]'Get the text file name[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=2] [COLOR=blue]If [/COLOR]Right(file.Name, 4) = ".txt" [COLOR=blue]Then[/COLOR] [COLOR=#00b050]'If the file found has a ".txt" extension then[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=2] FileNameWithExt = Mid$(file.Name, InStrRev(file.Name, "\") + 1) [COLOR=#00b050]'Assign the File Name with its extension type to the FileNameWithExt variable for example aeg.fof[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=2] strTemp = Mid$(file.Name, InStrRev(file.Name, "\") + 1) [COLOR=#00b050]'Assign the text file name to the strTemp variable for example aeg.fof[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=2] NameOfFile = Left$(strTemp, InStrRev(strTemp, ".") - 1) [COLOR=#00b050]'Assign the name of the text file without the extension type to the NameOfFile variable for example aeg[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=2] subFilePath = Left$(subFolder, InStrRev(subFolder, "\")) [COLOR=#00b050]'Assign the Directory location of the sub file path to the SubFilePath variable in this case B:\[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=2] SubFolderName = Mid$(subFolder, InStr(3, subFolder, "\")) [COLOR=#00b050]'Assign the name of the sub folder to the SubFolderName variable for example \aeg.fof[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=2] FileLoc = subFolder & "\" & file.Name [COLOR=#00b050]'Assign the subFolder and File.Name to the FileLoc variable[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=2] [/SIZE][/FONT]
[FONT=Calibri][SIZE=2] filenum = FreeFile [COLOR=#00b050]'Get unused file number[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=2] [COLOR=blue]Open [/COLOR]FileLoc For [COLOR=blue]Input As [/COLOR]filenum [COLOR=#00b050]'Open the text file for input as the file number[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=2] [COLOR=blue]Do While Not [/COLOR]EOF(filenum) [COLOR=#00b050]'Loop while not the End of File[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=2] [COLOR=blue]Line Input [/COLOR]#filenum, tmp$[/SIZE][/FONT]
[FONT=Calibri][SIZE=2] Count = Count + 1 [COLOR=#00b050]'Count the lines and increment[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=2] [COLOR=blue]Loop[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=2] [COLOR=blue]Close [/COLOR]#filenum [COLOR=#00b050]'Close the text file[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=2] [COLOR=blue]End If[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=2] LngCount = LngCount + 1[/SIZE][/FONT]
[FONT=Calibri][SIZE=2] [COLOR=blue]Next[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=2] [COLOR=blue]Next[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=2] [/SIZE][/FONT]
[FONT=Calibri][SIZE=2] [COLOR=blue]If [/COLOR]LngCount > 0 [COLOR=blue]Then[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=2] [/SIZE][/FONT]
[FONT=Calibri][SIZE=2] MsgBox CurDate & vbCrLf & _[/SIZE][/FONT]
[FONT=Calibri][SIZE=2] vbCrLf & "The NewProdRegeFlow program has failed to run at 06:00 Hours" & _[/SIZE][/FONT]
[FONT=Calibri][SIZE=2] vbCrLf & "There are a Total of " & LngCount & " Files " & "Containing Total Records of " & Count[/SIZE][/FONT]
[FONT=Calibri][SIZE=2] [/SIZE][/FONT]
[FONT=Calibri][SIZE=2] [COLOR=blue]Set [/COLOR]rst = DB.OpenRecordset("tblBDriveFilesExist") [COLOR=#00b050]'Set rst to Open the tblBDriveFilesExist table[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=2] rst.AddNew [COLOR=#00b050]'Add a new record to the B Drive File Exists table[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=2] rst!lngNoOfTextFiles = LngCount [COLOR=#00b050]'Assign the value lngCount variable to the lngNoOfTextFiles Field.[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=2] rst!lngTotalRecords = Count [COLOR=#00b050]'Assign the value of the count variable to the lngTotalRecords Field[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=2] rst!strUserID = sUser[/SIZE][/FONT]
[FONT=Calibri][SIZE=2] rst.Update [COLOR=#00b050]'Write the record to the database[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=2] rst.Close [COLOR=#00b050]'Close the recordset.[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=2] [COLOR=blue]Set [/COLOR]rst = [COLOR=blue]Nothing[/COLOR] [COLOR=#00b050]'Reset the rst variable to nothing[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=2] [COLOR=blue]End If[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=2] [/SIZE][/FONT]
[FONT=Calibri][SIZE=2] [COLOR=blue]If [/COLOR]LngCount = 0 [COLOR=blue]Then[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=2] [/SIZE][/FONT]
[FONT=Calibri][SIZE=2] MsgBox CurDate & " There are no text files in the B Drive"[/SIZE][/FONT]
[FONT=Calibri][SIZE=2] [/SIZE][/FONT]
[FONT=Calibri][SIZE=2] [COLOR=blue]Set [/COLOR]rst = DB.OpenRecordset("tblBDriveFilesExist") [COLOR=#00b050]'Set rst to Open the tblBDriveFilesExist table[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=2] rst.AddNew [COLOR=#00b050]'Add a new record to the B Drive File Exists table[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=2] rst!lngNoOfTextFiles = 0 [COLOR=#00b050]'Assign the value zero [0] variable to the lngNoOfTextFiles Field.[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=2] rst!lngTotalRecords = 0 [COLOR=#00b050]'Assign the value of the zero [0] variable to the lngTotalRecords Field[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=2] rst!strUserID = sUser[/SIZE][/FONT]
[FONT=Calibri][SIZE=2] rst.Update [COLOR=#00b050]'Write the record to the database[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=2] rst.Close [COLOR=#00b050]'Close the recordset.[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=2] [COLOR=blue]Set [/COLOR]rst = [COLOR=blue]Nothing[/COLOR] [COLOR=#00b050]'Reset the rst variable to nothing[/COLOR][/SIZE][/FONT]
[COLOR=blue][FONT=Calibri][SIZE=2] End If[/SIZE][/FONT][/COLOR]
[FONT=Calibri][SIZE=2] [/SIZE][/FONT]
[FONT=Calibri][SIZE=2] LngCount = 0[/SIZE][/FONT]
[FONT=Calibri][SIZE=2] Count = 0[/SIZE][/FONT]
[FONT=Calibri][SIZE=2] [/SIZE][/FONT]
[FONT=Calibri][SIZE=2] DoCmd.Echo [COLOR=blue]True[/COLOR], "Program End" [COLOR=#00b050]'Update the progress bar with the words "Program End"[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=2] DoCmd.Hourglass [COLOR=blue]False[/COLOR] [COLOR=#00b050]'Turn the Hourglass off[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=2] DoCmd.SetWarnings [COLOR=blue]True[/COLOR] [COLOR=#00b050]'Turn the warnings on[/COLOR][/SIZE][/FONT]
[COLOR=blue][FONT=Calibri][SIZE=2]End Function[/SIZE][/FONT][/COLOR]
