File Dialog-Browse/Save/Append

[andycl]

I need to be able to select a file (.txt), move the file to a different directory; however, if a similar file is in that directory append to it. I use similar because the file names will not have the exact same filename but similar. i.e myfile12 is already in the directory and the file I am moving is myfile123 and its contents need to be added to the existing myfile12.

I've explored file dialog for browsing and saving but am not having any good fortune putting it all together. Any place or pointers to start with are appreciated.

[OBP]

I am assuming that you are working with text files?
If so you can open the destination file in Append mode (instead of input mode as in your previous question) and write a string containing the new data in one go, but you obviously have to put the data in the string first.
You will obviously also need to establish the similarity using VBA If/then/else.

Do you have the code to Browse or do you really need to do that if the Folders you use are fixed?

[andycl]

What do you mean by fixed? Files are already in that directory so why have a browse feature? This is just something that is being requested for so I'm trying to incorporate it. If a user gets the external files once but something was left out, they go back and get what was left out it and it needs to bed added to the existing file (R0). The files being moved are the R0 files. And the importing you helped with previously is where they are imported and are now R1 tables ready for revisions to be made. And eventually when all revisions have been made I need a compare feature to export a text file of Delta's between R0 and R1.

Different question. I have never used nor seen where you can be linked to a table externally and make revisions to the file via a form. The tables must be physically contained within Access. Right or wrong?

[OBP]

Well there is not a point in browsing if they are always in the same folder, but I do have to the VBA for it.
As to your question Access can link to other data tables like SQL, but it requires connections that I don't normally deal with.
Like I said you can use the Append version of File Open to append the data.

[andycl]

Ok thanks, if you have something where browse and save as are incorporated together that would be helpful. I never can get the selected files to pass to the save as dialog option. The browse feature isn't necessarily for the first set of files but in the event there is a second file that should be including have the browse option to move from one directory to the other and append to the existing file that is already at that path.

[OBP]

Take a look at this simple database, I have extracted code from a much bigger one.
This works for me but you may need to set references in the VBA Editor's References Library, I have and excel sheet which shows references that need setting if you should need it.
Open Form 1 and click the Browse button.

The actual code is in a module.
There is some othe quite useful code in the database that came along with the Modules that I imported.

ps let me know if you need help with the Append routine.

[andycl]

Thanks for the db. I was having success getting the browse function to work correctly. It is taking that file and saving to another filepath that has me hung up. I will see if I can pass the selected file to the save as file dialog option. Thanks again.

[andycl]

The browse and select file functionality is working fine. I can select multiple files and display the file path in a list box. I now need to take those files and have the ability to save them to a different directory. Filecopy seems logically and if I could define the destination path I'd be done. However, I need to be able to browse to any location and copy the files. This is also where I will need to start the if/then/else statements for appending to existing files.

[andycl]

I got somewhere just not all the way there. I tried two different methods but never could get the files in the list box to save to a folder. This really needs to have the append criteria added but I need to have the saving portion down before trying that. I received "object does not except this property or method." I'm not calling the selected items correctly from the list box. The first method is the following:


Private Sub saveFileAs_Click()

'The following lines of code use a network path for the source file :
Dim sDest As String
Dim varFile As Variant


sDest = CurrentProject.path & "\Testbed\"


For Each varFile In Me.FileList
FileCopy varFile, sDest
Next varFile


End Sub

The second method used two list box. One (InvisibleFileList) to return the full directory path and the second list box (FileList) to return the filename only. I received "path not found." The save command is as follows:


Public Sub SaveAttachments()



Dim fileDestination As String
Dim i As Long


'Update this to the correct folder, be sure to include the ending \
fileDestination = CurrentProject.path & "\Testbed\"


For i = 0 To Me.FileList.ListCount - 1
FileCopy Me.InvisiblePathList.ItemData(i) & "\" & Me.FileList.ItemData(i), fileDestination & Me.FileList.ItemData(i)
Next i


End Sub

[OBP]

If you are just copying/moving the file to a new directory you can use

Name "C:\Users\Ron\SourceFolder\Test.xls" As "C:\Users\Ron\DestFolder\TestNew.xls"

The append method means opening the current file as we did for import and put the data in a string and then use File Append instead of File output to append it to the other file.

Why would you copy all the files from one directory to another each time, will you then delete them from the original file.
The browse method I provided identifies a single file and places it an unbound field for using wih your second method of copy.

[andycl]

The files are exported to a default location which cannot be changed. I'm moving the files to where the DB and any associated files for the DB will live. The list box and multiselect option is what I need to go for in this case. What you provided does work great and I will use that for single file browse select. So thank you. The destination folder will not always be the same so I was attempting to use CurrentProject.Path but keep getting file path not found . It does seem like a "what's the point" process but it's something I'm having to do.

[OBP]

Can you post what you have and what you have tried, I may have some old code for List box manipulation, but I would have thought that an Array holding file paths would do just as well.
I will test what you have.

[andycl]

Let me know if this version doesn't work for you. Thanks

[OBP]

OK, I have identified a couple of problems with what you are trying.
1. Your working through the file list in the Save VBA is not corrrect, I have that working now using this code
Dim ctlList As Control, varItem As Variant

' Return Control object variable pointing to list box.
Set ctlList = Me.FileList
' Enumerate through selected items.
For varItem = 0 To ctlList.ListCount - 1
' Print value of bound column.
MsgBox ctlList.ItemData(varItem)
'FileCopy ctlList.ItemData(varItem), sDest
Next varItem

Obviously the msgbox is not necessary I am just using to identify that the code is working.

2. You are trying to use the complete Path of the file you want to save, instead of the File Name, if you use File Scripting you can use GetFileName to get just the File's name for the file list.
You then have to add that to sDest to complete path for the file copy.
I will replace your browse version with the one that I use with File scripting and if it works OK I will post it on here tomorrow.

ps actually we could just use the right most characters of you file list to get the filename. I will try that first as it means that you can retain your get file code.

[OBP]

Well that went better than I thought, here is the code

'The following lines of code use a network path for the source file :
Dim sDest As String
Dim varFile As Variant

sDest = CurrentProject.path & "\TestFolder\" ' need to change this back to your folder
Dim ctlList As Control, varItem As Variant, posn As Integer, filename As String

' Return Control object variable pointing to list box.
Set ctlList = Me.FileList
' Enumerate through selected items.
For varItem = 0 To ctlList.ListCount - 1
' Print value of bound column.
MsgBox ctlList.ItemData(varItem)
For x = 1 To Len(ctlList.ItemData(varItem))
If Mid(ctlList.ItemData(varItem), x, 1) = "\" Then posn = x
Next x
filename = Right(ctlList.ItemData(varItem), Len(ctlList.ItemData(varItem)) - posn)
MsgBox filename
FileCopy ctlList.ItemData(varItem), sDest & filename



Note the msgboxes are not actually needed, it just shows you what is going on.
Also the change of folder name to suit my folder set up, you need to change it back to yours.
I have attached my version of the database.

[andycl]

Thanks for pointing out where I was going wrong. I'll look at what you have attached and go from there. I should be able to incorporate the the file append process into this. Thanks again.

[andycl]

Hey OBP, I'm trying to get started with the append routine. The originally exported file is a dat file. I'm unaware that it is even possible to open and write to a dat file with VBA. I tried reading from it and it never would work. So I am taking the files that are selected and creating a "_Temp.txt" that I can I use in the append routine. I have it copying both files like I want but the text file gets copied and named as _Temp.txt only and leaves the rest of the filename off from the ctlList. Please see if you see where that is happening because I am overlooking it. Thanks


