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

高手帮忙：从access中取出数据插入到excel中结果总是不能差到正确的位置上大家帮忙

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

高手帮忙：从access中取出数据插入到excel中结果总是不能差到正确的位置上大家帮忙

发表于：2007-09-04 13:21:13 楼主

outsql = " "
outsql = "insert into [excel 8.0;database=c:\mht\export\マスタ取込シート.xls].[部門マスタ$]（部門コード,部門名, tabファイル名) select departmentcd,departmentname,tablename from mst_department "
conn.ＥＸＥＣute （outsql)
请问如何定位从第二行第一列开始插入阿

发表于：2007-09-04 23:57:481楼得分:0

首先:在菜单 "工程 "的 "引用 "中添加excel引用;

public myexcel as new excel.application
public mybook as new excel.workbook
public mysheet as new excel.worksheet

public sub openexcel（)
set myexcel = createobject（ "excel.application ")
set mybook = myexcel.workbooks.add
set mysheet = mybook.worksheets（1)
end sub

public sub closeexcel（)
set myexcel = nothing
end sub

'-----------------------------------------------
public cn as new adodb.connection
public rs as new adodb.recordset

public sub openconn（)
set cn = new adodb.connection
set rs = new adodb.recordset
cn.cursorlocation = aduseclient
cn.open "provider=microsoft.jet.oledb.4.0;data source= " & app.path & "\data\data.mdb;jet oledb:database password=;persist security info=false; "

end sub

'关闭数据库连接
public sub closeconn（)
rs.close
set rs = nothing
cn.close
set cn = nothing
end sub

'-----------------------------------------------------------

private sub to_excel（)
k = 0

call openconn

'sql = "select * from 供应商表单 order by 表单id "

sql = "select 表单id,日期,供应商名称,采购员,是否有票客户,发票号码,货款, 付款,补单,备注 from 供应商表单 where 帐套连接id= ' " & accid & " ' "

rs.open sql, cn, 1, 1
if rs.recordcount <= 0 then
msgbox "没有可以导出的记录！ ", 48, "错误提示 "
exit sub
else
call openexcel
'添加excel列头~~~~~~~~~~~~~~~~~~~~~~~
mysheet.cells（1, 1) = imagecombo1.selecteditem.text
mysheet.cells（2, 1) = "表单id "
mysheet.cells（2, 2) = "日期 "
mysheet.cells（2, 3) = "供应商名称 "
mysheet.cells（2, 4) = "采购员 "
mysheet.cells（2, 5) = "是否有票客户 "
mysheet.cells（2, 6) = "发票号码 "
mysheet.cells（2, 7) = "应付货款 "
mysheet.cells（2, 8) = "已付货款 "
mysheet.cells（2, 9) = "补单 "
mysheet.cells（2, 10) = "备注 "

j = 3

do while not rs.eof

for i = 0 to rs.fields.count - 1
mysheet.cells（j, i + 1) = rs.fields（i).value
next i
j = j + 1
rs.movenext
k = k + 1
loop
end if

mysheet.cells（j, 7) = "hahahah得！ "

frame1.visible = false
progressbar1.value = 0
myexcel.visible = true

call closeconn
call closeexcel

end sub

发表于：2007-09-04 23:58:562楼得分:0

或简单测试

首先:在菜单 "工程 "的 "引用 "中添加excel引用;

'增加到模块
public myexcel as new excel.application
public mybook as new excel.workbook
public mysheet as new excel.worksheet

public sub openexcel（)
set myexcel = createobject（ "excel.application ")
set mybook = myexcel.workbooks.add
set mysheet = mybook.worksheets（1)
end sub

public sub closeexcel（)
set myexcel = nothing
end sub

--------------------------------------------------------------
private sub command1_click（)

call openexcel
'添加excel列头~~~~~~~~~~~~~~~~~~~~~~~
mysheet.cells（1, 1) = "hello "
mysheet.cells（2, 1) = "aaaaaa "
mysheet.cells（2, 2) = "bbbbbb "
mysheet.cells（2, 3) = "cccccc "
mysheet.cells（2, 4) = "dddddd "
mysheet.cells（2, 5) = "eeeeee "
mysheet.cells（2, 6) = "ffffff "
mysheet.cells（2, 7) = "gggggg "
mysheet.cells（2, 8) = "hhhhhh "
mysheet.cells（2, 9) = "iiiiii "
mysheet.cells（2, 10) = "jjjjjj "

myexcel.visible = true

call closeexcel

其他资讯

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

高手帮忙：从access中取出数据插入到excel中 结果总是不能差到正确的位置上 大家帮忙

高手帮忙：从access中取出数据插入到excel中结果总是不能差到正确的位置上大家帮忙