I need assistance because i cannot debug an error 5-Invalid Procedure Call or Argument.
I have two text files and I am opening them both up for reading and writing. The reading file is comparing the files line by line, if there is a difference, the writing file scripting object is writing that line to a new file. However, my code keeps getting caught up on one file, specifically at
Code:
fileNewWriting.WriteLine (lineFileNew)
I found somewhere it may have to do with unicode/ascii. I have tried all the "Tristates" optional parameters in the OpenTextFile method but none work. The files may have both unicode and ascii I am not sure and that is why I played around that option. My code is long so i tried to trim it down by removing my forms progress bar.
Code:
Private Sub Read_Click()
'All variables for the old file i will need to read lines
Dim fileOld As String, fileOldReading As TextStream, lineFileOld As String
Dim FilePathOld As String, FolderOld As String, fsoFolderOld As Scripting.Folder
'All variables for the new file i will need to read lines
Dim fileNew As String, fileNewReading As TextStream, lineFileNew As String
Dim filePathNew As String, FolderNew As String, fsoFolderNew As Scripting.Folder
'All vairables I will need to Create my new data file
Dim fileCreate As String, fileNewWriting As TextStream
'Other needed variables
Dim fso As Scripting.FileSystemObject
Dim fsoFile As Scripting.File
Dim lineCount As Long
Dim newDir As String
Dim intFileCounter As Integer
On Error GoTo ErrHandler
'A place to put my old files for backup
newDir = "C:\My Documents\" & Format(Date, "yyyymmdd") & "\"
'How I access the files and folders
Set fso = CreateObject("Scripting.FileSystemObject")
'A directory to move the original files
If Not fso.FolderExists(newDir) Then MkDir (newDir)
'This is where all the files are from the last upload
FolderOld = "C:\LastUpload\"
'This is where the files I get from newly acquired files
FolderNew = "C:\NewData\"
fileOld = Dir(FolderOld & "*.*")
'This variable is to make sure all files have been copied over
'total of 61
intFileCounter = 1
'I need to loop through all files in the new folder
Set fsoFolderNew = fso.GetFolder(FolderNew)
Do While fileOld <> ""
'For each new file find a similar old file name
For Each fsoFile In fsoFolderNew.Files
If fileOld = fsoFile.Name Then
filePathNew = fsoFile.Path
Exit For
End If
'There are 61 files, if it get to the end then the file was not found
If intFileCounter = 61 Then
MsgBox "The old file has not found a similar new file. Please review all files and retry", vbOKOnly _
, "No Match"
GoTo Finish
End If
Next
'Get the file names to use
FilePathOld = FolderOld & fileOld
'Open the old and new files for reading
Set fileOldReading = fso.OpenTextFile(FilePathOld, ForReading)
Set fileNewReading = fso.OpenTextFile(filePathNew, ForReading)
'Read the file until the end of the second file
'The second file should have more data than the original
Do Until fileNewReading.AtEndOfStream
'A string variable to hold new file line
lineFileNew = fileNewReading.ReadLine
'A string variable to hold old file line
lineFileOld = fileOldReading.ReadLine
'If they are not equal
If Trim(lineFileOld) <> Trim(lineFileNew) Then
If Not fso.FolderExists(FolderOld & "NewUploads\") Then MkDir (FolderOld & "NewUploads\")
fileCreate = FolderOld & "NewUploads\" & fileOld
'If the file that we are adding to is not there
'Used to set writing and appending
If Not fso.FileExists(fileCreate) Then
'New text file for writing/appending
fso.CreateTextFile (fileCreate)
'Initialize the write
Set fileNewWriting = fso.OpenTextFile(fileCreate, ForWriting)
'Write the new data when files are compared
fileNewWriting.WriteLine (lineFileNew)
fileNewWriting.Close
Set fileNewWriting = Nothing
'After this point data is appended
Set fileNewWriting = fso.OpenTextFile(fileCreate, ForAppending)
Else
fileNewWriting.WriteLine (lineFileNew)
End If
End If
Loop
fileOldReading.Close
fileNewReading.Close
Set fsoFile = Nothing
'Move to the next file
fileOld = Dir
Loop
Set fsoFolderOld = fso.GetFolder(FolderOld)
'Moves all the old files to the new date directory
For Each fsoFile In fsoFolderOld.Files
fsoFile.Move newDir
Next
Set fsoFile = Nothing
'Moves all the new files to the original directory
For Each fsoFile In fsoFolderNew.Files
fsoFile.Move FolderOld
Next fsoFile
Finish:
Set fso = Nothing
Set fsoFile = Nothing
Set fsoFolderNew = Nothing
Set fileOldReading = Nothing
Set fileNewReading = Nothing
Set fileNewWriting = Nothing
Set fsoFolderOld = Nothing
Exit Sub
ErrHandler:
'If the old file has reached the end, just sent it to null
'I want the rest of the new file so it will append
If Err = 62 Then
lineFileOld = ""
Resume Next
End If
MsgBox Err & "-" & Err.Description
Resume Finish
End Sub