用QTP将两个Excel表格中相对应列的数据输出到一个新的Excel文件中

smile033

这段时间工作中需要对比大量Excel表格中的数据，所以用QTP实现将需要对比的两个表格中需要对比的列拿出来，发到一个新的Excel表格中，然后在手动绘制新表格中的数据曲线图。
大家如果有更好的方法，希望不吝赐教。代码肯定有需要优化的地方，我还在修改中，先给大家抛个砖。

1. 
   
2. '========================================================================================================
   
3. '功能：将两个xlsx表格中相对应列的数据输出到一个新的xlsx文件中
   
4. '输入：两个xlsx
   
5. '输出：新的xlsx，事先手动创建
   
6. '========================================================================================================
   
7. Option Explicit
   
8. 
   
9. Dim marfilepath(20) ,julyfilepath(20),myfilepath(20),i,rowcount, ExcelShe,columncount,n
   
10. Dim ExcelBook,ExcelSheet,myExcelBook,myExcelSheet,marExcelBook ,marExcelSheet,julyExcelBook ,julyExcelSheet
    
11. '=================
    
12. '初始化输入
    
13. '==================
    
14. marfilepath(1) = "I:\19HD_fg_1.3M_1p_VBR_G100R3.mp4_march.xlsx"
    
15. julyfilepath(1) = "I:\19HD_fg_1.3M_1p_VBR_G100R3.mp4_july.xlsx"
    
16. myfilepath(1) = "I:\19HD_fg_1.3M.xls"
    
17. 
    
18. marfilepath(2) = "I:\19HD_fg_500k_1p_Pref32_Lf1_VBR_G100R3.mp4_march.xlsx"
    
19. julyfilepath(2) = "I:\19HD_fg_500k_1p_Pref32_Lf1_VBR_G100R3.mp4_july.xlsx"
    
20. myfilepath(2) = "I:\19HD_fg_500k.xls"
    
21. 
    
22. marfilepath(3) = "I:\ssim\19HD_ty_1.3M_1p_VBR_G100R3.mp4_march.xlsx"
    
23. julyfilepath(3) = "I:\ssim\19HD_ty_1.3M_1p_VBR_G100R3.mp4_july.xlsx"
    
24. myfilepath(3) = "I:\ssim\19HD_ty_1.3M.xlsx"
    
25. 
    
26. marfilepath(4) = "I:\ssim\19HD_ty_500k_1p_Pref32_Lf1_VBR_G100R3.mp4_march.xlsx"
    
27. julyfilepath(4) = "I:\ssim\19HD_ty_500k_1p_Pref32_Lf1_VBR_G100R3.mp4_july.xlsx"
    
28. myfilepath(4) = "I:\ssim\19HD_ty_500k.xlsx"
    
29. 
    
30. marfilepath(5) = "I:\ssim\19HD_wh_1.3M_1p_VBR_G100R3.mp4_march.xlsx"
    
31. julyfilepath(5) = "I:\ssim\19HD_wh_1.3M_1p_VBR_G100R3.mp4_july.xlsx"
    
32. myfilepath(5) = "I:\ssim\19HD_wh_1.3M.xlsx"
    
33. 
    
34. marfilepath(6) = "I:\ssim\19HD_wh_500k_1p_Pref32_Lf1_VBR_G100R3.mp4_march.xlsx"
    
35. julyfilepath(6) = "I:\ssim\19HD_wh_500k_1p_Pref32_Lf1_VBR_G100R3.mp4_july.xlsx"
    
36. myfilepath(6) = "I:\ssim\19HD_wh_500k.xlsx"
    
37. 
    
38. marfilepath(7) = "I:\ssim\19HD_xw_1.3M_1p_VBR_G100R3.mp4_march.xlsx"
    
39. julyfilepath(7) = "I:\ssim\19HD_xw_1.3M_1p_VBR_G100R3.mp4_july.xlsx"
    