For varItem = 0 To ctlList.ListCount - 1 ' Print value of bound column - used for testing purposes.
'MsgBox ctlList.ItemData(varItem)
For x = 1 To Len(ctlList.ItemData(varItem))
If Mid(ctlList.ItemData(varItem), x, 1) = "\" Then posn = x
Next x
filename = Right(ctlList.ItemData(varItem), Len(ctlList.ItemData(varItem)) - posn)
' Print value of bound column - used for testing purposes.
'MsgBox filename
strExt = filename
'MsgBox filename, vbInformation, "Testing"
' Replace the extension with _Temp.txt for a new file name
strFileTemp = Replace(ctlList.ItemData(varItem), strExt, "_Temp.txt")
' Copy .txt files
FileCopy ctlList.ItemData(varItem), sDest & strFileTemp
' Reset file extension .dat
filename = Right(ctlList.ItemData(varItem), Len(ctlList.ItemData(varItem)) - posn)
FileCopy ctlList.ItemData(varItem), sDest & filename

[OBP]

Taking a quick look I would say that this line

strFileTemp = Replace(ctlList.ItemData(varItem), strExt, "_Temp.txt")

is replacing the file name completely instead of appending the _temp.txt to the left part of the file name.

so perhaps you need to identify the left part of the jilename

strExt

after you have identified the right part here

filename = Right(ctlList.ItemData(varItem), Len(ctlList.ItemData(varItem)) - posn)

by finding the "." and using the left() function to return the filename without the extension on it.

This would then become this

strFileTemp = Replace(ctlList.ItemData(varItem), strExt, leftpart of strExt & "_Temp.txt")

where leftpart of strExt is the actual file name less the extension.

[andycl]

I was able to get the file names copied together. Thanks for the suggestion. Am I thinking about this correctly? The files being copied have similar file names i.e file1, file1a, file2, file2a and so on. If file1 exists and file1a is being copied, the contents of file1a should be merged to file1. If file2 exists and file2a is being copied, the contents of file2a should be merged to file2. The header rows of file1a should be omitted as they exist in file1. So to do this it should be something like. If file1 exist, open file1a and store data string, open file1 (destination file) in append mode, and add the data string to it. Thanks

[OBP]

Yep, spot on.
What do they use the files for?
Once you have the data in Access why do they need them?

[andycl]

I don't really think the append portion is something I need to do. I think the thinking was if there was more than 1 of the original exported files to combine them and have one master file for that file set. But if a file has already been imported into Access and you later have an additional file you need to append to the table in Access. If you append to the file and then try to import, it's not going to play nice because it will see duplicates. I should be able to set up an append to table update feature that would work for every additional file that's ever required. At least that's what I think. You have any thoughts and past experiences with this?

[OBP]

Access should be the main file obviously. Yes are correct about Access not liking the duplicates situation.
Believe it or not I haven't done much importing of data in Access for myself, so I can't really comment that much, what I would suggest is that the original files get renamed to indicate that they have been imported and can therefore be ignored in future.

[andycl]

That's probably the best way to go. I don't see a way to append to the file if it's already been imported as a table. There's probably a routine I could right to ignore the data already in the table and import what's new but that isn't worth it if access already has an append feature. Just out of curiosity have you ever experienced the file copy command not copying the file correctly? What I'm asking is after copying a file should you compare the selected and copied file to look for errors? I've never known this to happen even if you are changing the file extension but thought I would ask?

[OBP]

As I said I haven't done much with text files, but you certainly could write a routine to compare them if you are really worried about the data integrity, you can also get free software that compares files.

[andycl]

I've attempted a couple of different methods but not gotten what I am looking for. I'm trying to identify if a file exist before copying and if it does append to it. What do you think I can try differently and how to incorporate both open file for reading (source file) open file for appending (destination file). I thought an array was better off than long nested if then statements. But if there is a better way I'm open for suggestions.


Private Sub Command111_Click()


Dim CheckFile, FileFound As String, AllOk As Boolean, i As Integer, ErrMsg As String
Dim sDest As String, strFileTemp As String, FileExist As String
Dim ctlList As Control, varItem As Variant, posn As Integer, FileName As String
CheckFile = Array("*TESTMAIN*", "*TESTCASE*", "*TESTPRIMARY*", "*TESTSECONDARY*", "*TESTALTERNATE*")


'The following lines of code use a network path for the source file :
sDest = CurrentProject.path & "\Test Folder\" s



' Return Control object variable pointing to Me.FileList list box.
Set ctlList = Me.FileList

