Dim X1 As Excel.Application Set X1 = CreateObject("Excel.Application")
Dim W1 As Excel.Workbook Set W1 = X1.Workbooks.Open("c:\Tmp\pos.xls") W1.Activate With W1.ActiveSheet.QueryTables.Add(Connection:=rstRecordset, Destination:=Range("A1")) ..... End With Dim X1 As Excel.Application Set X1 = CreateObject("Excel.Application")
Dim W1 As Excel.Workbook Set W1 = X1.Workbooks.Open("c:\Tmp\pos.xls") W1.Activate With W1.ActiveSheet.QueryTables.Add(Connection:=rstRecordset, Destination:=Range("A1")) ..... End With
感谢您使用微软产品。 在您的问题描述中,您没有提到错误产生于哪一行,也没有给出确切的错误号和错误描述. 据推测,可能会有以下几个地方导致错误: 1. 由于要跨进程地将ADO Recordset从VB传入Excel,其CursorLocation必须设为客户端(adUseClient). 关于这一点, 可以参见文章http://support.microsoft.com/support/kb/articles/Q263/4/98.ASP 2. 将VBA代码移入VB中时,还有很重要的一点是要将所有的对Excel对象的相对引用改为绝对引用. 详细内容可参见下例.以下代码是在您原代码的基础上略作改动后的代码,你可以试一下. Dim cnnConnect As ADODB.Connection Dim rstRecordset As ADODB.Recordset Set cnnConnect = New ADODB.Connection cnnConnect.Open "Provider=SQLOLEDB;" & _ "Data Source=oo;" & _ "initial catalog=northwind;" & _ "User ID=sa;Password=sa;" Set rstRecordset = New ADODB.Recordset '加入此行 rstRecordset.CursorLocation = adUseClient rstRecordset.Open _ Source:="Select companyname,address From customers", _ ActiveConnection:=cnnConnect, _ CursorType:=adOpenDynamic, _ LockType:=adLockReadOnly, _ Options:=adCmdText
Dim xlapp As Excel.Application Dim xlBook As Excel.Workbook Dim xlSheet As Excel.WorksheetSet xlapp = New Excel.Application xlapp.Visible = TrueSet xlBook = xlapp.Workbooks.Open("c:\tmp\pos.xls") Set xlSheet = xlBook.Worksheets(1)xlBook.ActivateWith xlSheet.QueryTables.Add(rstRecordset, xlSheet.Range("A1")) .Name = "Contact List" .FieldNames = True .RowNumbers = False .FillAdjacentFormulas = False .PreserveFormatting = True .RefreshOnFileOpen = False .BackgroundQuery = True .RefreshStyle = xlInsertDeleteCells .SavePassword = True .SaveData = True .AdjustColumnWidth = True .RefreshPeriod = 0 .PreserveColumnInfo = True .Refresh BackgroundQuery:=False End With如果以上代码在您那里还有问题,那您就需要先确定错误发生的位置,然后再一步一步确定错误发生的原因. - 微软亚洲技术中心 VB技术支持本贴子仅供CSDN的用户作为参考信息使用。其内容不具备任何法律保障。您需要考虑到并承担使用此信息可能带来的风险。具体事项可参见使用条款 (http://www.csdn.net/microsoft/terms.shtm)。
ActiveSheet对象怎么建?
你能把完整代码执行正确吗?我把我所有分数给你!!
Dim rstRecordset As ADODB.Recordset
Set cnnConnect = New ADODB.Connection
cnnConnect.Open "Provider=SQLOLEDB;" & _
"Data Source=oo;" & _
"initial catalog=northwind;" & _
"User ID=sa;Password=sa;"
Set rstRecordset = New ADODB.Recordset
rstRecordset.Open _
Source:="Select companyname,address From customers", _
ActiveConnection:=cnnConnect, _
CursorType:=adOpenDynamic, _
LockType:=adLockReadOnly, _
Options:=adCmdText
Dim xlapp as Excel.Application
Set xlapp = New Excel.Application
xlapp.Visible = Truexlapp.Workbooks.Open ("c:\tmp\pos.xls")
xlapp.Workbooks("pos.xls").ActivateWith xlapp.ActiveSheet.QueryTables.Add(Connection:=rstRecordset, Destination:=Range("A1"))
.Name = "Contact List"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = True
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.PreserveColumnInfo = True
.Refresh BackgroundQuery:=False
End With
End Sub
你能将 可执行代码Email给我吗?
不胜感激!!
[email protected]
(我已加了Excel 9.0 object library,Office 9.0 object library, visual basic for application 等等,好几个类库)
Set X1 = CreateObject("Excel.Application")
Dim W1 As Excel.Workbook Set W1 = X1.Workbooks.Open("c:\Tmp\pos.xls")
W1.Activate
With W1.ActiveSheet.QueryTables.Add(Connection:=rstRecordset, Destination:=Range("A1"))
.....
End With
Dim X1 As Excel.Application
Set X1 = CreateObject("Excel.Application")
Dim W1 As Excel.Workbook Set W1 = X1.Workbooks.Open("c:\Tmp\pos.xls")
W1.Activate
With W1.ActiveSheet.QueryTables.Add(Connection:=rstRecordset, Destination:=Range("A1"))
.....
End With
在您的问题描述中,您没有提到错误产生于哪一行,也没有给出确切的错误号和错误描述.
据推测,可能会有以下几个地方导致错误:
1. 由于要跨进程地将ADO Recordset从VB传入Excel,其CursorLocation必须设为客户端(adUseClient). 关于这一点, 可以参见文章http://support.microsoft.com/support/kb/articles/Q263/4/98.ASP
2. 将VBA代码移入VB中时,还有很重要的一点是要将所有的对Excel对象的相对引用改为绝对引用. 详细内容可参见下例.以下代码是在您原代码的基础上略作改动后的代码,你可以试一下.
Dim cnnConnect As ADODB.Connection
Dim rstRecordset As ADODB.Recordset
Set cnnConnect = New ADODB.Connection
cnnConnect.Open "Provider=SQLOLEDB;" & _
"Data Source=oo;" & _
"initial catalog=northwind;" & _
"User ID=sa;Password=sa;"
Set rstRecordset = New ADODB.Recordset
'加入此行
rstRecordset.CursorLocation = adUseClient
rstRecordset.Open _
Source:="Select companyname,address From customers", _
ActiveConnection:=cnnConnect, _
CursorType:=adOpenDynamic, _
LockType:=adLockReadOnly, _
Options:=adCmdText
Dim xlapp As Excel.Application
Dim xlBook As Excel.Workbook
Dim xlSheet As Excel.WorksheetSet xlapp = New Excel.Application
xlapp.Visible = TrueSet xlBook = xlapp.Workbooks.Open("c:\tmp\pos.xls")
Set xlSheet = xlBook.Worksheets(1)xlBook.ActivateWith xlSheet.QueryTables.Add(rstRecordset, xlSheet.Range("A1"))
.Name = "Contact List"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = True
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.PreserveColumnInfo = True
.Refresh BackgroundQuery:=False
End With如果以上代码在您那里还有问题,那您就需要先确定错误发生的位置,然后再一步一步确定错误发生的原因.
- 微软亚洲技术中心 VB技术支持本贴子仅供CSDN的用户作为参考信息使用。其内容不具备任何法律保障。您需要考虑到并承担使用此信息可能带来的风险。具体事项可参见使用条款 (http://www.csdn.net/microsoft/terms.shtm)。