40. myfilepath(7) = "I:\ssim\19HD_xw_1.3M.xlsx"
    
41. 
    
42. marfilepath(8) = "I:\ssim\19HD_xw_500k_1p_Pref32_Lf1_VBR_G100R3.mp4_march.xlsx"
    
43. julyfilepath(8) = "I:\ssim\19HD_xw_500k_1p_Pref32_Lf1_VBR_G100R3.mp4_july.xlsx"
    
44. myfilepath(9) = "I:\ssim\19HD_xw_500k.xlsx"
    
45. 
    
46. marfilepath(9) = "I:\ssim\19HD_zh_1.3M_1p_VBR_G100R3.mp4_march.xlsx"
    
47. julyfilepath(9) = "I:\ssim\19HD_zh_1.3M_1p_VBR_G100R3.mp4_july.xlsx"
    
48. myfilepath(9) = "I:\ssim\19HD_zh_1.3M.xlsx"
    
49. 
    
50. marfilepath(10) = "I:\ssim\19HD_zh_500k_1p_Pref32_Lf1_VBR_G100R3.mp4_march.xlsx"
    
51. julyfilepath(10) = "I:\ssim\19HD_zh_500k_1p_Pref32_Lf1_VBR_G100R3.mp4_july.xlsx"
    
52. myfilepath(10) = "I:\ssim\19HD_zh_500k.xlsx"
    
53. 
    
54. marfilepath(11) = "I:\ssim\12HD_fg_1.4M_1p_Pref32_Lf1_VBR_G100R3.mp4_march.xlsx"
    
55. julyfilepath(11) = "I:\ssim\12HD_fg_1.4M_1p_Pref32_Lf1_VBR_G100R3.mp4_july.xlsx"
    
56. myfilepath(11) = "I:\ssim\12HD_fg_1.4M.xlsx"
    
57. 
    
58. marfilepath(12) = "I:\ssim\12HD_ty_1.4M_1p_Pref32_Lf1_VBR_G100R3.mp4_march.xlsx"
    
59. julyfilepath(12) = "I:\ssim\12HD_ty_1.4M_1p_Pref32_Lf1_VBR_G100R3.mp4_july.xlsx"
    
60. myfilepath(12) = "I:\ssim\12HD_ty_1.4M.xlsx"
    
61. 
    
62. marfilepath(13) = "I:\ssim\12HD_wh_1.4M_1p_Pref32_Lf1_VBR_G100R3.mp4_march.xlsx"
    
63. julyfilepath(13) = "I:\ssim\12HD_wh_1.4M_1p_Pref32_Lf1_VBR_G100R3.mp4_july.xlsx"
    
64. myfilepath(13) = "I:\ssim\12HD_wh_1.4M.xlsx"
    
65. 
    
66. marfilepath(14) = "I:\ssim\12HD_xw_1.4M_1p_Pref32_Lf1_VBR_G100R3.mp4_march.xlsx"
    
67. julyfilepath(14) = "I:\ssim\12HD_xw_1.4M_1p_Pref32_Lf1_VBR_G100R3.mp4_july.xlsx"
    
68. myfilepath(14) = "I:\ssim\12HD_xw_1.4M.xlsx"
    
69. 
    
70. marfilepath(15) = "I:\ssim\12HD_zh_1.4M_1p_Pref32_Lf1_VBR_G100R3.mp4_march.xlsx"
    
71. julyfilepath(15) = "I:\ssim\12HD_zh_1.4M_1p_Pref32_Lf1_VBR_G100R3.mp4_july.xlsx"
    
72. myfilepath(15) = "I:\ssim\12HD_zh_1.4M.xlsx"
    
73. 
    
74. marfilepath(16) = "I:\ssim\HD_fg_6M_1p_VBR_G100R3.mp4_march.xlsx"
    
75. julyfilepath(16) = "I:\ssim\HD_fg_6M_1p_VBR_G100R3.mp4_july.xlsx"
    
