不好意思,我刚申请,所以没有分,各位见谅!
为了解决导出数据到EXCEL分页问题,我在VB6中如下代码,但碰到计算字段赋值出错问题,请问怎样解决(谢谢)?
为了解决导出数据到EXCEL分页问题,我在VB6中如下代码,但碰到计算字段赋值出错问题,请问怎样解决(谢谢)?
解决方案 »
- 如何将数据库指定值赋给文本框,求解!!!
- 我又回来了,这里还有人记得我吗?为了聚集人气散200分(散分)
- 关于ERP软件里,“用户管理”这个功能如何来设计?
- 如何取得D:下的全部文本文件名称?
- 紧急求助...
- 我刚学delphi,请问这个delphi写的dll,在vb中为什么老是错误,谁能告诉我???
- 奇怪的RichTextBox控件显示文本时的内存溢出问题
- 手机短消息模块外包!
- 怎样学习使用API(能解决问题马上给高分)
- 关于ActiveBar的一点小问题
- listview的view模式lvwIcon,排一定数目的后换行
- 两个数据库之间的Insert into select 问题,请高手帮忙看看。。
If Rs_Data.State = 1 Then Rs_Data.Close
Rs_Data.CursorLocation = adUseClient
Rs_Data.Open m_SQL, obConn, adOpenKeyset, adLockReadOnly
Set Rs_Data.ActiveConnection = Nothing
strStruct = Replace(m_SQL, "Select", "Select Top 0", 1, 1, vbTextCompare)
On Error GoTo ErrHandle:
With Rs_Data
'记录总数
Irowcount = .RecordCount
'栏位总数
Icolcount = .Fields.Count
End With
'设置每页最大行数
lngPageSize = 65000
intPageCount = Round(Irowcount / lngPageSize, 0)
If intPageCount * lngPageSize < Irowcount Then
intPageCount = intPageCount + 1
End If
Set xlApp = CreateObject("Excel.Application")
Set xlBook = Nothing
Set xlSheet = Nothing
Set xlBook = xlApp.Workbooks().Add
xlApp.Visible = True
'添加Sheet数量
mIndex = intPageCount - xlBook.Sheets.Count
If mIndex > 0 Then
Set xlSheet = xlBook.Worksheets("sheet3")
xlBook.Sheets.Add , xlSheet, mIndex
End If
If intPageCount > 1 Then
If Rs_Temp.State = 1 Then Rs_Temp.Close
Rs_Temp.CursorLocation = adUseClient
Rs_Temp.Open strStruct, obConn, adOpenKeyset, adLockBatchOptimistic
Set Rs_Temp.ActiveConnection = Nothing
End If
For mIndex = 1 To intPageCount
Set xlSheet = xlBook.Worksheets("sheet" + CStr(mIndex))
xlSheet.Select
xlSheet.Range("A1").Value = "正在处理第 " + CStr(mIndex) + " 页数据,请稍等......"
If intPageCount > 1 Then
If Rs_Temp.RecordCount > 0 Then
Rs_Temp.CancelBatch
End If
Rs_Data.MoveFirst
Rs_Data.Move lngPageSize * (mIndex - 1), 1
mRow = 1
Do Until (mRow > lngPageSize Or Rs_Data.EOF)
Rs_Temp.AddNew
For mCol = 0 To Rs_Data.Fields.Count - 1
'有一个字段为计算字段 Rs_Temp(mCol).Value = Rs_Data(mCol).Value
Next
Rs_Data.MoveNext
mRow = mRow + 1
Loop
Else
Set Rs_Temp = Rs_Data.Clone
End If
Irowcount = Rs_Temp.RecordCount
'添加查询语句,导入EXCEL资料
Set xlQuery = xlSheet.QueryTables.Add(Rs_Temp, xlSheet.Range("a1")) xlQuery.Refresh
'写列标题
If m_Title <> "" Then
ArrTitle = Split(m_Title, ",")
For mCol = LBound(ArrTitle) To UBound(ArrTitle)
xlSheet.Cells(1, mCol + 1) = ArrTitle(mCol)
Next
End If
'设表格边框样式
xlSheet.Range(xlSheet.Cells(1, 1), xlSheet.Cells(Irowcount + 1, Icolcount)).Borders.LineStyle = xlContinuous
Next
Set xlSheet = xlBook.Worksheets("sheet1")
xlSheet.Select
xlApp.Application.Visible = True
Set xlApp = Nothing '"交还控制给Excel
Set xlBook = Nothing
Set xlSheet = Nothing
If Rs_Temp.State = 1 Then Rs_Temp.Close
Set Rs_Temp = Nothing
If Rs_Data.State = 1 Then Rs_Data.Close
Set Rs_Data = Nothing
If Rs_Data.State = 1 Then Rs_Data.Close
Rs_Data.CursorLocation = adUseClient
Rs_Data.Open m_SQL, obConn, adOpenKeyset, adLockReadOnly
Set Rs_Data.ActiveConnection = Nothing
strStruct = Replace(m_SQL, "Select", "Select Top 0", 1, 1, vbTextCompare)
On Error GoTo ErrHandle:
With Rs_Data
'记录总数
Irowcount = .RecordCount
'栏位总数
Icolcount = .Fields.Count
End With
'设置每页最大行数
lngPageSize = 65000
intPageCount = Round(Irowcount / lngPageSize, 0)
If intPageCount * lngPageSize < Irowcount Then
intPageCount = intPageCount + 1
End If
Set xlApp = CreateObject("Excel.Application")
Set xlBook = Nothing
Set xlSheet = Nothing
Set xlBook = xlApp.Workbooks().Add
xlApp.Visible = True
'添加Sheet数量
mIndex = intPageCount - xlBook.Sheets.Count
If mIndex > 0 Then
Set xlSheet = xlBook.Worksheets("sheet3")
xlBook.Sheets.Add , xlSheet, mIndex
End If
If intPageCount > 1 Then
If Rs_Temp.State = 1 Then Rs_Temp.Close
Rs_Temp.CursorLocation = adUseClient
Rs_Temp.Open strStruct, obConn, adOpenKeyset, adLockBatchOptimistic
Set Rs_Temp.ActiveConnection = Nothing
End If
For mIndex = 1 To intPageCount
Set xlSheet = xlBook.Worksheets("sheet" + CStr(mIndex))
xlSheet.Select
xlSheet.Range("A1").Value = "正在处理第 " + CStr(mIndex) + " 页数据,请稍等......"
If intPageCount > 1 Then
If Rs_Temp.RecordCount > 0 Then
Rs_Temp.CancelBatch
End If
Rs_Data.MoveFirst
Rs_Data.Move lngPageSize * (mIndex - 1), 1
mRow = 1
Do Until (mRow > lngPageSize Or Rs_Data.EOF)
Rs_Temp.AddNew
For mCol = 0 To Rs_Data.Fields.Count - 1
'有一个字段为计算字段(出错在此)
Rs_Temp(mCol).Value = Rs_Data(mCol).Value
Next
Rs_Data.MoveNext
mRow = mRow + 1
Loop
Else
Set Rs_Temp = Rs_Data.Clone
End If
Irowcount = Rs_Temp.RecordCount
'添加查询语句,导入EXCEL资料
Set xlQuery = xlSheet.QueryTables.Add(Rs_Temp, xlSheet.Range("a1")) xlQuery.Refresh
Next
[email protected]
是哪个字段不行,处理一下.鼠标放上面看它的值
if Rs_Data(mCol).name="XXX" then
Rs_Temp(mCol).Value = val(Rs_Data(mCol).Value)
end if
--在表中OweQty設計為計算字段,它等於FetchQty-PressQty
Select PlanDT, JIFAN, PlanQty, BomNo, PINFAN, MPCS, FetchQty, PressQty, OweQty, Re From TA_ForeCastAnalyze
CREATE TABLE [dbo].[TA_ForeCastAnalyze]
(
[PlanDT] [varchar](25) NULL,
[JIFAN] [varchar](25) NULL,
[BomNo] [varchar](50) NULL,
[PINFAN] [varchar](25) NULL,
[FetchQty] [decimal](18, 0) NULL,
[PressQty] [decimal](18, 0) NULL,
[OweQty] AS (isnull([FetchQty],0) - isnull([PressQty],0)),
[Remart] [nvarchar](50) NULL
)
感觉[OweQty]是浪费资源。
For mCol = 0 To Rs_Data.Fields.Count - 1
'有一个字段为计算字段(出错在此)
If Rs_Data.Fields(mCol).Name <> "OweQty" Then
Rs_Temp(mCol).Value = Rs_Data(mCol).Value
End If
Next
dim fld as ADODB.FieldSet Rs_Temp = New ADODB.Recordset
For Each fld in Rs_Data.Fields
Rs_Temp.Fields.Append fld.Name, fld.Type, fld.DefinedSize, _
fld.Attributes Or adFldUnknownUpdatable
Next
Rs_Temp.Open
把 Dim mCol As Integer 定义为长整形即可
Dim mCol As long
原表OweQty字段是计算字段,不允许编辑,也就不允许赋值。最简单的方法用以下语句在你的数据库里建一张空表,让你的strStruct从这张表里出。
原程序基本上没什么改变。CREATE TABLE [dbo].[TA_ForeCastAnalyze1]
(
[PlanDT] [varchar](25) NULL,
[JIFAN] [varchar](25) NULL,
[BomNo] [varchar](50) NULL,
[PINFAN] [varchar](25) NULL,
[FetchQty] [decimal](18, 0) NULL,
[PressQty] [decimal](18, 0) NULL,
[OweQty] [decimal](19, 0) NULL ,
[Remart] [nvarchar](50) NULL
) 以下程序是我做测试用的,你自己稍做修改即可。Dim obConn As New ADODB.Connection
Dim Rs_Data As New ADODB.Recordset
Dim Rs_Temp As New ADODB.Recordset
Dim m_SQL As String
Dim strStruct As StringPrivate Sub Command2_Click()
If Rs_Data.State = 1 Then Rs_Data.Close
Rs_Data.CursorLocation = adUseClient
Rs_Data.Open m_SQL, obConn, adOpenKeyset, adLockReadOnly
Set Rs_Data.ActiveConnection = Nothing
'strStruct = Replace(m_SQL, "Select", "Select Top 0", 1, 1, vbTextCompare)
' --在表中OweQty設計為計算字段,它等於FetchQty-PressQty
'Select PlanDT, JIFAN, PlanQty, BomNo, PINFAN, MPCS, FetchQty, PressQty, OweQty, Re From TA_ForeCastAnalyze'CREATE TABLE [dbo].[TA_ForeCastAnalyze]
'(
' [PlanDT] [varchar](25) NULL,
' [JIFAN] [varchar](25) NULL,
' [BomNo] [varchar](50) NULL,
' [PINFAN] [varchar](25) NULL,
' [FetchQty] [decimal](18, 0) NULL,
' [PressQty] [decimal](18, 0) NULL,
' [OweQty] AS (isnull([FetchQty],0) - isnull([PressQty],0)),
' [Remart] [nvarchar](50) NULL
') 'On Error GoTo ErrHandle:
With Rs_Data
'记录总数
Irowcount = .RecordCount
'栏位总数
Icolcount = .Fields.Count
End With
'设置每页最大行数
lngPageSize = 65000
intPageCount = Round(Irowcount / lngPageSize, 0)
If intPageCount * lngPageSize < Irowcount Then
intPageCount = intPageCount + 1
End If
Set xlApp = CreateObject("Excel.Application")
Set xlBook = Nothing
Set xlSheet = Nothing
Set xlBook = xlApp.Workbooks().Add
xlApp.Visible = True
'添加Sheet数量
mIndex = intPageCount - xlBook.Sheets.Count
If mIndex > 0 Then
Set xlSheet = xlBook.Worksheets("sheet3")
xlBook.Sheets.Add , xlSheet, mIndex
End If
If intPageCount > 1 Then
If Rs_Temp.State = 1 Then Rs_Temp.Close
Rs_Temp.CursorLocation = adUseClient
Rs_Temp.Open strStruct, obConn, adOpenKeyset, adLockBatchOptimistic
Set Rs_Temp.ActiveConnection = Nothing
End If
For mIndex = 1 To intPageCount
Set xlSheet = xlBook.Worksheets("sheet" + CStr(mIndex))
xlSheet.Select
xlSheet.Range("A1").Value = "正在处理第 " + CStr(mIndex) + " 页数据,请稍等......"
If intPageCount > 1 Then
If Rs_Temp.RecordCount > 0 Then
Rs_Temp.CancelBatch
End If
Rs_Data.MoveFirst
'Rs_Data.Move lngPageSize * (mIndex - 1), 1
mRow = 1
Do Until (mRow > lngPageSize Or Rs_Data.EOF)
Rs_Temp.AddNew
For mcol = 0 To Rs_Data.Fields.Count - 1
'有一个字段为计算字段(出错在此)
Rs_Temp(mcol).Value = Rs_Data(mcol).Value
Next
Rs_Data.MoveNext
mRow = mRow + 1
Loop
Else
Set Rs_Temp = Rs_Data.Clone
End If
Irowcount = Rs_Temp.RecordCount
'添加查询语句,导入EXCEL资料
Set xlQuery = xlSheet.QueryTables.Add(Rs_Temp, xlSheet.Range("a1")) xlQuery.Refresh
NextEnd SubPrivate Sub Form_Load()
obConn.ConnectionString = " Provider=SQLOLEDB.1;Password=dg;Persist Security Info=True;User ID=sa;Initial Catalog=pubs;Data Source=."
obConn.CursorLocation = adUseClient
obConn.Open
m_SQL = "select * from TA_ForeCastAnalyze"
strStruct = "select * from TA_ForeCastAnalyze1"
Rs_Temp.Open strStruct, obConn, adOpenStatic, adLockOptimisticEnd Sub
Dim fld As ADODB.Field
For Each fld In Rs_Data.Fields
Debug.Print fld.Name + vbTab + CStr(fld.Type) + vbTab + CStr(fld.DefinedSize) + vbTab + CStr(fld.Attributes)
Select Case fld.Type
Case 200
Rs_Temp.Fields.Append fld.Name, adVarChar, 500, adFldIsNullable
Case 131
Rs_Temp.Fields.Append fld.Name, adDouble, , adFldIsNullable
Case Else
Rs_Temp.Fields.Append fld.Name, fld.Type, fld.DefinedSize, adFldIsNullable
End Select
Next
Rs_Temp.Open End If