技術(shù) 點(diǎn)
- 技術(shù)
- 點(diǎn)
- V幣
- 點(diǎn)
- 積分
- 16023
|
目的:
從Access中復(fù)制數(shù)據(jù)到Excel中,
并設(shè)置單元格字體格式
(同一個(gè)單元格中部分字符,不同字體,本例中是單元格中第7,第8兩個(gè)字體設(shè)為7號(hào)楷體)
如果直接在Access中操作,需要8秒中(僅設(shè)置格式部分),
而同樣(幾乎一致)的代碼,在Excel中運(yùn)行,則只需1秒,甚至不到1秒.
我想Access中的代碼應(yīng)該是寫的不合理,
大家指點(diǎn)一下,如何優(yōu)化代碼?
Excel中的代碼(只需1秒)
- Private Sub CommandButton1_Click()
- Dim R As Long, C As Long, I As Long, J As Long
- Dim t As Date
- t = Now
- R = Rcount
- C = Ccount
- Application.ScreenUpdating = False
- For I = 4 To R
- For J = 1 To C
- With Sheets("A").Cells(I, J).Characters(7, 2).Font
- .Name = "楷體"
- .FontStyle = "常規(guī)"
- .Size = 6
- End With
- Next
- Next
- Application.ScreenUpdating = True
- t = t - Now
- MsgBox t
- End Sub
復(fù)制代碼
Access中的代碼(需要8秒-10秒)
- Private Sub Command0_Click()
- Dim XLA As New Excel.Application
- Dim rs As New ADODB.Recordset
- Dim XLB As Workbook
- Dim XLS As Worksheet
- Dim I As Integer, J As Integer
- Dim R As Long, C As Long
- Dim t As Date
- Set XLB = Nothing
- Set XLS = Nothing
- Set XLB = XLA.Workbooks().Open(CurrentProject.Path & "\Try.xlsb", , True)
- XLB.SaveAs CurrentProject.Path & "" & Format(Now, "yyyy-mm-dd hh nn ss") & ".xlsb" '另存為不同的文件名"
- '---------------
- '導(dǎo)出Excel
- '---------------
- rs.Open "B", CurrentProject.Connection, adOpenKeyset, adLockReadOnly
- Set XLS = XLB.Worksheets("A")
- XLS.Activate
- For I = 0 To rs.Fields.Count - 1
- XLS.Cells(3, 1 + I) = rs.Fields(I).Name
- Next
- XLS.Range("A4").CopyFromRecordset rs
- XLS.Range(XLS.Cells(3, 1), XLS.Cells(rs.RecordCount + 3, rs.Fields.Count)).Borders.LineStyle = xlContinuous
- R = rs.RecordCount + 3
- C = rs.Fields.Count
- rs.Close
- t = Now
- XLA.ScreenUpdating = False
- For I = 4 To R
- For J = 1 To C
- With XLS.Cells(I, J).Characters(7, 2).Font
- .Name = "楷體"
- .FontStyle = "常規(guī)"
- .Size = 7
- End With
- Next
- Next
- XLA.ScreenUpdating = True
- t = t - Now
- MsgBox t
- XLB.Save
- XLA.Visible = True
- XLA.WindowState = xlMaximized
- Set rs = Nothing
- Set XLS = Nothing
- Set XLB = Nothing
- Set XLA = Nothing
- End Sub
復(fù)制代碼 另外以前感覺Excel打開并在屏幕上可見才需要XLA.ScreenUpdating = False
現(xiàn)在發(fā)現(xiàn),即便Excel在內(nèi)存里打開,并沒有顯示在屏幕上,如果不加XLA.ScreenUpdating = False
更慢,而且是非常慢,大家可以試試.
附上附件,請(qǐng)大家?guī)兔?yōu)化優(yōu)化.
|
本帖子中包含更多資源
您需要 登錄 才可以下載或查看,沒有帳號(hào)?注冊(cè)
x
|