76. myfilepath(16) = "I:\ssim\HD_fg_6M.xlsx"
    
77. 
    
78. marfilepath(17) = "I:\ssim\HD_ty_6M_1p_VBR_G100R3.mp4_march.xlsx"
    
79. julyfilepath(17) = "I:\ssim\HD_ty_6M_1p_VBR_G100R3.mp4_july.xlsx"
    
80. myfilepath(17) = "I:\ssim\HD_ty_6M.xlsx"
    
81. 
    
82. marfilepath(18) = "I:\ssim\HD_wh_6M_1p_VBR_G100R3.mp4_march.xlsx"
    
83. julyfilepath(18) = "I:\ssim\HD_wh_6M_1p_VBR_G100R3.mp4_july.xlsx"
    
84. myfilepath(18) = "I:\ssim\HD_wh_6M.xlsx"
    
85. 
    
86. marfilepath(19) = "I:\ssim\HD_xw_6M_1p_VBR_G100R3.mp4_march.xlsx"
    
87. julyfilepath(19) = "I:\ssim\HD_xw_6M_1p_VBR_G100R3.mp4_july.xlsx"
    
88. myfilepath(19) = "I:\ssim\HD_xw_6M.xlsx"
    
89. 
    
90. marfilepath(20) = "I:\ssim\HD_zh_6M_1p_VBR_G100R3.mp4_march.xlsx"
    
91. julyfilepath(20) = "I:\ssim\HD_zh_6M_1p_VBR_G100R3.mp4_july.xlsx"
    
92. myfilepath(20) = "I:\ssim\HD_zh_6M.xlsx"
    
93. 
    
94. '==================
    
95. ''创建Excel应用程序的对象
    
96. '==================
    
97. 'Set fso = CreateObject("Scripting.FileSystemObject")
    
98. Set ExcelBook = CreateObject("Excel.Application")
    
99. Set ExcelSheet = CreateObject("Excel.Sheet")
    
100. 
     
101. '===================
     
102. ''分别取出marfilepath和julyfilepath的SSIM_curYUV，并输入到myfilepath的第一列和第二列
     
103. '===================
     
104. For n=1 to 20
     
105. 
     
106. Set marExcelBook = ExcelBook.WorkBooks.Open(marfilepath(n))'打开excel文件工作簿
     
107. Set marExcelSheet = marExcelBook.WorkSheets("Sheet1")
     
108. 
     
109. Set julyExcelBook = ExcelBook.WorkBooks.Open(julyfilepath(n))
     
110. Set julyExcelSheet = julyExcelBook.WorkSheets("Sheet1")
     
111. 
     
112. Set ExcelShe = marExcelBook.Worksheets("Sheet1").UsedRange     '查询sheet1中使用的区域
     
113. rowcount = ExcelShe.Rows.count  '使用的行
     
114. 'columncount = ExcelShe.Columns.count '使用的列
     
115. 
     
116. Set myExcelBook = ExcelBook.WorkBooks.Open(myfilepath(n))
     
117. Set myExcelSheet = myExcelBook.WorkSheets("Sheet1")
     
118. 
     
119. '写入新的Excel中
     
120. myExcelSheet.Cells(1,1).Value = "file:" & myfilepath(n)
     
121. myExcelSheet.Cells(2,1).Value = "SSIM_curframe_march"
     
122. myExcelSheet.Cells(2,2).Value = "SSIM_curframe_july"
     
123. 
     
124. For i=3 to rowcount
     
125. myExcelSheet.Cells(i,1).Value = marExcelSheet.Cells(i+4,6)
     
126. myExcelSheet.Cells(i,2).Value = julyExcelSheet.Cells(i+4,6)
     
127. Next
     
128. 
     
129. myExcelBook.Save
     
130. 
     
131. Next
     
132. ExcelBook.Quit'退出ExcelApp对象
     
133. 
     
134. Set ExcelBook = Nothing
     

复制代码

17800455

都写死了，不太高明。。。。