Scribd
Upload
6 views2 pages

Detect File Encoding and Export PDF

The document contains two private functions in VBA: DetectEncoding, which identifies the text file's encoding (UTF-8, UTF-16, or ANSI) based on the Byte Order Mark (BOM), and ReadTextFile, which reads the content of a text file using the detected encoding. Additionally, there is a subroutine FormatAndExportSheets that formats the header of each worksheet in the workbook, applies number formatting to numeric cells, and exports each sheet as a PDF. The code demonstrates file handling and formatting techniques in Excel VBA.

Uploaded by

Ravi Molasaria
Copyright
© All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as TXT, PDF, TXT or read online on Scribd
6 views2 pages

Detect File Encoding and Export PDF

The document contains two private functions in VBA: DetectEncoding, which identifies the text file's encoding (UTF-8, UTF-16, or ANSI) based on the Byte Order Mark (BOM), and ReadTextFile, which reads the content of a text file using the detected encoding. Additionally, there is a subroutine FormatAndExportSheets that formats the header of each worksheet in the workbook, applies number formatting to numeric cells, and exports each sheet as a PDF. The code demonstrates file handling and formatting techniques in Excel VBA.

Uploaded by

Ravi Molasaria
Copyright
© All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as TXT, PDF, TXT or read online on Scribd

Private Function DetectEncoding(ByVal FileName As String) As String

Dim b() As Byte


Dim f As Integer
Dim BOM As String

f = FreeFile
Open FileName For Binary As #f
If LOF(f) > 3 Then
ReDim b(0 To 2)
Get #f, , b
BOM = Hex(b(0)) & " " & Hex(b(1)) & " " & Hex(b(2))
End If
Close #f

Select Case BOM


Case "EF BB BF"
DetectEncoding = "UTF-8" ' UTF-8 with BOM
Case "FF FE"
DetectEncoding = "UTF-16LE"
Case "FE FF"
DetectEncoding = "UTF-16BE"
Case Else
DetectEncoding = "ANSI" ' Assume ANSI if no BOM
End Select
End Function

==============================================
Private Function ReadTextFile(ByVal FileName As String) As String

Dim enc As String

Dim stm As Object

enc = DetectEncoding(FileName)

Set stm = CreateObject("[Link]")

[Link] = 2 ' adTypeText

[Link] = IIf(enc = "ANSI", "windows-1252", enc) ' fallback to ANSI CP1252

[Link]

[Link] FileName

ReadTextFile = [Link]

[Link]

End Function

===============================================
Dim text As String
text = ReadTextFile("C:\temp\[Link]")
MsgBox text
===============================================

Sub FormatAndExportSheets()
Dim ws As Worksheet
Dim rng As Range
Dim pdfPath As String
Dim cell As Range

For Each ws In [Link]


With ws
'Formatting the header (first row)
Set headerRange = .Range("A1:"
& .Cells(1, .[Link]).Address)
With headerRange
.[Link] = True
.[Link] = RGB(220, 230, 241) 'Giving the header a light blue
background
.[Link] = xlMedium
End With

'Applying number formatting


For Each cell In .UsedRange
If IsNumeric([Link]) Then
If [Link] > 1 Then
[Link] = "###0" 'Formatting the currencies
End If
End If
Next cell

'Exporting the sheet to a PDF


.ExportAsFixedFormat Type:=xlTypePDF, _
Filename:=pdfPath & .Name & ".pdf", _
Quality:=xlQualityStandard, _
IncludeDocProperties:=True, _
IgnorePrintAreas:=False, _
OpenAfterPublish:=False
End With
End Sub

You might also like