|
我在同一个目录下,有几百个相同的excel文件,格式如下:
我想批量把这些excel文件的行和列全部转置,我在论坛上已经找到了一段excel2003的VBA代码:
Sub macro()
'----------1、找文件
Dim fs, mypath, Frfilename
On Error Resume Next
Application.ScreenUpdating = False
mypath = ThisWorkbook.Path
Frfilename = "*.xls"
Set fs = Application.FileSearch
With fs
.NewSearch
.SearchSubFolders = True
.LookIn = mypath
.Filename = Frfilename
If .Execute(SortBy:=msoSortByFileName) > 0 Then
n = .FoundFiles.Count
For i = 1 To n
'---------------转换
Transformer .FoundFiles(i)
Next
End If
End With
Set fs = Nothing
Application.ScreenUpdating = True
End Sub
Function Transformer(ByVal pth As String)
If pth = ThisWorkbook.Path & Application.PathSeparator & ThisWorkbook.Name Then Exit Function
Dim xarr, yarr, i%, j%, Numb%
Workbooks.Open (pth)
With ActiveWorkbook
Numb = Range("IV1").End(xlToLeft).Column
ReDim xarr(1 To 2, 1 To Numb), yarr(1 To Numb, 1 To 2)
xarr = Range("A1", Cells(2, Numb))
For i = 1 To 2
For j = 1 To Numb
yarr(j, i) = xarr(i, j)
Next
Next
Cells.Clear
Range("A1", Cells(Numb, 2)) = yarr
.Close True
End With
End Function
该帖子链接如下:http://club.excelhome.net/thread-481392-1-1.html
但是这个代码只是转置第一列的,我想转置第一第二列或者只转置第二列,请问各位代码应该如何修改呢?
excel文件在这:
2014-08-25-09-B4-1.zip
(15.33 KB, 下载次数: 15)
|
|