Excel模板：以批处理模式导入CSV [英] Excel template: import CSV in batch mode

问题描述

我需要创建一个用于在Excel中导入CSV数据（错误日志）的模板（MS Excel）。

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

1. 我设置条件格式，如果调用值大于0，单元格颜色必须自动更改为RED对于值= 0，单元格颜色必须为绿色。

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.

我将文件另存为Excel模板。

I saved the file as Excel template.

现在，我想以批处理模式将CSV数据导入从上述模板创建的新XLSX文档

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.

我使用以下命令：

C：\Program Files（x86）\ Microsoft Office \Office14 \EXCEL.EXE/ eC：\Work\errors.log/ tC：\ Work \ABFTest.xltx

而不是将数据加载到从ABFTest.xltx模板创建的新Excel文档中，命令打开两个Excel文件，一个包含CSV数据，以及从模板创建的其他文件。

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?

推荐答案

@Mubeen Shahid我根据你的原始请求写了下面的代码片段，所以现在不能完全满足你的新条件。如果没有用，那么不用放弃它。

@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.

代码将读取.csv文件，并将其格式化在工作表1的单列中。对于此示例.csv文件称为NosToCol.csv，您必须提供您自己的路径，因此显示为<>。可以在代码段中更改参数以适应。

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

这篇关于Excel模板：以批处理模式导入CSV的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！