定义模块Type RET_VALUE
long1 As Long
long2 As Long
long3 As Long
long4 As Long
long5 As Long
long6 As Long
long7 As Long
long8 As Long
long9 As Long
long10 As Long
end type
Private MyValue1 As RET_VALUE
function AdodbCcn_2DuanTS() Set cnn_2DuanTS = New ADODB.Connection
strDSN_2DuanTS = "Provider=SQLOLEDB;Data Source=lxz;Initial Catalog=oiltalimu; User ID=SUPERMAN;Password='';"
cnn_2DuanTS.Open strDSN_2DuanTS
Set rs1_2DuanTS = New ADODB.Recordset
Set rs2_2DuanTS = New ADODB.Recordset
Set rs3_2DuanTS = New ADODB.Recordset
Set rs4_2DuanTS = New ADODB.Recordset
str1_2DuanTS = "select top 12 * from 脱水转油综报1 order by DATATIME DESC"
str2_2DuanTS = "select top 12 * from 脱水转油综报2_1 order by DATATIME DESC"
str3_2DuanTS = "select top 12 * from 脱水转油综报2_2 order by DATATIME DESC"
str4_2DuanTS = "select top 12 * from 脱水转油综报3 order by DATATIME DESC"
rs1_2DuanTS.Open str1_2DuanTS, cnn_2DuanTS, adOpenKeyset, adLockOptimistic, adCmdText
rs2_2DuanTS.Open str2_2DuanTS, cnn_2DuanTS, adOpenKeyset, adLockOptimistic, adCmdText
rs3_2DuanTS.Open str3_2DuanTS, cnn_2DuanTS, adOpenKeyset, adLockOptimistic, adCmdText
rs4_2DuanTS.Open str4_2DuanTS, cnn_2DuanTS, adOpenKeyset, adLockOptimistic, adCmdText
nn_2DuanTS = rs2_2DuanTS!DataTime
tt1 = GetTime(nn_2DuanTS)
tt2 = SysTime()
/////////
With MyValue1
.long1 = rs3_2DuanTS!VIR102
.long2 = rs3_2DuanTS!VIR103
.long3 = rs3_2DuanTS!VIR104
.long4 = rs3_2DuanTS!VIR105
.long5 = rs1_2DuanTS!TIR133
.long6 = rs4_2DuanTS!PIR138
.long7 = "11"
.long8 = "11"
.long9 = "11"
.long10 = rs2_2DuanTS!LIR118
.long11 = rs2_2DuanTS!LIR119
.long12 = rs2_2DuanTS!LIR121
end with
AdodbCcn_2DuanTS = MyValue1
///////////
rs3_2DuanTS.Close
rs4_2DuanTS.CloseEnd function
模块结束
////
窗体中的调用模块的函数
Private Sub Command1_Click()
Dim xlApp As Excel.Application
Dim xlBook As Excel.Workbook
Dim xlSheet1 As Excel.Worksheet
Dim strSource, strDestination As String
'2003-7-14.xls就是一个模版文件
strSource = "c:\二段脱水2003-7-16.xls"strDestination = "c:\Temp1.xls"
'将模版文件拷贝到一个临时文件
FileCopy strSource, strDestinationSet xlApp = CreateObject("EXCEL.APPLICATION")
'隐藏EXCEL应用程序窗口隐藏EXCEL应用程序窗口
xlApp.Visible = False'打开工作簿,strDestination为一个EXCEL报表文件
Set xlBook = xlApp.Workbooks.Open(strDestination)
'设定工作表
Set xlSheet1 = xlBook.Worksheets(1)
'/////
'报表生成部分略去
'10 点
xlApp.ActiveSheet.Cells(9, 12).Value = AdodbCcn_2DuanTS.long1
xlApp.ActiveSheet.Cells(9, 13).Value = AdodbCcn_2DuanTS.long2
xlApp.ActiveSheet.Cells(9, 14).Value = AdodbCcn_2DuanTS.long3
xlApp.ActiveSheet.Cells(9, 15).Value = AdodbCcn_2DuanTS.long4
xlApp.ActiveSheet.Cells(9, 22).Value = AdodbCcn_2DuanTS.long5
xlApp.ActiveSheet.Cells(9, 23).Value = AdodbCcn_2DuanTS.long6
xlApp.ActiveSheet.Cells(9, 26).Value = AdodbCcn_2DuanTS.long7
xlApp.ActiveSheet.Cells(9, 31).Value = AdodbCcn_2DuanTS.long8
xlApp.ActiveSheet.Cells(9, 36).Value = AdodbCcn_2DuanTS.long9
xlApp.ActiveSheet.Cells(9, 37).Value = AdodbCcn_2DuanTS.long10
??????????????????????????
??????????????
??????????
??????????????
?????????????
?????????????]
????????
??????1)问题是添加表格中的数值多余57个时就说“局部非静态变量太多”
???????2)还有AdodbCcn_2DuanTS.long1是调用一次函数AdodbCcn_2DuanTS
???????每个付值都要调用一次函数太慢了,怎么解决?
?????????3)如果在程序的这个部分调用一个函数之后 xlApp.ActiveSheet.Cells(9, 37).Value将失去控制权了,怎么能把控制权在给他呢?
??????????????????4)能否在调用被调用函数中把被调用函数在付给一个结构体呢?
If Mode Then
'Mode =True 打印
xlSheet1.PrintOut '执行打印
Else
'Mode =False 预览
xlApp.Visible = True
xlSheet1.Activate
xlApp.ActiveSheet.PageSetup.Orientation = xlLandscape 'xlPortrait
xlSheet1.PrintPreviewxlApp.DisplayAlerts = FalseEnd IfxlApp.Quit
Set xlApp = Nothing
Set xlBook = Nothing
Set xlSheet1 = NothingEnd Sub
long1 As Long
long2 As Long
long3 As Long
long4 As Long
long5 As Long
long6 As Long
long7 As Long
long8 As Long
long9 As Long
long10 As Long
end type
Private MyValue1 As RET_VALUE
function AdodbCcn_2DuanTS() Set cnn_2DuanTS = New ADODB.Connection
strDSN_2DuanTS = "Provider=SQLOLEDB;Data Source=lxz;Initial Catalog=oiltalimu; User ID=SUPERMAN;Password='';"
cnn_2DuanTS.Open strDSN_2DuanTS
Set rs1_2DuanTS = New ADODB.Recordset
Set rs2_2DuanTS = New ADODB.Recordset
Set rs3_2DuanTS = New ADODB.Recordset
Set rs4_2DuanTS = New ADODB.Recordset
str1_2DuanTS = "select top 12 * from 脱水转油综报1 order by DATATIME DESC"
str2_2DuanTS = "select top 12 * from 脱水转油综报2_1 order by DATATIME DESC"
str3_2DuanTS = "select top 12 * from 脱水转油综报2_2 order by DATATIME DESC"
str4_2DuanTS = "select top 12 * from 脱水转油综报3 order by DATATIME DESC"
rs1_2DuanTS.Open str1_2DuanTS, cnn_2DuanTS, adOpenKeyset, adLockOptimistic, adCmdText
rs2_2DuanTS.Open str2_2DuanTS, cnn_2DuanTS, adOpenKeyset, adLockOptimistic, adCmdText
rs3_2DuanTS.Open str3_2DuanTS, cnn_2DuanTS, adOpenKeyset, adLockOptimistic, adCmdText
rs4_2DuanTS.Open str4_2DuanTS, cnn_2DuanTS, adOpenKeyset, adLockOptimistic, adCmdText
nn_2DuanTS = rs2_2DuanTS!DataTime
tt1 = GetTime(nn_2DuanTS)
tt2 = SysTime()
/////////
With MyValue1
.long1 = rs3_2DuanTS!VIR102
.long2 = rs3_2DuanTS!VIR103
.long3 = rs3_2DuanTS!VIR104
.long4 = rs3_2DuanTS!VIR105
.long5 = rs1_2DuanTS!TIR133
.long6 = rs4_2DuanTS!PIR138
.long7 = "11"
.long8 = "11"
.long9 = "11"
.long10 = rs2_2DuanTS!LIR118
.long11 = rs2_2DuanTS!LIR119
.long12 = rs2_2DuanTS!LIR121
end with
AdodbCcn_2DuanTS = MyValue1
///////////
rs3_2DuanTS.Close
rs4_2DuanTS.CloseEnd function
模块结束
////
窗体中的调用模块的函数
Private Sub Command1_Click()
Dim xlApp As Excel.Application
Dim xlBook As Excel.Workbook
Dim xlSheet1 As Excel.Worksheet
Dim strSource, strDestination As String
'2003-7-14.xls就是一个模版文件
strSource = "c:\二段脱水2003-7-16.xls"strDestination = "c:\Temp1.xls"
'将模版文件拷贝到一个临时文件
FileCopy strSource, strDestinationSet xlApp = CreateObject("EXCEL.APPLICATION")
'隐藏EXCEL应用程序窗口隐藏EXCEL应用程序窗口
xlApp.Visible = False'打开工作簿,strDestination为一个EXCEL报表文件
Set xlBook = xlApp.Workbooks.Open(strDestination)
'设定工作表
Set xlSheet1 = xlBook.Worksheets(1)
'/////
'报表生成部分略去
'10 点
xlApp.ActiveSheet.Cells(9, 12).Value = AdodbCcn_2DuanTS.long1
xlApp.ActiveSheet.Cells(9, 13).Value = AdodbCcn_2DuanTS.long2
xlApp.ActiveSheet.Cells(9, 14).Value = AdodbCcn_2DuanTS.long3
xlApp.ActiveSheet.Cells(9, 15).Value = AdodbCcn_2DuanTS.long4
xlApp.ActiveSheet.Cells(9, 22).Value = AdodbCcn_2DuanTS.long5
xlApp.ActiveSheet.Cells(9, 23).Value = AdodbCcn_2DuanTS.long6
xlApp.ActiveSheet.Cells(9, 26).Value = AdodbCcn_2DuanTS.long7
xlApp.ActiveSheet.Cells(9, 31).Value = AdodbCcn_2DuanTS.long8
xlApp.ActiveSheet.Cells(9, 36).Value = AdodbCcn_2DuanTS.long9
xlApp.ActiveSheet.Cells(9, 37).Value = AdodbCcn_2DuanTS.long10
??????????????????????????
??????????????
??????????
??????????????
?????????????
?????????????]
????????
??????1)问题是添加表格中的数值多余57个时就说“局部非静态变量太多”
???????2)还有AdodbCcn_2DuanTS.long1是调用一次函数AdodbCcn_2DuanTS
???????每个付值都要调用一次函数太慢了,怎么解决?
?????????3)如果在程序的这个部分调用一个函数之后 xlApp.ActiveSheet.Cells(9, 37).Value将失去控制权了,怎么能把控制权在给他呢?
??????????????????4)能否在调用被调用函数中把被调用函数在付给一个结构体呢?
If Mode Then
'Mode =True 打印
xlSheet1.PrintOut '执行打印
Else
'Mode =False 预览
xlApp.Visible = True
xlSheet1.Activate
xlApp.ActiveSheet.PageSetup.Orientation = xlLandscape 'xlPortrait
xlSheet1.PrintPreviewxlApp.DisplayAlerts = FalseEnd IfxlApp.Quit
Set xlApp = Nothing
Set xlBook = Nothing
Set xlSheet1 = NothingEnd Sub
解决方案 »
免费领取超大流量手机卡,每月29元包185G流量+100分钟通话, 中国电信官方发货