Excel template: import CSV in batch mode

问题:

I need to create a template (MS Excel) for importing CSV data (error log) in Excel.

1. I have set the conditional formatting that if a call value is greater than 0, the cell color must automatically change to RED while for value=0, the cell color must be green.

2. I saved the file as Excel template.

3. Now I want to import CSV data into a new XLSX document created from the template above (with conditional formatting), in batch-mode, and safe the result automatically.

I am using following command:

"C:\Program Files (x86)\Microsoft Office\Office14\EXCEL.EXE" /e "C:\Work\errors.log" /t "C:\Work\ABFTest.xltx"

Instead of loading the data into a new Excel document created from ABFTest.xltx template, the command open two Excel files, one containing the CSV data, and other file created from the template.

Is there any solution to this problem?

回答1:

@Mubeen Shahid I wrote the code snippet below based on your original request so it will not now fully meet your new conditions. If it is of no use then no matter just discard it.

The code will read a .csv file and format it in a single column in sheet 1. For this example the .csv file is called "NosToCol.csv" and you must supply your own path where shown thus <>. Parameters can be changed within the code snippet to suit.

Sub ReadCSVFile()

Dim ws As Worksheet
Dim fName As String, Txt1 As String, tmpvar As String
Dim fRow As Long, lRow As Long, Rw As Long
Dim Col As Long, rec As Long
Dim wrng As Range, cl As Range
Dim ifnum As Integer
Dim rearr(), wrarr

Set ws = Sheets("Sheet1")
fName = "<<yourpath>>\NosToCol.csv"
fRow = 2  'Row 2
Col = 1   'Col A
Txt1 = ""
ifnum = 1

    With ws
        lRow = .Cells(Rows.Count, Col).End(xlUp).Row

        'READ DATA FROM FILE
        Open fName For Input Access Read As #ifnum
        rec = 0
            Do While Not EOF(ifnum)
                Line Input #ifnum, tmpvar
                rec = rec + 1
                ReDim Preserve rearr(1 To rec)
                rearr(rec) = tmpvar
            Loop
            Close #ifnum

         'WRITE DATA TO RANGE
            For c = 1 To rec
                wrarr = Split(rearr(c), ",")
                Set wrng = .Range(.Cells(fRow, Col), .Cells(fRow + UBound(wrarr, 1), Col))
                '.Range(.Cells(fRow, Col), .Cells(fRow + UBound(wrarr, 1), Col)).Value = Application.Transpose(wrarr)
                wrng.Value = Application.Transpose(wrarr)
                c = c + 1
                    'MODIFY CELL COLOUR
                    For Each cl In wrng
                        If cl = 0 Then cl.Interior.Color = vbRed Else cl.Interior.Color = vbGreen
                    Next cl
            Next c
    End With
End Sub