由于学校的有两套不同的系统,要进行数据同步,都支持excel格式数据导入导出,但是两套模板格式不一样,需要进行转换。
这个实例涉及到字符的截取,字段获取以写入,不同表之间的操作等。
源模板:
目标模板:
VBA代码:
[code]Sub test()
Dim i%, r%, c%
Dim arr, brr()
With Worksheets("sheet1")
r = .Cells(.Rows.Count, 1).End(xlUp).Row
c = .Cells(1, Columns.Count).End(xlToLeft).Column
arr = .Range("a1").Resize(r,
c)
End With
ReDim brr(1 To (r - 1) * (c - 1), 1 To 5)
m = 1
For j = 2 To c
For i = 2 To r
crr = Split(arr(i, j), Chr(10))
drr = Split(arr(i, 1), "/")
brr(m, 1) = arr(1, j)
brr(m, 2) = crr(1)
brr(m, 3) = crr(0)
brr(m, 4) = drr(0)
brr(m, 5) = drr(1)
m = m + 1
Next
Next
With
Worksheets("sheet2")
.UsedRange.Offset(1, 0).ClearContents
.Range("a2").Resize(UBound(brr),
UBound(brr, 2)) = brr
End With
End Sub[/code]