您的位置：程序门 -> vb -> 基础类

菜鸟提问：vb中access库导出成excel的问题~ ，请高手指点

[收藏此页] [打印本页]选择字色：背景色：字体：[大][中][小]

菜鸟提问：vb中access库导出成excel的问题~ ，请高手指点[已结贴,结贴人:alextchen]

发表于：2007-08-01 15:21:04 楼主

小菜提问：
在vb中做了个“导出按钮”，把access库中的数据导出到excel中……
导出成功后打开c:\excel\gift.xls文件，就闪了下无法打开excel文件
到任务管理器中，才看到有excel.exe进程（此时无excel文件运行），
关闭excel.exe进程后，才能正常打开c:\excel\gift.xls文件……
请问这是什么问题？？该怎么解决呢？？

“导出“代码如下

private sub cmdoutput_click（)
dim cn as new adodb.connection
dim rs as new adodb.recordset
dim sql as string

cn.open "provider=microsoft.jet.oledb.4.0;data source=d:\gift\gift.mdb "

sql = "select * from [ly_gift] "

rs.source = sql
set rs.activeconnection = cn
rs.locktype = adlockoptimistic
rs.cursorlocation = aduseclient
rs.open sql, cn

if rs.recordcount < 1 then
msgbox "没有数据导出 ", vbokonly + vbcritical, "错误提示 "
else

if dir（ "c:\excel ", vbdirectory) = " " then
mkdir （ "c:\excel ")
end if

if dir（ "c:\excel ") <> " " then
kill "c:\excel\gift.xls "
end if
end if

dim i as integer
dim j as integer
dim xlexcel as new excel.application
dim xlbook as new excel.workbook
dim xlsheet as new excel.worksheet
set xlbook = xlexcel.workbooks.add
set xlsheet = xlexcel.worksheets.add

xlsheet.cells.columns（5).columnwidth = 20
xlsheet.cells（1, 1) = "联名卡号 "
xlsheet.cells（1, 2) = "领用 "
xlsheet.cells（1, 3) = "日期 "
xlsheet.cells（1, 4) = "时间 "
xlsheet.cells（1, 5) = "操作员 "

for i = 2 to rs.recordcount + 1
for j = 1 to rs.fields.count
xlsheet.cells（i, j) = rs.fields.item（j - 1).value
next j
rs.movenext
next i
xlbook.saveas fileformat:=xlexcel9795
xlbook.saveas filename:= "c:\excel\gift.xls "

rs.close
cn.close

end sub

发表于：2007-08-01 15:44:421楼得分:15

工作表存盘后加入以下二行就行了

xlbook.close ' 关闭工作表。
xlexcel.quit '用 quit 方法关闭 microsoft excel　　 '释放对象

发表于：2007-08-01 16:00:332楼得分:5

private sub cmdoutput_click（)
dim cn as new adodb.connection
dim rs as new adodb.recordset
dim sql as string

cn.open "provider=microsoft.jet.oledb.4.0;data source=d:\gift\gift.mdb "

sql = "select * from [ly_gift] "

rs.source = sql
set rs.activeconnection = cn
rs.locktype = adlockoptimistic
rs.cursorlocation = aduseclient
rs.open sql, cn

if rs.recordcount < 1 then
msgbox "没有数据导出 ", vbokonly + vbcritical, "错误提示 "
else

if dir（ "c:\excel ", vbdirectory) = " " then
mkdir （ "c:\excel ")
end if

if dir（ "c:\excel\gift.xls ") <> " " then
kill "c:\excel\gift.xls "
end if
end if

dim i as integer
dim j as integer
dim xlexcel as new excel.application
dim xlbook as excel.workbook
dim xlsheet as excel.worksheet
set xlbook = xlexcel.workbooks.add
set xlsheet = xlexcel.worksheets.add

xlsheet.cells.columns（5).columnwidth = 20
xlsheet.cells（1, 1) = "联名卡号 "
xlsheet.cells（1, 2) = "领用 "
xlsheet.cells（1, 3) = "日期 "
xlsheet.cells（1, 4) = "时间 "
xlsheet.cells（1, 5) = "操作员 "

for i = 2 to rs.recordcount + 1
for j = 1 to rs.fields.count
xlsheet.cells（i, j) = rs.fields.item（j - 1).value
next j
rs.movenext
next i
xlbook.saveas fileformat:=xlexcel9795
xlbook.saveas filename:= "c:\excel\gift.xls "

rs.close
cn.close

set xlsheet = nothing
set xlbook=nothing
xlexcel.quit
set xlexcel=nothing

end sub

发表于：2007-08-01 16:01:473楼得分:5

晕，忘了关闭workbook了
set xlsheet = nothing
xlbook.close
set xlbook=nothing
xlexcel.quit
set xlexcel=nothing

发表于：2007-08-01 16:03:444楼得分:5

还有
dim xlexcel as new excel.application
dim xlbook as new excel.workbook
dim xlsheet as new excel.worksheet
workbook和worksheet不要用new，而直接用对象集的add方法

其他资讯