Manually-generated excel files versus computer-generated reports/files

Posted on


Why is it the data in a user-generated report (e.g., manually encoded journal entries in an excel file) are destroyed once I append or merge them using Audit Command Language or windows cmd?

Like for example, I am trying to append the contents of sales reports whose data content was manually inputted by a human being– however, once I append them the data are destroyed. This is not the case for computer-generated reports (i.e., reports generated by SAP).


Excel files are binary files, not plain text. Running copy *.txt consolidated.txt is fine but running copy *.xls consolidated.xls does not work because binary files are encoded so that you have to open a specific program that understands the structure of the data in the file.

One way to consolidate a folder of Excel files is simply to convert them to a plain text format (eg a csv), and consolidate those using copy on the command line.

You can create a macro to do this for you:

  1. Create a new Excel file. Save the file in the same directory as the files you want to consolidate as a macro-enabled workbook: eg CsvConverter.xlsm

  2. Press alt+F11 to open the Visual Basic Editor. Select Insert then Module.

  3. Paste the following code into your module (taken from here):

    Option Explicit

    Sub ConvertToCSV()
    ' Uses code from John Walkenbach's Power Programming book
    Dim i As Long
    Dim NumFiles As Long
    Dim FileName As String
    Dim FileNames() As String

    ' Get name of first file in backlog directory
    FileName = Dir(ThisWorkbook.Path & "/*.xls")

    NumFiles = 1
    ReDim Preserve FileNames(1 To NumFiles)
    FileNames(NumFiles) = FileName

    ' Get other file names, if any
    Do While FileName <> ""
    FileName = Dir()
    If FileName <> "" Then
    NumFiles = NumFiles + 1
    ReDim Preserve FileNames(1 To NumFiles)
    FileNames(NumFiles) = FileName
    End If

    ' Save each file as a .csv file, overwriting any existing .csv files
    Application.DisplayAlerts = False
    For i = 1 To UBound(FileNames)
    If FileNames(i) <> ThisWorkbook.Name Then
    Workbooks.Open FileName:=ThisWorkbook.Path & "" &


Leave a Reply

Your email address will not be published. Required fields are marked *