vba学习_数据写入

来源：互联网发布：动画视频制作软件编辑：程序博客网时间：2024/05/16 05:49

这个是从多个子文件夹读取同名文件并写入同一个工作表的程序，适用于数据整理

Sub Anchordatarefresh()Dim data, row%, col%'定义数组all_data，40行*5列，存放所有数据Dim all_data(1 To 40, 1 To 5)'定义文件名数组addarrDim addarr(1 To 10)addarr(1) = "BasketballDrillresult"addarr(2) = "chinaspeedresult"addarr(3) = "slideeditresult"addarr(4) = "slideshowresult"addarr(5) = "MAPresult"addarr(6) = "Programmingresult"addarr(7) = "robotresult"addarr(8) = "SlideShow_444result"addarr(9) = "WebBrowsingresult"addarr(10) = "WordEditingresult"'定义子文件名数组addfigarrDim addfigarr(1 To 4)addfigarr(1) = 22addfigarr(2) = 27addfigarr(3) = 32addfigarr(4) = 37'依次从文件夹中读取文件，将文件中的数据读入dataarr一维数组For namecounter = 1 To 4For figcounter = 1 To 4Open ThisWorkbook.Path & "\" & addarr(namecounter) & "\" & addfigarr(figcounter) & "\logBD-Rate.txt" For Input As #1   dataarr = Split(StrConv(InputB(LOF(1), 1), vbUnicode), vbCrLf)   Close #1'将dataarr数组的值赋到all_data的对应行中For counter = 1 To 5all_data((namecounter - 1) * 4 + figcounter, counter) = dataarr(counter - 1)Next'将all_data中的值输出到420的对应单元格（all_data的值从（1,1）开始，单元格顺序为（2+1,3+1）开始For colcounter = 1 To 5   Worksheets("420").Cells(2 + (namecounter - 1) * 4 + figcounter, 3 + colcounter) = all_data((namecounter - 1) * 4 + figcounter, colcounter) * 1NextNextNext'依次从文件夹中读取文件，将文件中的数据读入dataarr一维数组For namecounter = 5 To 10For figcounter = 1 To 4

'ThisWorkbook.Path即当前excel文件所处的文件位置，相当于相对路径的使用，此处vba的语法与java类似，&&即等于java中的++，用于变量与字符串的内嵌Open ThisWorkbook.Path & "\" & addarr(namecounter) & "\" & addfigarr(figcounter) & "\logBD-Rate.txt" For Input As #1   dataarr = Split(StrConv(InputB(LOF(1), 1), vbUnicode), vbCrLf)   Close #1'将dataarr数组的值赋到all_data的对应行中For counter = 1 To 5all_data((namecounter - 1) * 4 + figcounter, counter) = dataarr(counter - 1)Next'将all_data中的值输出到444的对应单元格（all_data的值从（17,1）开始，单元格顺序为（2+1,3+1）开始）For colcounter = 1 To 5   Worksheets("444").Cells(2 + (namecounter - 5) * 4 + figcounter, 3 + colcounter) = all_data((namecounter - 1) * 4 + figcounter, colcounter) * 1NextNextNext'复制对应数据到另一个Anchor区域Worksheets("420").Range("d24:h39") = Worksheets("420").Range("d3:h18").ValueWorksheets("444").Range("d33:h56") = Worksheets("444").Range("d3:h26").ValueEnd Sub

0 0