将现有记录集克隆到 Excel 记录集

发布于 2024-08-05 20:32:03 字数 618 浏览 8 评论 0原文

我有一个记录集，其中包含导入 Excel 文件所需的所有数据。有没有一种方法可以将实时记录集克隆到打开 Excel 文件时创建的记录集？

这是我现在正在尝试的，但没有运气。

Dim connection : Set connection = Server.CreateObject("ADODB.Connection")

connection.Open "Driver={Microsoft Excel Driver (*.xls)}; DriverId=790; 
                 Dbq=c:\MyExcel.xls;" & _
                "DefaultDir=c:\; ReadOnly=False;"
Dim excelRecordset : Set excelRecordset = Server.CreateObject("ADODB.Recordset")
excelRecordset.Open "[SHEET1$]", connection, 2, 3

excelRecordset.AddNew
Set excelRecordset = recordset.clone

excelRecordset.Update
excelRecordset.Close

谢谢。

原文

I have a recordset that contains all of the data needed to import into an Excel file. Is there a way I can clone my live recordset into the one created when I open an Excel file?

Here is what I'm trying right now with no luck.

Dim connection : Set connection = Server.CreateObject("ADODB.Connection")

connection.Open "Driver={Microsoft Excel Driver (*.xls)}; DriverId=790; 
                 Dbq=c:\MyExcel.xls;" & _
                "DefaultDir=c:\; ReadOnly=False;"
Dim excelRecordset : Set excelRecordset = Server.CreateObject("ADODB.Recordset")
excelRecordset.Open "[SHEET1$]", connection, 2, 3

excelRecordset.AddNew
Set excelRecordset = recordset.clone

excelRecordset.Update
excelRecordset.Close

Thank you.

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

时光沙漏 2024-08-12 20:32:03

当您执行此操作时：-

Set excelRecordset = recordset.clone

您将对在 Excel 电子表格上打开的记录集的引用（并且它会关闭）替换为对新记录集的引用。这个新记录集不以任何方式连接到 Excel 电子表格。

使用记录集，您实际上没有太多选择，只能通过源记录集For Each，在目标上AddNew，将每个字段从源分配到目标并Update。

When you do this:-

Set excelRecordset = recordset.clone

You replace the reference to the recordset opened on the excel spreadsheet (and it closes) with a reference to new recordset. This new recordset is not in anyway connected to the excel spreadsheet.

Using recordsets you don't really have much choice but to For Each through the source recordset, AddNew on the destination, assign each field from source to destination and Update.

回复收藏 0 原文

请叫√我孤独 2024-08-12 20:32:03

Excel Range 对象有一个 CopyFromRecordset 方法来完成这项工作。如果您可以创建一个 Excel.Application 对象并打开要写入的工作簿，那么您应该能够使用它。

因此，如果您现有的记录集名为 rs 并且已被填充，则 VBA 中的代码将为：

Worksheets("SHEET1").Cells(2, 1).CopyFromRecordset rs

然后您可以迭代 rs.Fields 来填充字段名称

Dim fld As Field
Dim i As Integer
i = 1
With Worksheets("SHEET1")
    For Each fld in rs.Fields
        .Cells(1, i).Value = fld.Name
        i = i + 1
    Next fld
End With

不不过，确定将其转化为您的场景是多么容易。

或者，如果您正在使用的驱动程序提供该功能，您可能需要查看这篇知识库文章

Excel Range objects have a CopyFromRecordset method that should do the job. If you can create an Excel.Application object and open the workbook you want to write to then you should be able to use this.

So if your existing recordset is named rs and has already been populated, the code in VBA would be:

Worksheets("SHEET1").Cells(2, 1).CopyFromRecordset rs

You can then iterate over rs.Fields to fill in the field names

Dim fld As Field
Dim i As Integer
i = 1
With Worksheets("SHEET1")
    For Each fld in rs.Fields
        .Cells(1, i).Value = fld.Name
        i = i + 1
    Next fld
End With

Not sure how easy that would be to translate to your scenario though.

Alternatively, if it is available with the driver you are using, you may want to look into the SELECT INTO and INSERT INTO syntax described in this Knowledge Base article

回复收藏 0 原文

~没有更多了~