access子窗体查询导出ExcelExcel中有一列的数据是计算得出的导出Excel时如何向Exce传输公式.求代码

已完成代码如下,access中一个窗体查询结束后通过一个按钮导出Excel,Excel中的K列是数字格式,表示时间长度,单位是月,L列表示一个日期,格式是中日期,M列的数... 已完成代码如下,access中一个窗体查询结束后通过一个按钮导出Excel,Excel中的K列是数字格式,表示时间长度,单位是月,L列表示一个日期,格式是中日期,M列的数据access表中没有,希望通过代码向Excel中的M列导入一个公式,L列的日期加上K列的月数得到的结果是一个日期或者数字.
Private Sub 汇总到押品移交台账_Click()
Dim rs As DAO.Recordset
Dim objxls As Object
Dim N As Long
Dim strCode As String
Dim lngTSales As Long
Dim lngTSTock As Long
If IsNull(Me.贷款品种) Then
Set rs = CurrentDb.OpenRecordset("SELECT 总查询.* FROM 总查询 ORDER BY 总查询.序号;")
Else
Set rs = CurrentDb.OpenRecordset("SELECT 总查询.* FROM 总查询 WHERE (((总查询.[品种-贷款品种]) = '" & Me.贷款品种 & "')) ORDER BY 总查询.序号;")
End If
rs.MoveFirst
N = 5
Set objxls = CreateObject("excel.Application")
objxls.Workbooks.Add
objxls.Visible = True
With objxls.Sheets("Sheet1")
.Range("AE1:AF1").MergeCells = True
.Range("A1:A2,B1:B2,C1:C2,D1:D2,E1:E2,F1:F2,G1:G2,H1:H2,I1:I2,J1:J2 ,K1:K2 , L1: L2 , M1: M2 , ").MergeCells = True
.Range("A1,J1,K1,S1,T1").ColumnWidth = 5
.Range("F1,R1").ColumnWidth = 25
.Range("AE1:AF1").ColumnWidth = 10
.Range("A1") = "序号"
.Range("B1") = "二级机构名称"
.Range("J1") = "合同金额"
.Range("K1") = "贷款期限"
.Range("L1") = "贷款发放日"
.Range("M1") = "贷款到期日"
With .Range("A1:AI2")
.Font.Bold = True '设为粗体
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlCenter
.Borders.LineStyle = xlContinuous
End With
Do While rs.EOF = False
.Range("A" & N) = rs("序号")
.Range("B" & N) = rs("支行-所属支行")
.Range("G" & N) = rs("借款人")
.Range("H" & N) = rs("客户号")
.Range("I" & N) = rs("贷款账号")
.Range("J" & N) = rs("金额-借款金额(万元)")
.Range("K" & N) = rs("期限-借款期限(月)")
.Range("L" & N).NumberFormatLocal = "yyyymmdd"
.Range("L" & N) = rs("放款日期")
rs.MoveNext
N = N + 1
Loop
With .Range("A3:AI" & N - 1)
.Borders.LineStyle = xlContinuous
End With
End With
objxls.ActiveWorkbook.SaveAs FileName:=CurrentProject.Path & "\" & Me.年度 & "" & Me.所属支行 & " " & Me.贷款品种 & "押品移交台帐.xls"
Set objxls = Nothing
End Sub
拜谢大神展开

 我来答