File Dialog-Browse/Save/Append

[OBP]

First of all, I still can't quite understand the logic of the first section of code that testing file names against checkfile, but beyond that in the code looking for a file in the folder

For i = LBound(CheckFile) To UBound(CheckFile)
FileFound = Dir(MyPath & "\" & CheckFile(i))

You are using MyPath which is the first time this appears in the code, you do set a path but it is this line that I have modified

sDest = "C:\Users\A C\Downloads" ' will be changed to CurrentProject.path & MAXIMOExports

So I am not sure how the code will find anything, even though the code does not provide any Path not found error messages, which I would have thought it would but does give a file not found error.
OK, I have the code for finding the files in the desired directory, this finds files starting with the second item in my checklist BlankDB

CheckFile = Array("Experiment*.*", "BlankDB*.*", "*TESTPRIMARY*", "*TESTSECONDARY*", "*TESTALTERNATE*")
On Error GoTo errorcatch

'The following lines of code use a network path for the source file :
sDest = "C:\Users\A C\Downloads\" & CheckFile(1) ' will be changed to CurrentProject.path & MAXIMOExports
Dim ctr As Integer
ctr = 1
FileFound = Dir(sDest) ' Retrieving the first entry.

Do Until FileFound = "" ' Start the loop.

MsgBox ctr & " - " & FileFound
ctr = ctr + 1
FileFound = Dir() ' Getting next entry.
Loop

I am just using ctr to count the number of files found in the folder.

Is it possible to place the Checklist file names in an Access table with the first (master) file names, so that that the system knows which file to append to?

[andycl]

MyPath is an error and should be sDest. I copied the code over incorrectly. As far as putting the master file names in a table, I could put the string that will be in every occurrence of the file but not the entire filename. As that will change for each file. For instance, it could be TESTMAIN070717 or it could be TESTMAIN1234 or it could be TESTMAIN54764. The file will always have TESTMAIN in the filename. That filename structure is applicable to all the file strings I have listed in Checkfile. I am trying to use Checkfile to look for existing files in a directory. If the file in the listbox is TESTMAIN071417 and there is a TESTMAIN072517, Checkfile should detect that and append the selected file in the listbox to the existing file in the directory.

Currently, the routine will take 1 file at a time from the listbox and run Checkfile to see if an existing file with matching string exist. It will detect if the file does exist and will open the selected file from the listbox. I cannot open the file found from Checkfile in the directory. I’m fine with changing my approach if the logic is not right and there is a better way. The overall intent is to take a file from the listbox and copy it to another directory. If file in the listbox contains TESTMAIN in the filename and a file in the directory contains TESTMAIN in its filename, merge the file from the listbox to the existing file in the directory. I know I’m being repetitive and it’s not because I don’t think you understand what I’m trying to do. I’m just wanting to make sure what I am asking for is understood and there in no confusion. It’s not to be condensing. FYI, I haven't ran the code you provided but will later tonight. Thanks again.

[OBP]

So did you run my code, which does find the file names if they are there?.
The idea about the table is to identify which file is the one to be appended to and use that instead of of the checklist, the problem with the checklist is that it is hard coded in to the VBA and needs a programmer to change or add any new files.

[andycl]

I just now ran your code. It does find the file names if existing. Thanks for providing that. The checklist is hard coded but it will not change. The TESTMAIN, TESTPRIMARY, and so on used in the checkfile filename will never change. That portion of the filename will be in every occurrence of a file. TESTMAIN123 or TESTMAIN456 or 7879TESTMAIN. The files will always be generated with one of the strings used in Checkfile. So I don't see how I can use a table to identify the filenames. The only thing for certain about the filename is that TESTMAIN, TESTPRIMARY, and so on will be in the filename.

I guess I could take what you provided and somehow use a few if statements. If file from listbox contains TESTMAIN and file in directory contains TESTMAIN, open and append. Is that a good route to go or some other way better. It would be quite a few if's. I was attempting to get around that by using the checkfile. Since the files in the listbox are being copied one file at a time. I was attempting to take that file and before FileCopy, run checkfile, and if the checkfile and listbox filename shared matching strings perform the open and append.

Thanks for the code.

[OBP]

Andy, I will work on the rest of the code now that I have it finding the files.
Is it an incorrect assumption to think that normally you would be handling just one new file at a time?

I am sorry to belabour this point but WHY does your organisation allow the proliferation of so many different versions of the same file names, this is IT Chaos, (or do you get the data in from other people)?
That then leads to the second question WHY does your organisation put up with it, why hasn't someone come up with a procedure or protocol to rename the files in a logical fashion?
That also leads to the question of why isn't the data imported in to Access Tables and then totally ignored in some archive somewhere, as the total table can be output to a single file at any time.

[OBP]

OK, I am not sure of this helps or not, as I think that the code that I supplied doees most of what you want, ie find the CheckFile that matches your list file, but I now have the code to establish the date of the file and the routine will which is the oldest file in the folder with the Checklist name.
This is the code

Dim CheckFile, FileFound As String, AllOk As Boolean, i As Integer, ErrMsg As String
Dim sDest As String, strFileTemp As String, FileExist As String, filed As Date, firstfile As String, firstfiled As Date
Dim ctlList As Control, varItem As Integer, posn As Integer, FileName As String
CheckFile = Array("*TESTMAIN*.*", "*TESTCASE*.*", "*TESTPRIMARY*", "*TESTSECONDARY*", "*TESTALTERNATE*")
On Error GoTo errorcatch

'The following lines of code use a network path for the source file :
sDest = "C:\Users\A C\Downloads\" & CheckFile(0) ' will be changed to CurrentProject.path & MAXIMOExports
MsgBox sDest
Dim ctr As Integer
ctr = 1
FileFound = Dir(sDest) ' Retrieving the first entry.
If FileFound <> "" Then
firstfile = FileFound
filed = FileDateTime("C:\Users\A C\Downloads\" & FileFound)
firstfiled = filed
MsgBox ctr & " - " & FileFound & " dated - " & filed
End If

Do Until FileFound = "" ' Start the loop.

ctr = ctr + 1
FileFound = Dir() ' Getting next entry.
If FileFound <> "" Then
filed = FileDateTime("C:\Users\A C\Downloads\" & FileFound)
If filed < firstfiled Then
firstfiled = filed
firstfile = FileFound
End If

MsgBox ctr & " - " & FileFound & " dated - " & filed
End If
Loop
MsgBox firstfile & " dated - " & firstfiled
Exit Sub


So I assume that having established the correct master file you will open it for appending, open your List file for reading and append it's data to the master file. Or if no file is found you will just copy the list file in to the folder with the extension new "text.dat" name.

[andycl]

In some cases there will may be only one new file at a time but there will be instances where there is a new file for each of the five files.

I understand the questions and it is a lot of trouble to move files around when you could import them directly to Access. In that case, if you already had the master file in Access it would be seamless to import/append the new records to the existing table. The file structure is something I can't change so I don't fight that. Just to clarify, what is being called the master file would contain a set of data and a second file of the same type would contain completely different data. The second file isn't a revised version of the first. The first file could have automotive parts/manufacturing from 100 cars and live in the directory. Then another export is done of 100 different cars. For every export of that file type, it is desired they all live in one file. Then, import the one file. But Access could handle all of this 1 file at a time with ease. I wanted that method but didn't get it.

I'll add the code you provided to the other part of code I have and see what happens. I appreciate your effort in this. I was hitting a wall.

[andycl]

Correct. If master file exist, open it for appending and open file from listbox for reading and append it's data to the master file. FileCopy for no matching file type.

[andycl]

Was the code you provided finding more than one file for you? It only sees the first entry in the directory for me? It looks like everything is in place to find each entry but it ins't for me.

[OBP]

That code finds the oldest file with first item name in the Array, you would need to loop through the array to get the oldest versions of the others.
That is why I said I wasn't sure whether or not helped with what you were trying to do.
I was trying to identify the Master file for you.

[andycl]

I understand. I may be missing the point of what it does but since there will be only one instance of each file type in the directory the date shouldn't matter. There will only be one TESTMAIN, TESTPRIMARY, and so on. If you are copying another file from the listbox of that same file type is where the open and append routine should take place. I could be missing the obvious so just point it out if I am. I think I can the previous version you showed and look for the files and hopefully work some logical operators in there to open and append to the files. Thanks for the response.

[OBP]

No you aren't missing the point, I thought from your previous posts that there were all sort of files with variations of TESTMAIN etc, but if you do not have trouble identifying the original then as you say it is time to open that file for appending and opening the new file for reading.
So what logical operators do you need help with can you give me a simple example and I will advise accordingly.

[andycl]

My apologies. I could have been more clear in that respect. I have it set up to take one file at a time from the listbox and copy it to location sDest. If the file in file from the listbox is TESTMAIN and the file found piece you provided finds a TESTMAIN master, then append data from listbox TESTMAIN to master TESTMAIN in the directory sDest. Perform that operation for each file in the listbox. If no master file found, copy file to directory. I copied the code to this thread of the copy file portion. That's what I need to add this bit to. I can copy it here again if that would help.

I would have responded sooner but have been away. Thanks for the guidance.

[OBP]

I will try and take a look at it tomorrow nd get back to you.

[andycl]

OK that will work.

[OBP]

Well I should have done it this way in the first place, sorry it has taken me so long to get here.
But working from the beginning I have gone through step by step.

This code which includes some of yours and some of mine does the basics, it tests if the list file is in the array, if it is it appends the data to the file found in the folder, if it isn't it copies the file to the folder where the array files are.
Now I am not sure this does what you want in terms of finding the Actual Master files.
You seem to know which ones they are, but I don't, if it was me I would place the known master file's paths in a table in the database and refer to them in that way, because takes away the uncertainty of whether it is the correct file.

It doesn't include the part of your code that renames the file to temp for copying as I am not sure of the logic behind it.

But at least I have got it this far, the rest should just be finessing what we have to include your equirements.

Here is the code, as usual you do not the message boxes which are there to show the progress through the code.

Dim CheckFile, FileFound As String, AllOk As Boolean, i As Integer, ErrMsg As String, sOrigin As String, datastring As String
Dim sDest As String, strFileTemp As String, FileExist As String, filed As Date, firstfile As String, firstfiled As Date
Dim ctlList As Control, varItem As Integer, posn As Integer, FileName As String, result As Integer, fileinarray As Integer
CheckFile = Array("TESTMAIN", "TESTCASE", "TESTPRIMARY", "TESTSECONDARY", "TESTALTERNATE")
On Error GoTo errorcatch
Set fs = CreateObject("Scripting.FileSystemObject")
'The following lines of code use a network path for the source file :
sDest = "C:\Users\A C\Downloads\TestFolder\" ' & CheckFile(0) ' will be changed to CurrentProject.path & MAXIMOExports
sOrigin = "C:\Users\A C\Downloads\TestFolder2\"
'MsgBox sDest & " " & sOrigin
Set ctlList = Me.List0
For varItem = 0 To ctlList.ListCount - 1
For x = 1 To Len(ctlList.ItemData(varItem))
' Parse filename only
If Mid(ctlList.ItemData(varItem), x, 1) = "\" Then posn = x
Next x
firstfile = ctlList.ItemData(varItem)
FileName = Right(ctlList.ItemData(varItem), Len(ctlList.ItemData(varItem)) - posn)
'MsgBox varItem & " - " & FileName
result = 0
fileinarray = 0
For i = LBound(CheckFile) To UBound(CheckFile)
result = InStr(1, ctlList.ItemData(varItem), CheckFile(i))
'MsgBox ctlList.ItemData(varItem) & " - " & CheckFile(i)
If result <> 0 Then
MsgBox result & " - File is in array append it"
fileinarray = 1
FileFound = Dir(sDest & CheckFile(i) & "*.*")
MsgBox FileFound

Open sDest & FileFound For Append As #2
MsgBox firstfile
'Open sSourceFile For Input As SourceFileNum
Open firstfile For Input As #1
MsgBox 2
Do Until EOF(1)
Line Input #1, datastring
MsgBox datastring
Print #2, datastring
Loop
Close #2
Close #1
Exit For
End If
Next i
If fileinarray = 0 Then
MsgBox result & " - file is not in array copy it"
FileCopy ctlList.ItemData(varItem), sDest & FileName
End If
Next varItem
Exit Sub
errorcatch:
Close #2
Close #1
MsgBox "Error - " & Err.Description

[andycl]

Thanks for your help first of all. It is much appreciated. This is working as intended expect at FileFound. It breaks down for me at line below - FileFound. The files are exported as dat files. So strFileTemp replaces the dat extension with .txt extension. VBA can not open a dat (or can it?). So when the master file is copied it will copy the dat file as well as change the extension and create a txt file. Then when you are copying the a second file of that file type the FileFound should look to find the Checkfile .txt file. That's where the disconnect is now. I tried adding the ".txt*" filter but that doesn't seem to work. Just to clarify the Checkfile does find the existing files and the Msgbox confirms that. Just cannot open the master file because FileFound is empty.

FileFound = Dir(sDest & "\" & CheckFile(i) & "*_Temp.txt*")
MsgBox FileFound


Open sDest & FileFound For Append As #2
MsgBox firstfile

[OBP]

First of all check what
CheckfFile &"*_Temp.txt*"
actually gives you as a file name.
Does that filename actually exist in the folder sDest?
I am not sure why you would need the astrix on the end.

[andycl]

It does exist. I was attempting to explain that the very first file that is copied to sDest is .dat file extension. Since I didn't think VBA could open, read, and write to that type of file I copy the .dat as well as create a Temp.txt. Then when a second or third file of that type (TESTMAIN) is copied to the directory it should find the TESTMAIN.txt file and append to that one. This is all messy I know. I didn't think I could open and read anything from the .dat file. Let me mess around with it and see what's going on. Maybe you can and this all not needed.

[SamT]

I'm new to this thread and only skimmed all the previous posts. but as I understand the situation...
You have a "NewFiles" Directory, (FixedPath,) Which Must Not be Changed, and an "AccessFiles" directory, NewPath, which does change from time to time.

All files have one of =Array("TESTMAIN", "TESTCASE", "TESTPRIMARY", "TESTSECONDARY", "TESTALTERNATE") Strings in their Names.

For each file in "NewFiles," if a file with the same inner String exists in "AccessFiles, Append, else Move.

Personally, I would not list any files in a Form Listbox. I might show the current operation in the Status Bar.

I would iterate thru that, (actually a similar) Array, Use Dir to find any and all matching files in NewFiles, compare each to the files AccessFiles and append or move as required.

This is not complete Code, since I don't do Access and OBP is doing an excellent job for you.

Sub Main()
'Iterates thru NewFiles and Checks AccessFiles, decides which sub Procedure to pass FileName to

NewPAth = 'Folder Picker for AccessFiles Folder

StringList = Array("MAIN", "CASE", "PRIMARY", "SECONDARY", "ALTERNATE")
For i = LBound(StringList) to UBound(StringList)
'StatusBar Code

FileName = FixedPath & "*" & StringList(i)  & "*"
If Len(FileName) > 1 Then
ExistingFile = CheckFile(NewPath, StringList(i))  
If Len(ExistingFile) > 0 Then
AppendFile FileName, NewPath, ExistingFile
Else
MoveFile NewPath, FileName
End If
End If
Next i
End Sub

Function CheckFile(NewPAth As String, InnerString As String) As String
'StatusBar Code
CheckFile = Dir NewPAth  & "*" & InnerString  & "*"
End Function

Sub AppendFile(FileName As String, NewPath As String, ExistingFile As String)
'StatusBar Code
'Code to open FixedPath & FileName for Append
'Code to Open and append to NewPath & ExistingFile 
End Sub

Sub MoveFile(FileName as String, NewPath As String)
'StatusBar Code
'Code to move FixedPath & FileName to NewPath
End Sub