changes content of file

Copper Contributor

I made a script to change the content of all files in a directory.
This script worked fine and changed the content that I had selected.
These files are .tpr and are used in a another program as settings.
When after the execution, running the files I noticed that some data was missing.
Viewing the files with Notepad2 I noticed that two rows that should have not been affected by the script was changed as to start end end with " this was why the program was no longer working.
Opening this file again in excel and the " are gone.
Saving the file manually in excel and closing it, and then again view it in Notepad and the " are gone.
However if I try to just open and save the file using a macro.
Sub AllFiles()
Dim Bibliotek As String
Dim Filnamn As String
Dim ArbetsBok As Workbook

Application.ScreenUpdating = False

Bibliotek = "R:\DELTA\Robban\Temp\Filer att testa på\" 'change to suit
If Right(Bibliotek, 1) <> "\" Then Bibliotek = folderPath + "\"
Filnamn = Dir(Bibliotek & "*.tpr")
Do While Filnamn <> ""
Set ArbetsBok = Workbooks.Open(Bibliotek & Filnamn)
Filnamn = Dir
End Sub
If I run the above, the " is there and also added if I first manually saves the file which removes the "
How the data is shown in excel


How the data is shown in notepad2 after using ActiveWorkbook.Save


And as said before if I once again open the file in excel all the " is gone.
I'm at loss how to solve this. There are thousands of files and to manually open and save those would take long.


1 Reply

I have done some further testing.
Obviously it is the Cells that contain a "," that gets the "...." when using ActiveWorkbook.Save
Really strange that just opening and saving the file manually won't do this. Instead the "" is not shown upon opening and thus not saved into the file.
So obviously the manual save function and the ActiveWorkbook.Save don't work in the same way even though ActiveWorkbook.Save is what you get when recording the manual save function.