用QTP将两个Excel表格中相对应列的数据输出到一个新的Excel文件中
这段时间工作中需要对比大量Excel表格中的数据，所以用QTP实现将需要对比的两个表格中需要对比的列拿出来，发到一个新的Excel表格中，然后在手动绘制新表格中的数据曲线图。大家如果有更好的方法，希望不吝赐教。代码肯定有需要优化的地方，我还在修改中，先给大家抛个砖。
'========================================================================================================
'功能：将两个xlsx表格中相对应列的数据输出到一个新的xlsx文件中
'输入：两个xlsx
'输出：新的xlsx，事先手动创建
'========================================================================================================
Option Explicit
Dim marfilepath(20) ,julyfilepath(20),myfilepath(20),i,rowcount, ExcelShe,columncount,n
Dim ExcelBook,ExcelSheet,myExcelBook,myExcelSheet,marExcelBook ,marExcelSheet,julyExcelBook ,julyExcelSheet
'=================
'初始化输入
'==================
marfilepath(1) = "I:\19HD_fg_1.3M_1p_VBR_G100R3.mp4_march.xlsx"
julyfilepath(1) = "I:\19HD_fg_1.3M_1p_VBR_G100R3.mp4_july.xlsx"
myfilepath(1) = "I:\19HD_fg_1.3M.xls"
marfilepath(2) = "I:\19HD_fg_500k_1p_Pref32_Lf1_VBR_G100R3.mp4_march.xlsx"
julyfilepath(2) = "I:\19HD_fg_500k_1p_Pref32_Lf1_VBR_G100R3.mp4_july.xlsx"
myfilepath(2) = "I:\19HD_fg_500k.xls"
marfilepath(3) = "I:\ssim\19HD_ty_1.3M_1p_VBR_G100R3.mp4_march.xlsx"
julyfilepath(3) = "I:\ssim\19HD_ty_1.3M_1p_VBR_G100R3.mp4_july.xlsx"
myfilepath(3) = "I:\ssim\19HD_ty_1.3M.xlsx"
marfilepath(4) = "I:\ssim\19HD_ty_500k_1p_Pref32_Lf1_VBR_G100R3.mp4_march.xlsx"
julyfilepath(4) = "I:\ssim\19HD_ty_500k_1p_Pref32_Lf1_VBR_G100R3.mp4_july.xlsx"
myfilepath(4) = "I:\ssim\19HD_ty_500k.xlsx"
marfilepath(5) = "I:\ssim\19HD_wh_1.3M_1p_VBR_G100R3.mp4_march.xlsx"
julyfilepath(5) = "I:\ssim\19HD_wh_1.3M_1p_VBR_G100R3.mp4_july.xlsx"
myfilepath(5) = "I:\ssim\19HD_wh_1.3M.xlsx"
marfilepath(6) = "I:\ssim\19HD_wh_500k_1p_Pref32_Lf1_VBR_G100R3.mp4_march.xlsx"
julyfilepath(6) = "I:\ssim\19HD_wh_500k_1p_Pref32_Lf1_VBR_G100R3.mp4_july.xlsx"
myfilepath(6) = "I:\ssim\19HD_wh_500k.xlsx"
marfilepath(7) = "I:\ssim\19HD_xw_1.3M_1p_VBR_G100R3.mp4_march.xlsx"
julyfilepath(7) = "I:\ssim\19HD_xw_1.3M_1p_VBR_G100R3.mp4_july.xlsx"
myfilepath(7) = "I:\ssim\19HD_xw_1.3M.xlsx"
marfilepath(8) = "I:\ssim\19HD_xw_500k_1p_Pref32_Lf1_VBR_G100R3.mp4_march.xlsx"
julyfilepath(8) = "I:\ssim\19HD_xw_500k_1p_Pref32_Lf1_VBR_G100R3.mp4_july.xlsx"
myfilepath(9) = "I:\ssim\19HD_xw_500k.xlsx"
marfilepath(9) = "I:\ssim\19HD_zh_1.3M_1p_VBR_G100R3.mp4_march.xlsx"
julyfilepath(9) = "I:\ssim\19HD_zh_1.3M_1p_VBR_G100R3.mp4_july.xlsx"
myfilepath(9) = "I:\ssim\19HD_zh_1.3M.xlsx"
marfilepath(10) = "I:\ssim\19HD_zh_500k_1p_Pref32_Lf1_VBR_G100R3.mp4_march.xlsx"
julyfilepath(10) = "I:\ssim\19HD_zh_500k_1p_Pref32_Lf1_VBR_G100R3.mp4_july.xlsx"
myfilepath(10) = "I:\ssim\19HD_zh_500k.xlsx"
marfilepath(11) = "I:\ssim\12HD_fg_1.4M_1p_Pref32_Lf1_VBR_G100R3.mp4_march.xlsx"
julyfilepath(11) = "I:\ssim\12HD_fg_1.4M_1p_Pref32_Lf1_VBR_G100R3.mp4_july.xlsx"
myfilepath(11) = "I:\ssim\12HD_fg_1.4M.xlsx"
marfilepath(12) = "I:\ssim\12HD_ty_1.4M_1p_Pref32_Lf1_VBR_G100R3.mp4_march.xlsx"
julyfilepath(12) = "I:\ssim\12HD_ty_1.4M_1p_Pref32_Lf1_VBR_G100R3.mp4_july.xlsx"
myfilepath(12) = "I:\ssim\12HD_ty_1.4M.xlsx"
marfilepath(13) = "I:\ssim\12HD_wh_1.4M_1p_Pref32_Lf1_VBR_G100R3.mp4_march.xlsx"
julyfilepath(13) = "I:\ssim\12HD_wh_1.4M_1p_Pref32_Lf1_VBR_G100R3.mp4_july.xlsx"
myfilepath(13) = "I:\ssim\12HD_wh_1.4M.xlsx"
marfilepath(14) = "I:\ssim\12HD_xw_1.4M_1p_Pref32_Lf1_VBR_G100R3.mp4_march.xlsx"
julyfilepath(14) = "I:\ssim\12HD_xw_1.4M_1p_Pref32_Lf1_VBR_G100R3.mp4_july.xlsx"
myfilepath(14) = "I:\ssim\12HD_xw_1.4M.xlsx"
marfilepath(15) = "I:\ssim\12HD_zh_1.4M_1p_Pref32_Lf1_VBR_G100R3.mp4_march.xlsx"
julyfilepath(15) = "I:\ssim\12HD_zh_1.4M_1p_Pref32_Lf1_VBR_G100R3.mp4_july.xlsx"
myfilepath(15) = "I:\ssim\12HD_zh_1.4M.xlsx"
marfilepath(16) = "I:\ssim\HD_fg_6M_1p_VBR_G100R3.mp4_march.xlsx"
julyfilepath(16) = "I:\ssim\HD_fg_6M_1p_VBR_G100R3.mp4_july.xlsx"
myfilepath(16) = "I:\ssim\HD_fg_6M.xlsx"
marfilepath(17) = "I:\ssim\HD_ty_6M_1p_VBR_G100R3.mp4_march.xlsx"
julyfilepath(17) = "I:\ssim\HD_ty_6M_1p_VBR_G100R3.mp4_july.xlsx"
myfilepath(17) = "I:\ssim\HD_ty_6M.xlsx"
marfilepath(18) = "I:\ssim\HD_wh_6M_1p_VBR_G100R3.mp4_march.xlsx"
julyfilepath(18) = "I:\ssim\HD_wh_6M_1p_VBR_G100R3.mp4_july.xlsx"
myfilepath(18) = "I:\ssim\HD_wh_6M.xlsx"
marfilepath(19) = "I:\ssim\HD_xw_6M_1p_VBR_G100R3.mp4_march.xlsx"
julyfilepath(19) = "I:\ssim\HD_xw_6M_1p_VBR_G100R3.mp4_july.xlsx"
myfilepath(19) = "I:\ssim\HD_xw_6M.xlsx"
marfilepath(20) = "I:\ssim\HD_zh_6M_1p_VBR_G100R3.mp4_march.xlsx"
julyfilepath(20) = "I:\ssim\HD_zh_6M_1p_VBR_G100R3.mp4_july.xlsx"
myfilepath(20) = "I:\ssim\HD_zh_6M.xlsx"
'==================
''创建Excel应用程序的对象
'==================
'Set fso = CreateObject("Scripting.FileSystemObject")
Set ExcelBook = CreateObject("Excel.Application")
Set ExcelSheet = CreateObject("Excel.Sheet")
'===================
''分别取出marfilepath和julyfilepath的SSIM_curYUV，并输入到myfilepath的第一列和第二列
'===================
For n=1 to 20
Set marExcelBook = ExcelBook.WorkBooks.Open(marfilepath(n))'打开excel文件工作簿
Set marExcelSheet = marExcelBook.WorkSheets("Sheet1")
Set julyExcelBook = ExcelBook.WorkBooks.Open(julyfilepath(n))
Set julyExcelSheet = julyExcelBook.WorkSheets("Sheet1")
Set ExcelShe = marExcelBook.Worksheets("Sheet1").UsedRange '查询sheet1中使用的区域
rowcount = ExcelShe.Rows.count'使用的行
'columncount = ExcelShe.Columns.count '使用的列
Set myExcelBook = ExcelBook.WorkBooks.Open(myfilepath(n))
Set myExcelSheet = myExcelBook.WorkSheets("Sheet1")
'写入新的Excel中
myExcelSheet.Cells(1,1).Value = "file:" & myfilepath(n)
myExcelSheet.Cells(2,1).Value = "SSIM_curframe_march"
myExcelSheet.Cells(2,2).Value = "SSIM_curframe_july"
For i=3 to rowcount
myExcelSheet.Cells(i,1).Value = marExcelSheet.Cells(i+4,6)
myExcelSheet.Cells(i,2).Value = julyExcelSheet.Cells(i+4,6)
Next
myExcelBook.Save
Next
ExcelBook.Quit'退出ExcelApp对象
Set ExcelBook = Nothing
都写死了，不太高明。。。。
页:
[1]
