问题描述
我需要创建一个模板 (MS Excel) 以在 Excel 中导入 CSV 数据(错误日志).
I need to create a template (MS Excel) for importing CSV data (error log) in Excel.
我设置了条件格式,如果调用值大于 0,单元格颜色必须自动更改为红色,而值 = 0 时,单元格颜色必须为绿色.
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.
我正在使用以下命令:
该命令不是将数据加载到从 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的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!