Private Sub Command6_Click()
Dim xlApp As Excel.Application 'Defination
Dim xlBook As Excel.Workbook
Dim xlSheet As Excel.Worksheet Set xlApp = CreateObject("Excel.Application")
Set xlBook = xlApp.Workbooks.Open("D:\VB\ModelFile\ModelChart.xls") 'Read the TestInformation
Set xlSheet = xlBook.Worksheets(1)
'Application.AskToUpdateLinks = False 'Auto update setting
f = 1
For ii = 2 To 4084
xlSheet.Cells(ii, 1) = (ii - 2) * f / 60
Debug.Print ii
Next ii
For ii = 2 To 4084
For jj = 2 To 3
xlSheet.Cells(ii, jj) = ii * jj
Next jj
Debug.Print ii
Next ii
ActiveSheet.ChartObjects("ͼ±í 6").Activate
With xlApp.ActiveChart
.ChartArea.Select
.ChartArea.Copy
End With
xlBook.Close (False)
xlApp.Application.Quit
Set xlApp = Nothing Set xlApp = CreateObject("Excel.Application")
Set xlBook = xlApp.Workbooks.Open("D:\VB\ModelProfil.xls") 'Read the TestInformation
Set xlSheet = xlBook.Worksheets(1)
xlSheet.Range("A28").Select
xlSheet.Paste
xlBook.SaveAs "D:\VB\ChartReport.xls" 'Save the result
xlBook.Close (False)
xlApp.Application.Quit
Set xlApp = Nothing
End Sub中间的for循环是赋值部分,我用debug.print观察速度,发现太慢了,10000多格单元格要好几分钟了,几百份报告的话要出几个小时了估计,这样不行的,不知道有什么快速的办法没有?
Dim xlApp As Excel.Application 'Defination
Dim xlBook As Excel.Workbook
Dim xlSheet As Excel.Worksheet Set xlApp = CreateObject("Excel.Application")
Set xlBook = xlApp.Workbooks.Open("D:\VB\ModelFile\ModelChart.xls") 'Read the TestInformation
Set xlSheet = xlBook.Worksheets(1)
'Application.AskToUpdateLinks = False 'Auto update setting
f = 1
For ii = 2 To 4084
xlSheet.Cells(ii, 1) = (ii - 2) * f / 60
Debug.Print ii
Next ii
For ii = 2 To 4084
For jj = 2 To 3
xlSheet.Cells(ii, jj) = ii * jj
Next jj
Debug.Print ii
Next ii
ActiveSheet.ChartObjects("ͼ±í 6").Activate
With xlApp.ActiveChart
.ChartArea.Select
.ChartArea.Copy
End With
xlBook.Close (False)
xlApp.Application.Quit
Set xlApp = Nothing Set xlApp = CreateObject("Excel.Application")
Set xlBook = xlApp.Workbooks.Open("D:\VB\ModelProfil.xls") 'Read the TestInformation
Set xlSheet = xlBook.Worksheets(1)
xlSheet.Range("A28").Select
xlSheet.Paste
xlBook.SaveAs "D:\VB\ChartReport.xls" 'Save the result
xlBook.Close (False)
xlApp.Application.Quit
Set xlApp = Nothing
End Sub中间的for循环是赋值部分,我用debug.print观察速度,发现太慢了,10000多格单元格要好几分钟了,几百份报告的话要出几个小时了估计,这样不行的,不知道有什么快速的办法没有?
解决方案 »
免费领取超大流量手机卡,每月29元包185G流量+100分钟通话, 中国电信官方发货