Tag Archives: beyond compare

Beyond compare is an Excel to TXT script to solve the problems of special characters unable to output, multiple sheet pages unable to compare, and files too large to exceed the system memory

Beyond compare is an Excel to TXT script to solve the problems of special characters unable to output, multiple sheet pages unable to compare, and files too large to exceed the system memory

' XLS_to_CSV.vbs
'
' Converts an Excel workbook to a comma-separated text file.  Requires Microsoft Excel.
' Usage:
'  WScript XLS_to_CSV.vbs <input file> <output file>

Option Explicit

' MsoAutomationSecurity
Const msoAutomationSecurityForceDisable = 3
' OpenTextFile iomode
Const ForReading = 1
Const ForAppending = 8
Const TristateTrue = -1 
' XlFileFormat
Const xlCSV = 6 ' Comma-separated values
Const xlUnicodeText = 42
' XlSheetVisibility
Const xlSheetVisible = -1

Dim App, AutoSec, Doc, FileSys, AppProtect
Set FileSys = CreateObject("Scripting.FileSystemObject")
If FileSys.FileExists(WScript.Arguments(1)) Then
    FileSys.DeleteFile WScript.Arguments(1)
End If
Set App = CreateObject("Excel.Application")
'Set AppProtect = CreateObject("Excel.Application")

On Error Resume Next

App.DisplayAlerts = False
AutoSec = App.AutomationSecurity
App.AutomationSecurity = msoAutomationSecurityForceDisable
Err.Clear

Dim I, J, SheetName, TgtFile, TmpFile, TmpFilenames(), Content
Set Doc = App.Workbooks.Open(WScript.Arguments(0), False, True)
If Err = 0 Then
    I = 0
    For J = 1 To Doc.Sheets.Count
        If Doc.Sheets(J).Visible = xlSheetVisible Then
            I = I + 1
        End If
    Next
    ReDim TmpFilenames(I - 1)
    Set TgtFile = FileSys.OpenTextFile(WScript.Arguments(1), ForAppending, True, TristateTrue)
    I = 0
    For J = 1 To Doc.Sheets.Count
        If Doc.Sheets(J).Visible = xlSheetVisible Then
            SheetName = Doc.Sheets(J).Name
            TgtFile.WriteLine """SHEET " & SheetName & """"
            Doc.Sheets(J).Activate
            TmpFilenames(I) = FileSys.GetSpecialFolder(2) & "\" & FileSys.GetTempName
            Doc.SaveAs TmpFilenames(I), xlUnicodeText
            Set TmpFile = FileSys.OpenTextFile(TmpFilenames(I), ForReading, False, TristateTrue)
            'Write Writing the entire file will cause all the contents of the file to be lost if the write fails, so use the line-by-line method.
            ' It also prevents the problem of insufficient memory for too large files
            while not TmpFile.AtEndOfStream
                TgtFile.WriteLine TmpFile.ReadLine
            Wend
            'TgtFile.Write TmpFile.ReadAll
            TmpFile.Close
            If I <> UBound(TmpFilenames) Then
                TgtFile.WriteLine
            End If
            Doc.Sheets(J).Name = SheetName
            I = I + 1
        End If
    Next
    TgtFile.Close
    Doc.Close False
End If

App.AutomationSecurity = AutoSec
App.Quit
Set App = Nothing

For I = 0 To UBound(TmpFilenames)
    If FileSys.FileExists(TmpFilenames(I)) Then
        FileSys.DeleteFile TmpFilenames(I)
    End If
Next

WScript.Sleep(1000)

'This step is to expose the failed window to the foreground for the user to close manually, which should be ignored by the On Error Resume Next catch above
App.Visible = true

If AppProtect.Workbooks.
' 'Protected processes can not just exit, the user may be using        
'    AppProtect.Quit
'End If
'AppProtect.Visible = true
'Set AppProtect = Nothing

‘ XLS_to_CSV.vbs” Converts an Excel workbook to a comma-separated text file. Requires Microsoft Excel.’ Usage:’ WScript XLS_to_CSV.vbs <input file> <output file>
Option Explicit
‘ MsoAutomationSecurityConst msoAutomationSecurityForceDisable = 3′ OpenTextFile iomodeConst ForReading = 1Const ForAppending = 8Const TristateTrue = -1’ XlFileFormatConst xlCSV = 6 ‘ Comma-separated valuesConst xlUnicodeText = 42′ XlSheetVisibilityConst xlSheetVisible = -1
Dim App, AutoSec, Doc, FileSys, AppProtectSet FileSys = CreateObject(“Scripting.FileSystemObject”)If FileSys.FileExists(WScript.Arguments(1)) ThenFileSys.DeleteFile WScript.Arguments(1)End IfSet App = CreateObject(“Excel.Application”)’Set AppProtect = CreateObject(“Excel.Application”)
On Error Resume Next
App.DisplayAlerts = FalseAutoSec = App.AutomationSecurityApp.AutomationSecurity = msoAutomationSecurityForceDisableErr.Clear
Dim I, J, SheetName, TgtFile, TmpFile, TmpFilenames(), ContentSet Doc = App.Workbooks.Open(WScript.Arguments(0), False, True)If Err = 0 ThenI = 0For J = 1 To Doc.Sheets.CountIf Doc.Sheets(J).Visible = xlSheetVisible ThenI = I + 1End IfNextReDim TmpFilenames(I – 1)Set TgtFile = FileSys.OpenTextFile(WScript.Arguments(1), ForAppending, True, TristateTrue)I = 0For J = 1 To Doc.Sheets.CountIf Doc.Sheets(J).Visible = xlSheetVisible ThenSheetName = Doc.Sheets(J).NameTgtFile.WriteLine “””SHEET ” & SheetName & “”””Doc.Sheets(J).ActivateTmpFilenames(I) = FileSys.GetSpecialFolder(2) & “\” & FileSys.GetTempNameDoc.SaveAs TmpFilenames(I), xlUnicodeTextSet TmpFile = FileSys.OpenTextFile(TmpFilenames(I), ForReading, False, TristateTrue)’Write If you write the whole file, a write failure will cause all the contents of the whole file to be lost, so a line-by-line approach is used.’ It can also prevent the problem of too large file with insufficient memory while not TmpFile.AtEndOfStream TgtFile.WriteLine TmpFile.ReadLine Wend ‘TgtFile.Write TmpFile.ReadAllTmpFile.CloseIf I <> UBound(TmpFilenames) ThenTgtFile.WriteLineEnd IfDoc.Sheets(J).Name = SheetNameI = I + 1End IfNextTgtFile.CloseDoc.Close FalseEnd If
App.AutomationSecurity = AutoSecApp.QuitSet App = Nothing
For I = 0 To UBound(TmpFilenames)If FileSys.FileExists(TmpFilenames(I)) ThenFileSys.DeleteFile TmpFilenames(I)End IfNext
WScript.Sleep(1000)
‘This step is designed to expose the failed window to the foreground for the user to close manually, which should be ignored by the above On Error Resume Next capture App.Visible = true
If AppProtect.Workbooks.Count = 0 Then’ ‘The protection process can’t just exit, the user may be using ‘ AppProtect.Quit’End If’AppProtect.Visible = true’Set AppProtect = Nothing