如何将品种工作簿名称及对应的工作表的E列数据写入数组

cicnmg

将品种工作簿名称及对应的工作表的E列数据写入数组

chxw68

没看懂你的要求。

cicnmg

chxw68 发表于 2016-10-9 21:25
没看懂你的要求。

把工作簿名称写入数组，把工作表sheet1中E列数据写入数组，没成功。怎么解决？

cicnmg

“品种日周期”文件夹里有上千种名字不同的excel文件*.csv，“品种日周期”文件夹外有一个文件名为《相关性分析结果》的excel启用宏的工作簿。
    需要在excel的工作簿《相关性分析结果》里的sheet1工作表的A列、B列、C列分别写入配对的品种名称（A列里、B列里写）、相关性系数计算结果（C列里写）。
    具体算法：对“品种日周期”文件夹里的上千个不同品种的*.csv文件里的E列数据、每两个进行配对组合排列，并用函数CORREL计算其相关系数。配对与计算后，将配对名分别写入A列、B列，将计算出的相关系数结果写入C列。
     对于每个excel文件里E列数据，CORREL函数里的两个参数取值办法是：例如在本求助中只有三个品种的*.csv文件的情况下，需要PP指数与PTA指数配对计算、PP指数与白糖指数配对计算、PTA指数与白糖指数配对计算每个配对品种之间的相关系数。
    PP指数E列有637行（个）数据、PTA指数E列有2382行（个）数据、白糖指数E列有2613行（个）数据。
PP指数与PTA指数配对计算值 =  =CORREL(PP指数.csv!$E$1:$E$637,PTA指数.csv!$E$1746:$E$2382)       注：1746 = 2382 - 637 + 1
   
PP指数与白糖指数配对计算值  =CORREL(PP指数.csv!$E$1:$E$637,白糖指数.csv!$E$1977:$E$2613)        注：1977 =2613 - 637 + 1
   
PTA指数与白糖指数配对计算 =CORREL(PTA指数.csv!$E$1:$E$2382,白糖指数.csv!$E$232:$E$2613)        注：232 =2613 - 2382 + 1
     
    也就是说：每两个配对品种的E列数据的截取数以最短的数为基准，对于最短的取全部的E列数据，对于最长的取最长的E列里倒数的与最短数据量相等的数据。

wzsy2_mrf

可设一个辅助表，用sql将文件数据读入数据表各列中，列头以文件名命名，之后不过是截取数组问题。为什么要用sql?l因为用它可以在不打开文件的情况下取数，否则几百个文件打开、关闭就卡死你。

cicnmg

wzsy2_mrf 发表于 2016-10-10 10:49
可设一个辅助表，用sql将文件数据读入数据表各列中，列头以文件名命名，之后不过是截取数组问题。为什么要 ...

老师讲的道理我懂了，但我不会SQL啊

wzsy2_mrf

1. Sub aa()
   
2.     Dim cnn As Object, pPath$, temp$, myPath$, j&, shtName$, sql$
   
3.     pPath = ThisWorkbook.Path & "\品种日周期"
   
4.     Set cnn = CreateObject("ADODB.Connection")
   
5.     With cnn
   
6.         If Application.Version = "11.0" Then
   
7.             .Provider = "microsoft.jet.oledb.4.0"
   
8.             .ConnectionString = "extended properties=""excel 8.0;HDR=yes;"";data source=" & ThisWorkbook.FullName
   
9.         Else
   
10.             .Provider = "microsoft.ACE.oledb.12.0"
    
11.             .ConnectionString = "extended properties=""excel 12.0;HDR=yes;"";data source=" & ThisWorkbook.FullName
    
12.         End If
    
13.         .Open
    
14.     End With
    
15.    
    
16.     myPath = pPath
    
17.     temp = Dir(myPath & "*.xls")
    
18.    
    
19.     Do While temp <> ""
    
20.         If InStr(temp, "指数") > 0 Then
    
21.             shtName = Split(temp, ".")(0)
    
22.             sql = "select * from [Excel 12.0;hdr=no;Database=" & myPath & temp & ";].[" & shtName & "$E:E]"
    
23.             j = j + 1
    
24.             Sheet2.Cells(1, j).Value = shtName
    
25.             Sheet2.Cells(2, j).CopyFromRecordset cnn.Execute(sql)
    
26.         End If
    
27.         temp = Dir
    
28.     Loop
    
29.     Set cnn = Nothing
    
30. End Sub
    
31. 
    

复制代码

wzsy2_mrf

先将cva 文件转存为xlsx文件。