举个例子:
Private Sub fopen()
Dim ExcelApp As New Excel.Application
Dim eworkbook As Workbook
Dim eworksheet As Worksheet
Set eworkbook = ExcelApp.Workbooks.Open("G:\传感器编号.xls")
Set eworksheet = eworkbook.Sheets(1)
End SubPrivate Sub fclose()
If Not (ExcelApp Is Nothing) Then
ExcelApp.Quit
End If
End SubPrivate Sub Command1_Click()
Call fopen
End SubPrivate Sub Command2_Click()
Call fclose
End Sub
我点两下Command1之后,在进程中我就看见两个Excel的进程,但是我点Command2之后,却关闭不了Excel进程,给出错误提示"要求对象".
现在我希望达到这样的效果,在程序执行前,先判断是否有我已经打开的Excel文件,如果有就把先把它关闭.
高手们请帮帮忙!!!!!!!!!!!!!!!!!!!!!!!!!!!!!11
Private Sub fopen()
Dim ExcelApp As New Excel.Application
Dim eworkbook As Workbook
Dim eworksheet As Worksheet
Set eworkbook = ExcelApp.Workbooks.Open("G:\传感器编号.xls")
Set eworksheet = eworkbook.Sheets(1)
End SubPrivate Sub fclose()
If Not (ExcelApp Is Nothing) Then
ExcelApp.Quit
End If
End SubPrivate Sub Command1_Click()
Call fopen
End SubPrivate Sub Command2_Click()
Call fclose
End Sub
我点两下Command1之后,在进程中我就看见两个Excel的进程,但是我点Command2之后,却关闭不了Excel进程,给出错误提示"要求对象".
现在我希望达到这样的效果,在程序执行前,先判断是否有我已经打开的Excel文件,如果有就把先把它关闭.
高手们请帮帮忙!!!!!!!!!!!!!!!!!!!!!!!!!!!!!11
解决方案 »
- 向各位大侠求助,如何让datagrid控件,在点击下拉按钮时,显示多项内容,像ComboBox控件一样
- 请教各位:我登陆sqlserver的用户名为‘ABC’,密码为‘123’在语句中如何实现?
- 大家有没有发贴机源码或是资料?
- CSDN的Blog为什么出现这种问题?
- 我想问问微软的程序员用什么OS,什么开发工具,Office用的是那个版本
- 为什么看不到中文,跪求高手!!!???
- 有些软件用VB编的,但那菜单是XP的是怎么做的啊!有没有生成这种菜单的。免费控件下载了!
- 请问怎样把VB中源文件转为其它格式的文件?
- 关于在图片框画线
- 怎样取得本地和远程计算机的名字,IP和端口号
- VB和VB.NET的区别?
- 下标越界
Private Sub cmdExcel_Click()
On Error GoTo ErrHandler
Dim strsql As String
Dim strsql_db As String
Dim jhze As Double
Dim fkze As Double
Dim wczcje As Double
Dim yfkje As Double
Dim fkje As Double
Dim ce As Double
If Text1.Text = "" Then
MsgBox "查询的年份不能为空!", 48, "信息"
Exit Sub
End If
If Text2.Text = "" Then
MsgBox "请查询数据!", 48, "信息"
Exit Sub
End If
Set xlapp1 = CreateObject("excel.application") 'create the excel object
xlapp1.Workbooks.Open (App.Path & "\按单位查询模板.xls") 'FileName changed
xlapp1.Workbooks("按单位查询模板.xls").Activate
xlapp1.Worksheets(1).Cells(1, 1) = Text1.Text & "年按单位统计的完成资产统计表"
strsql = Text2.Text
Set rs = ExecuteSQL(strsql, msgtext)
For i = 6 To rs.RecordCount + 5
xlapp1.ActiveSheet.Rows(i).Insert
xlapp1.Worksheets(1).Cells(i, 1) = i - 5
xlapp1.Worksheets(1).Cells(i, 2) = rs.Fields("单位名称")
xlapp1.Worksheets(1).Cells(i, 3) = rs.Fields("计划总额")
xlapp1.Worksheets(1).Cells(i, 4) = rs.Fields("付款总额")
xlapp1.Worksheets(1).Cells(i, 5) = rs.Fields("完成资产金额")
xlapp1.Worksheets(1).Cells(i, 6) = rs.Fields("预付款金额")
xlapp1.Worksheets(1).Cells(i, 7) = rs.Fields("付款金额")
xlapp1.Worksheets(1).Cells(i, 8) = rs.Fields("差额")
jhze = jhze + rs.Fields("计划总额")
wczcje = jhje + rs.Fields("完成资产金额")
yfkje = jhje + rs.Fields("预付款金额")
fkje = fkje + rs.Fields("付款金额")
fkze = fkze + rs.Fields("付款总额")
ce = ce + rs.Fields("差额")
rs.MoveNext
Next i
xlapp1.ActiveSheet.Rows(5).Delete
xlapp1.Worksheets(1).Cells(4, 3) = jhze
xlapp1.Worksheets(1).Cells(4, 4) = fkze
xlapp1.Worksheets(1).Cells(4, 5) = wczcje
xlapp1.Worksheets(1).Cells(4, 6) = yfkje
xlapp1.Worksheets(1).Cells(4, 7) = fkje
xlapp1.Worksheets(1).Cells(4, 8) = ce
With CommonDialog1
.DialogTitle = "生成Excel"
.FileName = "*.xls"
.Filter = "(Excel)*.xls|*.xls"
.CancelError = True
.ShowOpen
'.ShowSave
End With
'xlapp1.Save
xlapp1.ActiveWorkbook.SaveAs (CommonDialog1.FileName)
xlapp1.Quit
MsgBox "数据导Excel完成!", 48, "信息"
rs.Close
Set rs = Nothing
Exit Sub
ErrHandler:
'用户按了“取消”按钮
MsgBox "用户取消从Excel导出数据操作!", 48, "提示"
Exit Sub
End Sub
Private Sub cmdExcel_Click()
On Error GoTo ErrHandler
Dim strsql As String
Dim strsql_db As String
Dim jhze As Double
Dim fkze As Double
Dim wczcje As Double
Dim yfkje As Double
Dim fkje As Double
Dim ce As Double
If Text1.Text = "" Then
MsgBox "查询的年份不能为空!", 48, "信息"
Exit Sub
End If
If Text2.Text = "" Then
MsgBox "请查询数据!", 48, "信息"
Exit Sub
End If
Set xlapp1 = CreateObject("excel.application") 'create the excel object
xlapp1.Workbooks.Open (App.Path & "\按单位查询模板.xls") 'FileName changed
xlapp1.Workbooks("按单位查询模板.xls").Activate
xlapp1.Worksheets(1).Cells(1, 1) = Text1.Text & "年按单位统计的完成资产统计表"
strsql = Text2.Text
Set rs = ExecuteSQL(strsql, msgtext)
For i = 6 To rs.RecordCount + 5
xlapp1.ActiveSheet.Rows(i).Insert
xlapp1.Worksheets(1).Cells(i, 1) = i - 5
xlapp1.Worksheets(1).Cells(i, 2) = rs.Fields("单位名称")
xlapp1.Worksheets(1).Cells(i, 3) = rs.Fields("计划总额")
xlapp1.Worksheets(1).Cells(i, 4) = rs.Fields("付款总额")
xlapp1.Worksheets(1).Cells(i, 5) = rs.Fields("完成资产金额")
xlapp1.Worksheets(1).Cells(i, 6) = rs.Fields("预付款金额")
xlapp1.Worksheets(1).Cells(i, 7) = rs.Fields("付款金额")
xlapp1.Worksheets(1).Cells(i, 8) = rs.Fields("差额")
jhze = jhze + rs.Fields("计划总额")
wczcje = jhje + rs.Fields("完成资产金额")
yfkje = jhje + rs.Fields("预付款金额")
fkje = fkje + rs.Fields("付款金额")
fkze = fkze + rs.Fields("付款总额")
ce = ce + rs.Fields("差额")
rs.MoveNext
Next i
xlapp1.ActiveSheet.Rows(5).Delete
xlapp1.Worksheets(1).Cells(4, 3) = jhze
xlapp1.Worksheets(1).Cells(4, 4) = fkze
xlapp1.Worksheets(1).Cells(4, 5) = wczcje
xlapp1.Worksheets(1).Cells(4, 6) = yfkje
xlapp1.Worksheets(1).Cells(4, 7) = fkje
xlapp1.Worksheets(1).Cells(4, 8) = ce
With CommonDialog1
.DialogTitle = "生成Excel"
.FileName = "*.xls"
.Filter = "(Excel)*.xls|*.xls"
.CancelError = True
.ShowOpen
'.ShowSave
End With
'xlapp1.Save
xlapp1.ActiveWorkbook.SaveAs (CommonDialog1.FileName)
xlapp1.Quit
MsgBox "数据导Excel完成!", 48, "信息"
rs.Close
Set rs = Nothing
Exit Sub
ErrHandler:
'用户按了“取消”按钮
MsgBox "用户取消从Excel导出数据操作!", 48, "提示"
Exit Sub
End Sub
◆访问EXCEL:ExcelSheet = GetObject('','Excel.Sheet')
返回结果为类,则成功。例:
ExcelSheet = GetObject('','Excel.Sheet')
If Type("ExcelSheet")#'O' THEN
=MessageBox( "访问Excel失败!请检查你的系统是否正确安装 Excel 软件!",48,"Excel不正常")
RETURN
ENDIF◆创建EXCEL实例:oExcel=CreateObject("Excel.Application")
该实例创建后,任何对EXCEL的操作都针对该实例进行,关闭EXCEL后应释放该实例变量:RELEASE oExcel◆打开已存在的EXCEL文件:oExcel.WorkBooks.Open("&lcFileName") &&lcFileName为Excel文件名
打开带有读写密码的文件:oExcel.WorkBooks.Open("&lcFileName",,.F.,,"&R_Pwd","&W_Pwd") &&lcFileName-文件名,R_Pwd-读密码,W_Pwd-写密码,第二个参数表示读写方式:.T.只读,.F.读写◆设置新增工作薄的工作表数:oExcel.SheetsInNewWorkbook=1 &&新建工作表数量定为1个◆新增EXCEL工作薄:oExcel.WorkBooks.Add◆当前工作薄工作表总数:oExcel.WorkSheets.Count &&如:lnSheetCnt=oExcel.WorkSheets.count◆新增EXCEL工作表:oExcel.Sheets.Add &&不带参数为增加至当前工作表之前
在指定工作表后新建工作表:oExcel.Sheets.Add(,oExcel.Sheets(lnSheet),1,-4167) &&lnSheet为指定表号
在最后工作表后新建工作表:oExcel.Sheets.Add(,lnSheetsCnt,1,-4167) &&lnSheetCnt为工作表总数◆激活工作表:oExcel.WorkSheets("Sheet1").Activate &&激活工作表1
删除工作表:oExcel.WorkSheets("Sheet2").Delete &&删除工作表2
命名工作表:oExcel.WorkSheets("Sheet3").Name="新表" &&重命名工作表3◆使EXCEL可视:oExcel.Vsible=.T.◆Excel的窗口控制:oExcel.WindowState = -4140 &&最小化=-4140,最大化=-4137,还原=-4143◆后台操作报警关闭:oExcel.DisplayAlerts=.F. &&关闭(.F.) 打开(.T.)◆设置工作薄的标题:oExcel.Caption="Vfp控制Excel操作"◆当前工作薄换名存盘:oExcel.ActiveWorkBook.SaveAs("&lcFileName")
换名存为文本文件:oExcel.ActiveWorkBook.Saveas("&lcFileName",-4158) ◆工作薄存盘:oExcel.WorkBooks.Save◆关闭工作薄:oExcel.WorkBooks.Close◆退出EXCEL:oExcel.Quit &&关闭EXCEL后通常应释放实例变量:Release oExcel◆打印当前工作簿:oExcel.ActiveWorkBook.PrintOut(1,oExcel.WorkSheets.count,1,.T.)
*默认直接打印整个工作簿:oExcel.ActiveWorkBook.PrintOut()
*参数1-数值:当前工作簿中进行打印的起始工作表号(默认1)
*参数2-数值:当前工作簿中进行打印的结束工作表号(默认最末)
*参数3-数值:打印份数(默认1)
*参数4-是预览打印还是直接打印:.T.-预览打印,.F.-直接打印(默认.F.)◆打印当前工作表:oExcel.ActiveSheet.PrintOut() &&共八个参数
*参数1-数值:起始页号,省略则默认为开始位置
*参数2-数值:终止页号,省略则默认为最后一页
*参数3-数值:打印份数,省略则默认为1份
*参数4-逻辑值:是否预览,省略则默认为直接打印(.F.)
*参数5-字符值:设置活动打印机名称,省略则为默认打印机
*参数6-逻辑值:是否输出到文件,省略则默认为否(.F.),若选.T.且参数8为空,则Excel提示输入要输出的文件名
*参数7-逻辑值:输出类型,省略则默认为(.T.)逐份打印,否则逐页打印
*参数8-字符值:当参数6为.T.时,设置要打印到的文件名◆预览当前工作表:oExcel.ActiveSheet.PrintPreview◆当前工作表页面设置:oExcel.ActiveSheet.PageSetup &&如:
With oExcel.ActiveSheet.PageSetup
.CenterFooter="第"+"&P"+"页 共"+"&N"+"页" &&页脚中间
.LeftFooter="制表人:"+AllTrim(lcUserName) &&页脚左边
.rightfooter="制表日期:"+Dtoc(Date()) &&页脚右边
.TopMargin=2.5/0.035 &&顶边距
.BottomMargin=2.5/0.035 &&底边距
.LeftMargin=1.4/0.035 &&左边距
.RightMargin=0.5/0.035 &&右边距
.FooterMargin=3.5/0.035 &&脚高距
.PrintTitleRows = "$1:$3" &&固定打印的顶端标题行(第1-3行)
.Orientation = 1 &&打印方向: 1为纵向,2为横向
.PaperSize=9 &&纸张类型: A4(注意:不同打印机与版本可能有不同)
.PrintQuality = 180 &&打印精度
EndWith◆从包含大量内含公式的EXCEL文件导至DBF中时,直接转换会出现致命错误,这时可选择将EXCEL另存为文本文件再导至DBF中可保数据不丢失:
oExcel.WorkBooks.Open('D:\123.xls')
oExcel.ActiveWorkBook.Saveas('D:\123.txt',-4158 ) &&注意参数-4158不能错◆若判断EXCEL或某个XLS文件是否已经打开(避免不可预知的错误发生):
LOCAL lOldSetOpt,mChanNum
lOldSetOpt = DDESetOption("SAFETY")
= DDESetOption("SAFETY",.F.)
mChanNum = DDEInitiate('Excel','SYSTEM') &&EXCEL是否已经打开
**mChanNum = DDEInitiate('Excel','&lcFileName') &&lcFileName某XLS文件名,如:D:\123.XLS
IF mChanNum <> -1
= messagebox('程序 EXCEL 已经打开,请先将其关闭!',64,'系统提示!')
** = messagebox('文件 '+lcFileName+' 已经打开,请先将其关闭!',64,'系统提示!')
= DDETerminate(mChanNum)
= DDESetOption("SAFETY",lOldSetOpt)
RETURN
ENDIF
我自己先试试一下
对了,每次关闭前是否要先保存Excel文件呢????
LOCAL lOldSetOpt,mChanNum
lOldSetOpt = DDESetOption("SAFETY")
= DDESetOption("SAFETY",.F.)
mChanNum = DDEInitiate('Excel','SYSTEM') &&EXCEL是否已经打开
**mChanNum = DDEInitiate('Excel','&lcFileName') &&lcFileName某XLS文件名,如:D:\123.XLS
IF mChanNum <> -1
= messagebox('程序 EXCEL 已经打开,请先将其关闭!',64,'系统提示!')
** = messagebox('文件 '+lcFileName+' 已经打开,请先将其关闭!',64,'系统提示!')
= DDETerminate(mChanNum)
= DDESetOption("SAFETY",lOldSetOpt)
RETURN
ENDIF
这段看不太明白,给指点一下,怎么判断EXCEL文件已经打开