在用vb做程序的时候,它本身的报表并不太好使用,因此应用excel输出数据,是一个好方法,以下是一组操纵excel的函数据,希望能帮助大家.
'excel vba控制函数
'write by weihua 2000.10.12
'检测文件
function checkfile(byval strfile as string) as boolean
dim filexls as object
set filexls = createobject("scripting.filesystemobject")
if isnull(strfile) or strfile = "" then
checkfile = false
exit function
end if
if filexls.fileexists(strfile) = false then
checkfile = false
set filexls = nothing
exit function
else
checkfile = true
set filexls = nothing
end if
end function
'检测工作表
function checksheet(byval strsheet as string, byval strworkbook as string, xlcheckapp as excel.application) as boolean
dim l as integer
dim checkworkbook as excel.workbook
if checkfile(strworkbook) and strsheet <> "" and not isnull(strsheet) then
for l = 1 to xlcheckapp.workbooks.count
if getpath(xlcheckapp.workbooks(l).path) & xlcheckapp.workbooks(l).name = strworkbook then
set checkworkbook = xlcheckapp.workbooks(l)
exit for
end if
next l
set checkworkbook = xlcheckapp.workbooks.open(strworkbook)
for l = 1 to checkworkbook.worksheets.count
if checkworkbook.worksheets(l).name = trim(strsheet) then
checksheet = true
exit for
end if
next l
else
msgbox "工作表不存在，可能是由文件名或工作表名引起的！"
checksheet = false
end if
end function
'建立工作表
'createmethod:1追加
'createmethod:2覆盖
function createsheet(byval strsheetname as string, byval strworkbook as string, byval createmethod as integer, xlcreateapp as excel.application) as boolean
dim xlcreatesheet as excel.worksheet
if checkfile(strworkbook) then
xlcreateapp.workbooks.open (strworkbook)
if createmethod = 1 then
if checksheet(strsheetname, strworkbook, xlcreateapp) = false then
set xlcreatesheet = xlcreateapp.worksheets.add
xlcreatesheet.name = strsheetname
xlcreateapp.activeworkbook.save
createsheet = true
set xlcreatesheet = nothing
else
'msgbox strsheetname & "工作表已存在！"
createsheet = false
set xlcreatesheet = nothing
end if
elseif createmethod = 2 then
if checksheet(strsheetname, strworkbook, xlcreateapp) = true then
set xlcreatesheet = xlcreateapp.worksheets(strsheetname)
xlcreatesheet.cells.select
xlcreatesheet.cells.delete
xlcreateapp.activeworkbook.save
createsheet = true
set xlcreatesheet = nothing
else
'msgbox strsheetname & "工作表不存在！"
createsheet = false
