Background: I am extracting a file that is saved in a SQL database using an ADODB connection. One column of the database is the filename, including the file extension, and another is the actual contents of the file as a hex stream. I would like to save this file and open it.
Problem: This works fine with .pdf files. However, with .png files there is an error- the file is corrupted when I try to open it. I used a hex editor (HxD) and noticed that there were excess values. If I remove these the file opens fine. The hex stream should begin with the "per mille" character (Chr(137) in excel) in order for the file to open. I have not found a way to edit the hex stream in excel without converting it to characters.
The .png file opens with no problem when I take out the first few characters using a hex editor so that the file begins with:
‰PNG
Or the equivalent in hex code:
89 50 4E 47
The excess characters are
ÿþ
Or the equivalent in hex code:
FF FE
(I am trying to remove these). These characters are in the saved file even when I remove 4 characters from the text string using
Content = Right(Content, Len(Content) - 4)
It's almost like they automatically get added before the string when I save the file.
Code:
Calling the save to file function, where Content is the file content and Name is the filename:
Call StringToTextFile("C:\", rst![Content], rst![Name])
The function is below:
Public Sub StringToTextFile(ByVal directory As String, ByVal Content As String, ByVal filename As String)
'Requires reference to scrrun.dll
Dim fso As Scripting.FileSystemObject
Dim ts As Scripting.TextStream
Set fso = New Scripting.FileSystemObject
If Right(filename, 4) = ".png" Then 'recognizing the .png file
'Content = CByte(Chr(137)) & Right(Content, Len(Content)) 'unsuccessful attempt at inserting "per mille" character
'iret = InStr(0, Chr(137), Content, vbBinaryCompare) 'unsuccessful attempt at finding the "per mille" character in the content
End If
Set ts = fso.CreateTextFile(directory & filename, True, True)
ts.Write Content
ts.Close
Dim myShell As Object
Set myShell = CreateObject("WScript.Shell")
myShell.Run directory & filename 'Open the saved file
End Sub
When I try to insert the "per mille" character using Chr(137) it just shows a blank space in the hex editor.
Any help is appreciated!
This seems to be a similar discussion, but I am unsure how to apply this to my case: excel-vba-hex-to-ascii-error