' Enumerate through selected items.
For varItem = 0 To ctlList.ListCount - 1
' Print value of bound column - used for testing purposes.
'MsgBox ctlList.ItemData(varItem)
For x = 1 To Len(ctlList.ItemData(varItem))
' Parse filename only
If Mid(ctlList.ItemData(varItem), x, 1) = "\" Then posn = x
Next x
FileName = Right(ctlList.ItemData(varItem), Len(ctlList.ItemData(varItem)) - posn)
' Print value of bound column - used for testing purposes.
MsgBox FileName, vbInformation, "For Testing"
' Replace the extension with _Temp.txt for a temp file
strFileTemp = Replace(ctlList.ItemData(varItem), ".dat", "_Temp.txt")
For i = LBound(CheckFile) To UBound(CheckFile)
FileFound = Dir(MyPath & "\" & CheckFile(i))
If FileFound = "" Then
AllOk = False
ErrMsg = ErrMsg & CheckFile(i) & vbNewLine
End If
Next i
If Not AllOk Then
'MsgBox strFileTemp, vbInformation, "FileExist"
' Copy .txt files
FileCopy ctlList.ItemData(varItem), sDest & strFileTemp
' Reset file extension .dat
FileName = Right(ctlList.ItemData(varItem), Len(ctlList.ItemData(varItem)) - posn)
' Copy dat file
FileCopy ctlList.ItemData(varItem), sDest & FileName
Next varItem
' Print file storage location
MsgBox CurrentProject.path & "\Test Folder\", vbInformation, "Files Have Been Copied To The Below Location:"

'Kill strFileTemp

' Check that all files in Me.FileList copied MaximoExports directory
If Dir(CurrentProject.path & "\Test Folder\") = FileName Then
MsgBox "All files were successfully imported"
Else
MsgBox "All Files Did Not Import. Return To Import Menu"
End If


End Sub

[andycl]

An End If was left out causing the error. It is running; however, it provides the files that do not exist in the directory as opposed to the files that share a common filename as any of the files being copied.

[andycl]

Updated - It's identifying the existing files in the destination source. I'm not sure if I have the sequence of steps correct. Maybe the array should be the first thing that is processed. What do I need to set Open File For Append to for the existing file? If you don't mind giving me a hint. Here's updated code. Thanks


Dim CheckFile, FileFound As String, AllOk As Boolean, i As Integer, ErrMsg As StringDim sDest As String, strFileTemp As String, FileExist As String
Dim ctlList As Control, varItem As Variant, posn As Integer, FileName As String
CheckFile = Array("*LOCMAIN*", "*LOCSPEC*", "*ASSETMAIN*", "*ASSETSPEC*", "*DIGITASSET*")






'The following lines of code use a network path for the source file :
sDest = CurrentProject.path & "\Test Folder\" ' will be changed to CurrentProject.path & MAXIMOExports


' Return Control object variable pointing to Me.FileList list box.
Set ctlList = Me.FileList

' Enumerate through selected items.
For varItem = 0 To ctlList.ListCount - 1
' Print value of bound column - used for testing purposes.
'MsgBox ctlList.ItemData(varItem)
For x = 1 To Len(ctlList.ItemData(varItem))
' Parse filename only
If Mid(ctlList.ItemData(varItem), x, 1) = "\" Then posn = x
Next x
FileName = Right(ctlList.ItemData(varItem), Len(ctlList.ItemData(varItem)) - posn)
' Print value of bound column - used for testing purposes.
MsgBox FileName, vbInformation, "For Testing"
' Replace the extension with _Temp.txt for a temp file
strFileTemp = Replace(ctlList.ItemData(varItem), ".dat", "_Temp.txt")
For i = LBound(CheckFile) To UBound(CheckFile)
FileFound = Dir(MyPath & "\" & CheckFile(i))
MsgBox FileFound
If FileFound = "" Then
FileExist = CheckFile(i)
MsgBox FileExist
AllOk = False
End If
If strFileTemp Like FileExist Then
FileExist = True
MsgBox FileExist
End If
ErrMsg = ErrMsg & CheckFile(i) & vbNewLine
Next i
If Not AllOk Then
'MsgBox strFileTemp, vbInformation, "FileExist"
' Copy .txt files
FileCopy ctlList.ItemData(varItem), sDest & strFileTemp
' Reset file extension .dat
FileName = Right(ctlList.ItemData(varItem), Len(ctlList.ItemData(varItem)) - posn)
' Copy dat file
FileCopy ctlList.ItemData(varItem), sDest & FileName
End If
Next varItem
' Print file storage location
MsgBox CurrentProject.path & "\Test Folder\", vbInformation, "Files Have Been Copied To The Below Location:"

'Kill strFileTemp

' Check that all files in Me.FileList copied MaximoExports directory
If Dir(CurrentProject.path & "\Test Folder\") = FileName Then
MsgBox "All files were successfully imported"
Else
MsgBox "All Files Did Not Import. Return To Import Menu"
End If

[OBP]

Sorry I didn't get back to you yesterday, Tuesday is my Poker Night.
As you have developed the code where abouts do you want the Append VBA code?

The code is simple

Open filename For Append As #FileNumber

You can then use

Print #FileNumber, Textstring

or

Write #FileNumber, Textstring

[andycl]

No worries at all. Hope you playted well.
I think it should go after,
If strFileTemp Like FileExist Then
FileExist = True
MsgBox FileExist
This should be where if a similar file already exist for the file being copied is set True. At that point, the source file (strFileTemp) is opened for reading and the destination file (checkfile) is opened for appending. But I need to grab the destination file. Right now it is only looking for a file in a directory with a certain string.

[OBP]

I managed to come second and win a few pounds, so not too bad, I play on Tuesdays and Sundays in local Pubs.

I would suggest creating a test folder with copies of your files in it (unless it already is a test folder) and trying the code that I suggested.
Have you got the code for extracting the data in to a string for sending to the file?
I provided code for reading CSV files here

http://www.vbaexpress.com/forum/showthread.php?20548-Solved-How-to-import-a-text-file-with-more-than-255-fields-into-Access

and here

http://www.vbaexpress.com/forum/showthread.php?59966-split-data-into-individual-groups

[andycl]

Always good to win.

I have test files and test folder I'm working with now. I'll look at what you provided. First, I have to get the checkfile and strFileTemp to be set to the same similar files. Now in the loop, checkfile may be at TESTMAIN and strFileTemp at TEST PRIMARY. So if I opened and appended I would combine the wrong file to the destination file. That's why I said I thought my sequence of steps may be out of order. It should take the strFileTemp filename and checkfile look for existing files with that same string. If found, then open and append. Thanks for the info.

[OBP]

Does the test you are using pick up the File's Date?
That should tell you which is the original.
If you use File Scripting (FSO) you can pick up data like "last accessed" and file size.

[andycl]

The program loops through the listbox and checks if one of the selected files already exist in the directory to which it is being copied. But it seems like it is setting the FileExist flag true if the text string has a match in the listbox and not the destination directory. It should only set FileExist true if the string is in the directory. If that part is working, I then try and open the selected file and matching file in the directory for appending. It will not run past that point because checkfile(i) is an array and not the actual file. Do I need to go back and use MID to retrieve the entire filename so I can actually open the file for appending? I've tried several methods already. Maybe my way of doing this is the wrong way to go about it. Maybe there is a better way to search for existing files and then proceed to the append routine. Any help or advice is welcome. Thanks




Dim CheckFile, FileFound As String, AllOk As Boolean, i As Integer, ErrMsg As String
Dim sDest As String, strFileTemp As String, FileExist As String
Dim ctlList As Control, varItem As Variant, posn As Integer, FileName As String
CheckFile = Array("*TESTMAIN*", "*TESTCASE*", "*TESTPRIMARY*", "*TESTSECONDARY*", "*TESTALTERNATE*")






'The following lines of code use a network path for the source file :
sDest = CurrentProject.path & "\Test Folder\" ' will be changed to CurrentProject.path & MAXIMOExports


' Return Control object variable pointing to Me.FileList list box.
Set ctlList = Me.FileList

' Enumerate through selected items.
For varItem = 0 To ctlList.ListCount - 1
' Print value of bound column - used for testing purposes.
'MsgBox ctlList.ItemData(varItem)
For x = 1 To Len(ctlList.ItemData(varItem))
' Parse filename only
If Mid(ctlList.ItemData(varItem), x, 1) = "\" Then posn = x
Next x
FileName = Right(ctlList.ItemData(varItem), Len(ctlList.ItemData(varItem)) - posn)
' Print value of bound column - used for testing purposes.
MsgBox FileName, vbInformation, "For Testing"
' Replace the extension with _Temp.txt for a temp file
strFileTemp = Replace(ctlList.ItemData(varItem), ".dat", "_Temp.txt")

AllOk = True
For i = LBound(CheckFile) To UBound(CheckFile)
FileFound = Dir(MyPath & "\" & CheckFile(i))
If FileFound = "" Then
FileExist = CheckFile(i)
MsgBox FileExist
AllOk = False
End If

If strFileTemp Like FileExist Then
Open strFileTemp For Input As #1
'Open FileExist For Output As #8
'Do Until EOF(1)
'xchar = Input(1, #1)
'MsgBox xchar
FileExist = True '- used in testing
MsgBox FileExist '- used in testing
End If


'ErrMsg = ErrMsg & CheckFile(i) & vbNewLine
Next i


If Not AllOk Then
'MsgBox strFileTemp, vbInformation, "FileExist"
' Copy .txt files
FileCopy ctlList.ItemData(varItem), sDest & strFileTemp
' Reset file extension .dat
FileName = Right(ctlList.ItemData(varItem), Len(ctlList.ItemData(varItem)) - posn)
' Copy dat file
FileCopy ctlList.ItemData(varItem), sDest & FileName
End If
Next varItem


' Print file storage location
MsgBox CurrentProject.path & "\Test Folder\", vbInformation, "Files Have Been Copied To The Below Location:"

'Kill strFileTemp

' Check that all files in Me.FileList copied MaximoExports directory
If Dir(CurrentProject.path & "\Test Folder\") = FileName Then
MsgBox "All files were successfully imported"
Else
MsgBox "All Files Did Not Import. Return To Import Menu"
End If

[OBP]

I will take a look at the code and see if I can tell where it is going wrong.

[andycl]

I don't mind if the method is different. If the end result is the same, then its ok with me.

[OBP]

Can you explain your logic of having a list and an array, what is the purpose of CheckFile = Array("*TESTMAIN*", "*TESTCASE*", "*TESTPRIMARY*", "*TESTSECONDARY*", "*TESTALTERNATE*")?
I have created a form with a list and I am now testing the code, but I will be busy this afternoon with the grandchildren and then Poker tonight, so I probably won't get around to really testing the code until tomorrow.

[andycl]

TESTMAIN, TESTCASE, TESTPRIMARY, TESTSECONDARY, TESTALTERNATE will always be in the filename. There may be leading or trailing numbers or a combination of both the filename. TESTMAIN070117 may already exist in the directory and the file being copied (file in the listbox) is TESTMAIN072317. TESTMAIN072317 (file in listbox) needs to be added/merged with the file that already exist in the directory (TESTMAIN070117). CheckFile is supposed to search for such files in the destination folder. If the file in listbox is TESTMAIN0702317, when Checkfile runs it should look for an existing TESTMAIN file in the directory. If there is an existing file, at that point the selected file should be open for reading and the destination file should be open for appending. If not, just a plain FileCopy will be performed.

Good luck in poker. Thanks for looking at this.

[OBP]

Before I go out, I see what you are doing with the Array, so what is the List box for?
Is that where you put the File names that the serach finds?

[andycl]

No, the files selected from the file dialog are placed in the listbox. I put them in the listbox as a way the use can see all that was selected. As the copy procedure takes place, check if a common file in the destination directory exist. If file exist, merge to existing. If not, filecopy.

[andycl]

Hope that clarifies. Listbox is a way to store files selected. Probably not required but wanted users to see files selected if several files were selected. User will have a chance to delete a file if one was selected out of error before copying.

[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

[andycl]

VBA can indeed open and write to a file extension dat. With that, I have removed the strFileTemp and create text file portion. However, FileFound is where the issue. The existing file will have characters before and after the Checkfile(i) array string. For instance, 123456TESTMAINabcd or charTESTPRIMARY789. That seems to be the issue. When I reduced the filename in the directory to TESTMAIN, it ran through smoothly.

[andycl]

I tried revising FileFound = Dir(sDest & CheckFile(i) & "*.*") several different ways. Just so I am communication correctly the string TESTMAIN TESTPRIMARY and so on are apart of the filename. There will be character before and after the Checkfile string.

FYI, SamT thanks for the contribution. This method seems to be working for OBP so hopefully after this last issue is ironed out I will have it running as well. If not, your suggestion may have to be something I visit.

[OBP]

Andy, the problem is with how you ask Dir to find your files.
I have changed this line

FileFound = Dir(sDest & CheckFile(i) & "*.*")

to

FileFound = Dir(sDest & "*" & CheckFile(i) & "*.*")

and it now finds
123456TESTMAINabcd
however it finds the first version of TestMain, opens it and then appends the data to it.
So we are now back to how does the VBA code know which is the correct TESTMAIN for it to append to.
I can change it to iterate through all of them and append the data to them all, but that is hardly efficient.
Which is why I asked you if you know the names of the Master files why aren't those names in a table so that the VBA can know the correct table to append to, you would not even need to find it.

[OBP]

SamT, this is more like BASIC and VBA than Access, as Access objects are not really involved, so I am going back 30+ years for the correct code etc.

[OBP]

Right, this version of the code checks for the files in the list (which are in TestFolder2) checks if they are in the array, if it is not in the array it copies it to TestFolder where the masters are.
If the file from the list is in the array it appends the data to all the Master files with that file name in the array.
It works and currently updates 4 versions of TestMain, 2 versions of TESTCASE and copies one file called testfile text, it ells you which file is being appended to and from which new file.

Here is the code in full



Dim CheckFile, FileFound As String, i As Integer, datastring As String
Dim sDest As String, firstfile As String, filcopied As Integer
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
'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
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
filecopied = 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) & "*.*")
GoSub Appendsub
'Open sSourceFile For Input As SourceFileNum
Do Until FileFound = "" ' Start the loop.
ctr = ctr + 1
FileFound = Dir() ' Getting next entry.
If FileFound <> "" Then GoSub Appendsub
Loop
Exit For
End If
Next i
If fileinarray = 0 Then
If filecopied = 0 Then
MsgBox result & " - file is not in array copy it"
FileCopy ctlList.ItemData(varItem), sDest & FileName
filecopied = 1
End If
End If
Next varItem

Exit Sub
errorcatch:
Close #2
Close #1
MsgBox "Error - " & Err.Description


Appendsub:
MsgBox "current file appending to - " & FileFound & " from - " & FileName
Open firstfile For Input As #1
Open sDest & FileFound For Append As #2
Do Until EOF(1)
Line Input #1, datastring
Print #2, datastring
Loop
Close #1
Close #2
MsgBox "data transferred to " & FileFound
Return

[andycl]

It seems like by adding the asterik to the FileFound line corrected the issue when there is a master file in the directory. For files in the listbox that do not have a master file in the sDest directory it is not performing the intial FileCopy. It goes to error catch - Path not found. Same goes for the newer code you posted. But it is appending if there is master already in sDest. Just not doing the initial copy if there is not a master file there. I apologize. You've been great with your help and I'm sorry its working this way.

I hear what you are saying about how does it know which TESTMAIN to append to. There will only be one TESTMAIN, TESTPRIMARY, and so forth in sDest directory. If there is a second file of that type, append to the master. If there is a third of that type, append to the master.

[OBP]

Note that my version copies the non array file from TestFolder2 to TestFolder, so you need to ensure that your path or paths reflect that in the line of code

FileCopy ctlList.ItemData(varItem), sDest & FileName

my ctlList.ItemData(varItem) contains the complete path to the list file, does yours?

[andycl]

Ok, no it is not. It is the filename only. So ctlList.ItemData(varItem) should be set to the full path file?

[OBP]

Yes this is what my non array entry looks like

C:\Users\A C\Downloads\TestFolder2\testfile.txt

[SamT]

For files in the listbox that do not have a master file in the sDest directory it is not performing the intial FileCopy

Take a look at the use of ExistingFile and Checkfile in my last post

[andycl]

What is testfile.txt? I thought we were taking the files from the listbox and using Checkfile to test if the file from the listbox contained one of the strings in the array Checkfile(i). To fix, what does ctlList.Item(varItem) need to point to. I have it pointing to the filename in the listbox.

I do apologize for not putting this all together. You've been very helpful and I hate to ask questions like this.

[andycl]

I have a root folder listbox on the form. I set ctlList.ItemData(varItem) to that, which is the complete file path. Didn't seem to do anything.

[OBP]

Andy, without your files I have had to produce dummy data for test purposes and testfile.txt is just a file that I already had that I new did not fit any of your Array names.
So I put that in the list to test the code and it worked fine.
Without access to your computer I can only simulate what I think you need.
If you can provide the actual paths to your files and a non array name I can reproduce that on my computer.

Don't worry about how long this is taking, on the other Forum I "Work on" my motto is "I do not give easily" and I don't.

It is much easier being able to converse with you in real time, rather that very late at night, you must have got up pretty early this morning.

[andycl]

Yes, real time conversation is much more effective. I do appreciate your patience. You don't need the actual files just the complete file name?

[andycl]

Also, let's say its the first time the program is opened and a user will copy files. That would mean there are not any files in sDest. So I think that the problem when copying the files for the first time. The files in the list will contain the Array strings. But if this is the frist time files are being copied, sDest will be empty. It seems that when sDest is empty Checkfile(i) thinks that TESTMAIN is in the directory when really its the first time TESTMAIN is being copied. So it tries to append and when it gets to FileFound it stops because there is not a file in sDest to append to. I may have explained that horribly so just say so.

[OBP]

I had not considered "first use" as I assumed it was already a mature system.
But we can build some checks in to prevent that.
No I don't really need actual files, just Paths to where your data is stored, I will then duplicate that as near as possible to test the code.

[andycl]

Ok, I can provide that. I think its the first use situation that is error source. If the built in checks do not resolve I can provide the paths of data storage.

[OBP]

Here is the new code that allows for the Master not being in the Folder already.


Dim CheckFile, FileFound As String, i As Integer, datastring As String
Dim sDest As String, firstfile As String, filcopied As Integer
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
'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
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
filecopied = 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) & "*.*")
If FileFound = "" Then
fileinarray = 0
Exit For
End If
GoSub Appendsub
'Open sSourceFile For Input As SourceFileNum
Do Until FileFound = "" ' Start the loop.
ctr = ctr + 1
FileFound = Dir() ' Getting next entry.
If FileFound <> "" Then GoSub Appendsub
Loop
Exit For
End If
Next i
If fileinarray = 0 Then
If filecopied = 0 Then
MsgBox result & " - file is not in array copy it"
FileCopy ctlList.ItemData(varItem), sDest & FileName
filecopied = 1
End If
End If
Next varItem

Exit Sub
errorcatch:
Close #2
Close #1
MsgBox "Error - " & Err.Description


Appendsub:
MsgBox "current file appending to - " & FileFound & " from - " & FileName
Open firstfile For Input As #1
Open sDest & FileFound For Append As #2
Do Until EOF(1)
Line Input #1, datastring
Print #2, datastring
Loop
Close #1
Close #2
MsgBox "data transferred to " & FileFound
Return


It is this part that does the check

If FileFound = "" Then
fileinarray = 0
Exit For
End If

[andycl]

From a quick check, that seems have worked the way I was hoping. I'll do some more testing to verify but from a fast check it looks great. I am going to need to insert a few commas between the last field of the master file and where the new file appended data starts. I've looked online but can't find anything that shows me how to do that. Adding this hopefully gets this part finished.

[OBP]

This line of code in the subroutine
Print #2, datastring

print the data to the file

So you could use
datastring = ",,,,,"
and
Print #2, datastring
to put it in the file before going through the #1 file.
So it would become

Appendsub:
MsgBox "current file appending to - " & FileFound & " from - " & FileName
Open firstfile For Input As #1
Open sDest & FileFound For Append As #2
datastring = ",,,,,"
Print #2, datastring
Do Until EOF(1)
Line Input #1, datastring
Print #2, datastring
Loop
Close #1
Close #2
MsgBox "data transferred to " & FileFound
Return

[andycl]

The commas will align the data in the approprioate fields for the import.

[OBP]

Andy, how about
datastring = ",,,,,Data added " * Date()
that will tell anyone looking at the data when each append was made.

[OBP]

I am not sure what this means
"The commas will align the data in the approprioate fields for the import."



Do you mean importing in to Access or Excel?

[andycl]

Sorry for the confusion. Yeah after all the moving and copying files, it's time to import the master file to Access. This is where we both were saying Access should have been used to let Access handle the importing/appending for each file type.

By addidng the 3 commas, it separated the first field of the appended data to the correct field of the table and set all subsequent fields correctly.

[OBP]

So, can you explain the sequence of events?
Copy file or append to Master
Import master to Access
Copy fille or append to Master
Import master to Access again?

[andycl]

Evidently all of the copy file and append file will have to happen prior to importing to Access. After the master is imported to Access, no more appending will occur. So I guess if something is missed to bad so sad. You'll have to start another DB for any other data. Not my show.

So the header rows of the files are used as field names for the table when they are imported to access. When you append a file, it appends everything obviously including the header rows. Creating a file that header row of the intial master file a. The file is structured as header rows, all the data, then after an append, header rows, and all the data. When I import the appended master file, Access will import to the last data set before the header rows are repeated. I've got to omit the header rows when appending to the master file. Didn't think that would happen because when I was importing a file at a time that did not occur.

[OBP]

You could allow the Header rows to be inported and then use a Delete query to remove them.
I think you can specify whether or not a file has header rows or not.
But I also have a VBA import routine that could probably be modified to ignore them during import.
Are you using VBA, docmdimport or a Data Import to bring in the data to Access?

[andycl]

If that was confusing, i'm trying to say omit the header rows of an append file to the master. The master already has the header rows.

[OBP]

OK, of course that is possible, the code that I provided on your previous post skips the first line of code to avoid the headings.
Have you forgotten it already? :)

ps going out for a while so won't be able to answer until I get back.

[andycl]

We've had a few post going back and forth so cut me some slack. jk
I'll go back and look. Is it just to remove the first line? Because they are all different. Some 2 lines, some 3, and some not the complete line and the data set begins.

[andycl]

You're referring to the import and not omitting for an append, correct?

[OBP]

It was the more than 265 fields question, that was you wasn't it?
It was this line of code
If reccount > 1 Then ' process if not heading record
where we were using reccount, in this one we aren't currently using a counter be we could.
Or as I said you can do it in the import routine or even after the data has been imported by deleting any offending records.

[OBP]

Hey what do mean cut you some slack, I am the one who is 70 with fading brain cells. :devil2:

[SamT]

:sick:
My brain hurts

[andycl]

I was joking around anyways. 70, I would have never know.

Just so I'm putting the problem statement out there. Master file is in the directory and it has it's header rows for the file. A second file is appended to master file and it also brings the header rows with it. When trying to import using the routine you are referring to, it imports and stops where the appended file header row begins. So the newly appended
master file looks like this: Header rows - initial master file data - Header rows (from appending file) - data (from appending file).

I can see how using what was earlier used to overlook the headers but not sure how to go about overlooking both sets of headers rows as they will be downstream in the file and not the first rows of the file. And the second set of headers will be located differently for each of the five file types. Thats why I was hoping to omit the header rows from the append file in during that routine.

I know this is all crazy.

[OBP]

Sam, I am currently working on three separate databases, it keeps the old brain cells ticking over, I also post on Tech Guys,where the other 2 are, which used to be a sister forum to VBAX, I have 19000 posts over there.

[OBP]

The routine that I wrote for your previous post ommits the header rows during import and the same method can easily be used to ommit the header during the Append operation.
The only problem is if it is over more than one input "line", unless you know it in advance if it then you can ommit as many rows as necessary.

[OBP]

Andy, this should do it
Dim sDest As String, firstfile As String, filecopied As Integer, reccount As Integer
and

Appendsub:
MsgBox "current file appending to - " & FileFound & " from - " & FileName
reccount = 0
Open firstfile For Input As #1
Open sDest & FileFound For Append As #2
Do Until EOF(1)
Line Input #1, datastring
If reccount > 1 Then
Print #2, datastring
End If
reccount = reccount + 1
Loop
Close #1
Close #2
MsgBox "data transferred to " & FileFound
Return

This line could be
If reccount > 1 Then
If reccount >2 Then
or
If reccount > 3 Then

[SamT]

This is an interesting, if easy, problem and I would like to work on it, but I am not going to read the previous 97 posts to figure out the data structure of Andy's situation.

I don't even know if my speculation in that regard, in post #60 (http://www.vbaexpress.com/forum/showthread.php?59949-File-Dialog-Browse-Save-Append&p=365579&viewfull=1#post365579), was accurate.

Don't let your chest swell too much, I am 67 and this is my hobby too, also to keep my b.b.b.brain active when it is too hot outside to build stuff. If you go back far enough, you can find my name on a few other forums, even had my own, but it was getting too much like work, so I killed mine and dropped out of the rest and only work on the extremely easy and the most interesting projects here.

[andycl]

So do the other lines need to be added? I added what OBP posted and the data append actually stopped working. It ran the procedure but didn't append any data.

SamT your insight and comments are appreciated as well.

[OBP]

Andy that is very odd, because I have tested it and it works for me, it just doesn't append the first line.

[OBP]

Sam, are you also self taught from the 80s?

[SamT]

yes and no, but from the 70's. Military.

I didn't pick up VBA until ca2004 when I rewrote the Excel Object Model and help files as a learning experience. I am still debating on upgrading to Win 7 and Excel 2007. It's getting boring. I am playing with CLISP at the moment, might set up an LFS Machine for that.

Or... I might grow Pot and go back to Natural Philosophy and finish my treatise on Quantum Gravity. But that would mean refreshing my math skills. Bleh.

[andycl]

This is odd then. I have checked everything I know to check. The routine runs start to finish and the Msgbox gives the indication data was transferred. But there is no data being transferred. If I remove the reccount variable (back to what it was previously), it will append the data. I'm not sure what gives if OBP has it running. And I did declare reccount as an integer.


Appendsub: MsgBox "current file appending to - " & FileFound & " from - " & FileName
reccount = 0
Open firstfile For Input As #1
Open sDest & FileFound For Append As #2
datastring = ",,,"
Do Until EOF(1)
Line Input #1, datastring
If reccount > 1 Then
Print #2, datastring
End If
reccount = reccount + 1
Loop
Close #1
Close #2
MsgBox "data transferred to " & FileFound
Return

[andycl]

Any ideas? Also, before appending if I want to add a choice for the user to append or save in different location. Can I use the vb yes no msgbox and the msofiledialogsaveas? I've read where save as doesn't actually save the file.

[OBP]

Been busy today cleaning my son's house as he is on holiday and I am off out to Poker in a minute.
How many lines of code were being tranferred before you added reccount in to the subroutine?
Whe I get home I will post my version of the database.

[andycl]

Individual lines in the file? Vary from 50 to a couple hundred lines. If that's not what you're asking sorry, just let me know. I do need to add the option save the file to a different location if the user does not want to append the new file to the master. I've got to use file dialog and probably a yes no msgbox. Trying to add that in while we figure out the reason the append stopped after adding reccount.

Good luck playing cards.

[SamT]

IIRC, you said that some of the files have more than 1 header line. How can you tell by looking at the file which are header lines?

[andycl]

I guess my interpration of a header line is what is being used as a field name of database table when the file is imported to Access. Correct me if that's off base. I say the files consist of more than 1 header line because if you open the file there are 2 and 3 header lines and the actual data starts after the last header name. That can be mid line of the third line of the file. My definitions could be off and we are talking about different things. But essentially what I am trying to do is when a file is appending to the master file omit the header lines because those header lines are already in the master file. They shouldn't be duplicated.

[andycl]

I said all that and didn't answer your question. I know the file structure for the files that being worked with. The header lines will remain the same each time a file is exported.

[OBP]

Here it is.
Use the top button for the code.

[SamT]

if you open the file there are 2 and 3 header lines and the actual data starts after the last header name
Hmmmmm. What if you open the file in Notepad and turn off Word Wrap?
I'll buy you a cup of coffee the next time we meet if you see more than one header line then.

BTW, your definition is correct.

[andycl]

With word wrap off, the middle of the third line is where the last header name is and the data starts. This particular file has over 200 field names. But it is not one continuous line even with word wrap off. With word wrap on its many more lines. I'm going to put the files up here. Because even with the db OBP posted the append routine does nothing when I added reccount. It doesn't move any of the data to the master file. That makes no sense.

[OBP]

Andy we know from your other code where we split the file into 2 tables that the files use the vblf as it's record seperator for the header, the same as rest of the data, so as the VBA code also uses the vblf to separate the lines there should be no need to skip more than 1 line input.
I have retested my code this morning and it is still working great, doing exactly as expected, I just don't see how skipping the line input for 1 line affects all the rest.
Have you tried adding a msgbox to display what it says it is appending?


Appendsub:
MsgBox "current file appending to - " & FileFound & " from - " & FileName
reccount = 0
Open firstfile For Input As #1
Open sDest & FileFound For Append As #2
Do Until EOF(1)
Line Input #1, datastring
If reccount > 1 Then
Print #2, datastring
msgbox datastring
End If
reccount = reccount + 1
Loop
Close #1
Close #2
MsgBox "data transferred to " & FileFound
Return

[OBP]

Update, using your original data file which was for importing 265+ fields I have reproduced your problem, ie it does NOT update when both files have Headers.
I will now try and establish how to overcome the problem.

[OBP]

Sometimes we can be as dumb as hell.
We established in your first post that because of the vblf that your files use the inputline imports the WHOLE file in one record, how could we have forgotten?
So it works for my files because they DO input 1 line at a time and not for your files because they don't, that was why we went to importing the Characters one a time and decide where the Record delimiters were.
So it look like we will have to do the same here.

Although I don't really understand what would be detrimental about having more header records as they can easily be deleted in other programs.

[andycl]

So we need to do Input (1, #1) or something like that.

[OBP]

Yes I will get on it now that I have had Lunch.
I won't bother with other files until I have this working.
The save to another folder could be achieved using the same copy code as now and then "kill" the original file.

This is the sort of code that you need for the decision process, obviously with different wording and actions


Dim response
response = MsgBox("Are You Sure You Want to Quit Access", vbYesNo + vbExclamation + vbDefaultButton2)
If response = vbNo Then Exit Sub ' User chose No.

DoCmd.Quit

[andycl]

Ok good deal. Thanks for help

[SamT]

With word wrap off, the middle of the third line is where the last header name is and the data starts. This particular file has over 200 field names. But it is not one continuous line even with word wrap off. With word wrap on its many more lines. I'm going to put the files up here. Because even with the db OBP posted the append routine does nothing when I added reccount. It doesn't move any of the data to the master file. That makes no sense.
That means that even the Records are probably spread across multiple lines.

I think that the files must be read by field count, rather than by lines.

Something like

'first, Replace linefeeds with separator
TmpArray = Split(FileText, Separator)

For j = 0 to UBound(TmpArray) Step FieldCount
If TmpArray(j) = FirstHeaderName then GoTo J_Next 'Skips Headers
Write(TmpArray(j to j+FieldCount) to TmpFile
Write Linefeed Character toTmpFile
J_Next:
Next

Append TmpFile to Master

[OBP]

OK, checked and working.
I will post the code in full.


Dim CheckFile, FileFound As String, i As Integer, datastring As String, xchar As String
Dim sDest As String, firstfile As String, filcopied As Integer, reccount As Integer
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
'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
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
filecopied = 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) & "*.*")
If FileFound = "" Then
fileinarray = 0
Exit For
End If
GoSub Appendsub
'Open sSourceFile For Input As SourceFileNum
Do Until FileFound = "" ' Start the loop.
ctr = ctr + 1
FileFound = Dir() ' Getting next entry.
If FileFound <> "" Then GoSub Appendsub
Loop
Exit For
End If
Next i
If fileinarray = 0 Then
If filecopied = 0 Then
MsgBox result & " - file is not in array copy it"
FileCopy ctlList.ItemData(varItem), sDest & FileName
filecopied = 1
End If
End If
Next varItem

Exit Sub
errorcatch:
Close #2
Close #1
MsgBox "Error - " & Err.Description

Appendsub:
MsgBox "current file appending to - " & FileFound & " from - " & FileName
reccount = 0
datastring = ""
Open firstfile For Input As #1
Open sDest & FileFound For Append As #2
Do Until EOF(1)
xchar = Input(1, #1)
If xchar = vbLf Then reccount = reccount + 1
If reccount > 0 Then
datastring = datastring & xchar
End If
Loop
'MsgBox datastring
Print #2, datastring
Close #1
Close #2
MsgBox "data transferred to " & FileFound
Return

[OBP]

If you have any files that will exceed the 65,000 string character limit we can instead print the datastring after every vbLf, ie one line at a time.

[OBP]

Sam, the problem is that the Data and the headings are in one RECORD, but the record delimiter is a vbLf instead vbCf, so VBA thinks the whole file is just one record and using LineInput it imports the whole file as 1 record.
Andy's prvious post deals with it in detail.
So I basically used the same method to read the data one character at a time ans use it to create a string to write to the Master file.

[OBP]

Andy, humble apologies, do you remember the very first database I sent on the question that had the "Browsing" routine in it using File Scripting.
Well, File Scripting reads your text files one line at a time (where open file #1 can't).

So to read it one line at a time all it takes is

Dim oFSO As New FileSystemObject
Dim oFS
Set oFS = oFSO.OpenTextFile("C:\Users\A C\Downloads\TestFolder\TESTMAIN000.TXT")

Do Until oFS.AtEndOfStream
sText = oFS.ReadLine
MsgBox sText
Loop



Practically all of the work that we have done over the last 2 questions could have been easily handled using FSO.

[SamT]

Notepad has a HardCoded limit of 255 characters per line, So that Notepad test does not return a true result of where the actual line of text ends.

Most editors have some arbitrfary line length, some are dependent on the machine it runs on, some,like Notepad, are hard coded.

Sam, the problem is that the Data and the headings are in one RECORD, but the record delimiter is a vbLf instead vbCf, so ... whole file is just one record and using LineInput it imports the whole file as 1 record.

Not so.
The File is a Record Set
One actual line in the file is a Record. See First sentence above.
The Record Separator is vbLf
One Header Name is one Field.
The Field separator is the character(s) between Header Names

What is/are the character(s) between two Header Names? That is the Separator I am talking about

Try this

tmp1 =Split(File, vbLf)
tmp2 = Split(tmp1(0), "Separator_I_am_talking_about")
MsgBox UBound(tmp2)
MsgBox "the number of Fields in a Record is: " & UBound(tmp2) + 1
For i = 0 to UBound(tmp2)
MsgBox tmp2(i)
next

7Zip one of the shortest files and upload it. I will open it with UltraEdit, which has no arbitrary line length.
Please use 7zip, since I know that it does not change the file when it compresses it like some do.

Gotta go build something, be back when it's too hot outside. :)

[OBP]

Sam, just go and read Andy's previous post here
http://www.vbaexpress.com/forum/showthread.php?20548-Solved-How-to-import-a-text-file-with-more-than-255-fields-into-Access.

We went right through this and Jonh pointed out that it uses vbLf which is the record seperator and the comma is the field seperator, but it is now completely immaterial, as FSO can read the file correctly where as Opening the file and using LineInput does not.

[andycl]

OBP, that seems to have got it. I think I had a typo that I didnt catch until now. Thats my bad. But this is working great. The routine does not bring the header rows during data transfer of appending a file. I'll run through a few files for testing but from a quick look this seems to have done the trick. I also have the vbYesNo box working like I want. Yes to append and No to save file elsewhere. As of now, I don't believe there will be files with 65,000 character string. But it may be something I'm not aware of.

Thanks as always for the help.

[OBP]

Andy, after you have shown them it working, it would be worth spending the time going over to FSO, it would make the programming much shorter and simpler for both importing and appending.

[andycl]

Would the program crash if there were character strings exceeding 65,000? All files are appending correctly (without header rows) except one. It is the largest file size of the files. I don’t know if that has anything to do with it or not but the program locks up on that one file when appending.

[OBP]

It is possible, I will look at an alternative.

[andycl]

Ok good deal. The file structure isn't different but looking there may be some strings in that 65,000 range

[OBP]

Andy, replace the subroutine with this and test it.


Appendsub:
MsgBox "current file appending to - " & FileFound & " from - " & FileName
reccount = 0
datastring = ""
Open firstfile For Input As #1
Open sDest & FileFound For Append As #2
Do Until EOF(1)
xchar = Input(1, #1)
If xchar = vbLf Then
reccount = reccount + 1
datastring = datastring & xchar
If reccount > 1 Then
Print #2, datastring
datastring = ""
End If
End If
If reccount > 1 Then datastring = datastring & xchar
Loop
'MsgBox datastring
'Print #2, datastring
Close #1
Close #2
MsgBox "data transferred to " & FileFound
Return

I still think FSO is the way to go, but try this one for now.

[SamT]

Hey, you got it working. :beerchug:

And I agree that the FSO is the way to go

[andycl]

So it is the file size that causes it lock up. When I reduced the file down, it worked as expected.

[andycl]

I'll have to look at the FSO option. Maybe it handles larger files.

[OBP]

When you say "So it is the file size that causes it lock up. When I reduced the file down, it worked as expected.", is that with the newest code that I posted or the original code?
As the newest code is using a string that is only the size of 1 record.

[OBP]

I have already posted the code for reading the data using FSO, the only thing you have to do is set the VBA library References to
Microsoft Scripting Runtime.

[andycl]

It was my own oversight. I didnt change this line If reccount > 0 Then to If reccount > 1 Then
Works the way it should. So is it still reading each character or the entire file as a record?

[andycl]

Is the FSO code you're talking about in another thread?

[OBP]

It was my own oversight. I didnt change this line If reccount > 0 Then to If reccount > 1 Then
Works the way it should. So is it still reading each character or the entire file as a record?

The latest version reads each character to build up a Record (datastring) ie uses vblf to end adding to it and append it to the Master file.
It then clears the datastring to build the next record.

[OBP]

Is the FSO code you're talking about in another thread?


No it is on this page in Post #124.

I made quite a few posts during that period.

[SamT]

You should click the Star under OBP's name and give him some Reputation, he went above and beyond for you.

[andycl]

I'll start looking at the FSO scripting you posted earlier. So the benefit is that it reads the file 1 line at a time. I see the limitations open file has and far less coding.

[andycl]

OBP has been great. No doubt there. I have tried show my gratitude throughout the thread. But yes, I will do it the formal way for this site via the reputation.

[andycl]

After putting this through a test, it performs just fine. When appending files, it will create blank rows in the master file. Any reason that happens? I think that can corrected when importing the file to an Access table by running a delete rows query.

Also, there may be instances when this application is ran by Access Runtime. I never read anything where any of the VBA reference libraries wouldn't work correctly if using Runtime. Is that misguided?

[OBP]

I will take a look at the blank line problem, but it may be fixed by using FSO.
I have never used Runtime, so I am not sure.

[andycl]

Hey again, Im back to working on the import routine. Theses last couple of posts are where I picked up. I have tried everything to get this file to import. I get the error message "item cannot be found in the collection corresponding to the requested normal or ordinal." Also the error message box in the VBA shows reccount = 0. I have made sure the table matches the file and cut the table size down as you suggested in the last post. The file is not any different from the other routine we worked on in the past routine. Only differences are the data in it and the field size. I'm out of good ideas to try. Maybe the VBA I have revised is incorrect. I you have a chance to look at it, some feedback would be appreciated.


Dim i As Integer Do Until EOF(1)
xchar = Input(1, #1)
If xchar = vbLf Then
reccount = reccount + 1
fieldcount = 1
reclength = 0
If reccount > 1 Then
With rst
.AddNew
For i = 1 To 199
.Fields(i) = fieldstring(i)
fieldstring(i) = ""
Next i
.Update
End With

With rst2
.AddNew
.Fields(72) = keyfield
For i = 1 To maxfields - 199
.Fields(i) = fieldstring(i + 199)
fieldstring(i + 199) = ""
Next i
.Update
End With

With rst
.AddNew
.Fields(199) = keyfield
.Update
End With
End If
Else \
If reccount > 0 Then
x = x + 1
If xchar = Chr(34) Then
If first = 0 Then
first = 1
Else
first = 0
End If
End If

[OBP]

Andy, first of all what file are you trying to import from, I have just tested the code which was developed for TESTMAIN, which works OK, on the other 2 files that you sent me and neither of them complete the input.
SECTESTPRIMARY5736298.1500919251120784620 - Copy.dat fails due to key violations, because the fields after the first 2 are completely blank, so it tries to put 2 blanks in the Location key field, which creates a duplicate.
Also SECTESTPRIMARY5736298.1500919251120784620 - Copy has completely different and far fewer Field than the other 2, so should obviously be in a different table.

SECTVAIELOCMAINNUC5006.1500915498085621484uSE stops due to a field being too large, I have overcome that by clipping the field to 255 characters, but the question arises of why that particular field comes out over 255 characters. I will have a closer look at it.

I have also realised that because of the data structure of the original files FSO may have the same problem that I had to overcome, ie the use of "," in a field which creates an extra field, shifting everything to the right one field, I will check that out too.

[OBP]

OK, I was right the FSO has exactly the same problem with the comma within quotes, causing too many fields to be created, so that is going to cause a major problem if we tried to use it for importing the data.
It should still work OK for Appending though.

[OBP]

SECTVAIELOCMAINNUC5006.1500915498085621484uSE has 2 fields that are too large, one of them over 300 characters, I think they are also the ones with quotes and commas which look as if they have concatenated 2 fields.
I have set both fields to Memo and they import OK.
You could of course set most of the fields with real text data in them to memo to ensure that they never exceed the 255 character limit.

[andycl]

Yes, when I set those two fields two memo the import worked just fine. So it the TESTPRIMARY that is giving me the difficulties. As far as creating extra fields, that should be something I can take care of during the import by running a delete query. At least I think it should.

[SamT]

also see thread: How to import a text file with more than 255 fields into Access (http://www.vbaexpress.com/forum/showthread.php?20548-How-to-import-a-text-file-with-more-than-255-fields-into-Access)

[OBP]

Yes, when I set those two fields two memo the import worked just fine. So it the TESTPRIMARY that is giving me the difficulties. As far as creating extra fields, that should be something I can take care of during the import by running a delete query. At least I think it should.

Unfortunately you can't do anything if it creates too many fields as it displaces the data.
It just means that you will have stay with the single character input that we have developed here and FSO is out for Importing.

[OBP]

The new version of EXPTESTPRIMARY1808398.1491995706329461740 that you sent me has a problem for you, it has extra field names not in the current TableImport2.
In fact it looks like an extra 24 tables.
ALNVALUE,ASSETATTRSD,LS_CHANGEBY,LOCATIONSPEC.CHANGEDATE,CLASSSTRUCTURESD_L OCSPEC,DISPLAYSEQUENCE,INHERITEDFROMITEM,ITEMSPECVALCHANGED,LINKEDTOATTRIBU TE,LINKEDTOSECTION,LOCATIONSPECSD,LS01,LS02,LS03,LS04,LS05,MANDATORY,MEASUR EUNITSD,NUMVALUE,LS_ORGSD,SECTION,TABLEVALUE
That means that TestPrimary needs it's own table, or you extend the curent one.

LOCATIONSPEC.CHANGEDATE may give you headache as full stops arenot allowed in Access Field Names.

Can I recommend to you FreeFileViewer, it is great for looking at files and finding things with it's search.

[andycl]

Ok understood. I can't get around the error message I posted earlier for the TESTPRIMARY. I'm using the same routine that we did for the other file. The keyfield is in the second table so I moved it to add keyfield after rst 2 is processed.

When I spoke of adding extra rows it was in regards to the append procedure. Even if it is they should be able to be delete by a query after that are imported. I crossed up threads into one comment and made that confusing.

[andycl]

You are correct. I have created two tables for the TESTPRIMARY import that are independent and only for TESTPRIAMRY.

rst.Open "tblImportTESTPRIMARY1", CurrentProject.Connection, adOpenDynamic, adLockOptimistic
rst2.Open "tblImportTESTPRIMARY2", CurrentProject.Connection, adOpenDynamic, adLockOptimistic

I didn't post all the code. Just the record adding piece. Sorry for not giving the complete picture.

[OBP]

You are correct. I have created two tables for the TESTPRIMARY import that are independent and only for TESTPRIAMRY.

rst.Open "tblImportTESTPRIMARY1", CurrentProject.Connection, adOpenDynamic, adLockOptimistic
rst2.Open "tblImportTESTPRIMARY2", CurrentProject.Connection, adOpenDynamic, adLockOptimistic


Does the tblImportTESTPRIMARY2 have the extra 24 fields in it?
I can post a list of all the fields in the file if you want, because they must match the number in table.
I didn't post all the code. Just the record adding piece. Sorry for not giving the complete picture.

[andycl]

I compared the fields in the file against the tables and they are accounted for. The primary key is in table 2 and it needs to be added to table 1 to establish a joining field.. Do I manually add the primary key field in table 1 or does the code take care of that? Also, RecordID was added for the other file during the import. Do I add that manually to the table or it is automated as well?

I have 198 fields in the first table. There will be 199 if I am to manually add the primary key. Table 2 has 84 fields with field 73 being the primary key.

[OBP]

I compared the fields in the file against the tables and they are accounted for. The primary key is in table 2 and it needs to be added to table 1 to establish a joining field.. Do I manually add the primary key field in table 1 or does the code take care of that? Also, RecordID was added for the other file during the import. Do I add that manually to the table or it is automated as well?

I have 198 fields in the first table. There will be 199 if I am to manually add the primary key. Table 2 has 84 fields with field 73 being the primary key.

You have to manually add the KeyID Field to Table 1, the code should then be modified to update that field when the field count gets to 199 during import.

I suggest that when you have finished the table and code changes that you send me a copy so that we will both talking about the same things in future.

[OBP]

To overcome adding blank lines replace the Subroutine code with this one


Appendsub:
MsgBox "current file appending to - " & FileFound & " from - " & FileName
reccount = 0
datastring = ""
Open firstfile For Input As #1
Open sDest & FileFound For Append As #2
Do Until EOF(1)
xchar = Input(1, #1)
If xchar = vbLf Then
reccount = reccount + 1
datastring = datastring & xchar
If reccount > 1 And Len(datastring) > 20 Then
'MsgBox Len(datastring) & Len(datastring) > 100
Print #2, datastring
datastring = ""
End If
End If
If reccount > 1 Then datastring = datastring & xchar
Loop
'MsgBox datastring
'Print #2, datastring
Close #1
Close #2
MsgBox "data transferred to " & FileFound
Return


This is the part that controls it, the 20 could probably be reduced to 5 or 2 and still work OK
And Len(datastring) > 20

[SamT]

Hey Guys

I closed the other thread, but first I moved all of yesterday's post from it into this one.
You maybe want to go back tru this thread and reread all of yesterday's post in here.
Note that Moving posts like this merges them in chronological order.
Having a conversation in two threads is just too confusing and is not a good thing.
Sorry and thanks for putting up with me.
Sam

[andycl]

That's going to be huge eliminating any unnecessary rows created in the append. I'll add the one line and give it a go as I get a chance to. Appreciate OBP