将数据数组传输到工作表上的范围

问题描述

我想将VB数组中的数据复制到Excel中。使用该方法逐个单元传输数据，传输10,000多个记录花费的时间太长。有一种更快捷的方法就是传输数据数组。我有语法等但似乎无法将其显示在我的Excel电子表格上。



数组如下所示



'创建一个包含3列和4行的数组

DataArray（0,0）=帐户

DataArray（1,0）=史密斯

DataArray（2,0）=John

DataArray（0,1）=账户

DataArray（1,1 ）=绿色

DataArray（2,1）=Jill

DataArray（0,2）=账户

DataArray （1,2）=布朗

DataArray（2,2）=杰夫

DataArray（0,3）=HR

DataArray（1,3）=Neil

DataArray（2,3）=Anne





代码如下



'在第1行的工作表中添加标题。

oSheet = oBook.Worksheets（1 ）

oSheet.Range（A1）。Value =Department

oSheet.Range（B1）。Value =Surname
$ b $博Sheet.Range（C1）。Value =Forename



'从数组A2开始将数组传输到工作表。

oSheet.Range（A2）。调整大小（4,3）.Value = DataArray



我在Excel电子表格中得到的结果是



部门姓氏姓名

账户账户账户等



我知道哪里出错了？

I want to copy data from a VB array into Excel. Using the method transfer data cell by cell, transferring 10,000 plus records was taking too long. There is a quicker way and that is transferring the array of data. I have the syntax etc but cannot seem to get it displayed on my Excel spreadsheet.

The array is like as follows

'Create an array with 3 columns and 4 rows
DataArray(0, 0) = "Accounts"
DataArray(1,0) = "Smith"
DataArray(2,0) = "John"
DataArray(0, 1) = "Accounts"
DataArray(1,1) = "Green"
DataArray(2,1) = "Jill"
DataArray(0, 2) = "Accounts"
DataArray(1,2) = "Brown"
DataArray(2,2) = "Jeff"
DataArray(0, 3) = "HR"
DataArray(1,3) = "Neil"
DataArray(2,3) = "Anne"


Code is as follows

'Add headers to the worksheet on row 1.
oSheet = oBook.Worksheets(1)
oSheet.Range("A1").Value = "Department"
oSheet.Range("B1").Value = "Surname"
oSheet.Range("C1").Value = "Forename"

'Transfer the array to the worksheet starting at cell A2.
oSheet.Range("A2").Resize(4, 3).Value = DataArray

The result I get in the Excel spreadsheet is

DepartmentSurname Forename
AccountsAccounts Accounts etc

Any idea where I am going wrong?

推荐答案

Dim MyArray(NumRows - 1, NumColumns - 1) as String
For row as Integer = 0 to NumRows - 1
    For column as Integer = 0 to NumColumns - 1
        MyArray(row, col) = row & "_" & column
    Next column
Next row

这篇关于将数据数组传输到工作表上的范围的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！