|
楼主 |
发表于 2009-7-2 16:20
|
显示全部楼层
谢谢了,自己在Tq-laoyu和大家的帮助下,终于算差不多了,其中也发现了些问题。还请大家多帮忙。我做了两个sub去调用
Sub Test()
Dim i As Integer
Dim strPath As String
Dim wbk As Workbook
strPath = "E:\xls"
'遍历文件夹
With Application.FileSearch
.LookIn = strPath
.SearchSubFolders = True
.Filename = "*.*"
If .Execute > 0 Then
For i = 1 To .FoundFiles.Count
Application.DisplayAlerts = False
Range("A" & i) = .FoundFiles(i)
Set wbk = Workbooks.Open(Range("A" & i).Text)
Call MovingRow(wbk)
Next i
End If
End With
End Sub
Sub MovingRow(wb)
Dim irow As Integer
Dim i As Integer
Dim iTop As Integer
Dim sCol As String
Dim Str As String
Dim Stemp As String
Dim bFound As Boolean
iTop = 0
irow = wb.Sheets(1).UsedRange.Rows.Count
Str = ""
bFound = False
For i = 1 To irow
'判断姓名列
If wb.Sheets(1).Cells(i, "C") = "姓名" Then
sCol = "C"
Else If wb.Sheets(1).Cells(i, "B") = "姓名" Then
sCol = "B"
End If
With wb.Sheets(1).Range(sCol & i)
wb.Sheets(1).Range(sCol & i).UnMerge
Stemp = wb.Sheets(1).Cells(i, sCol)
If .Borders(xlEdgeTop).LineStyle = 1 Then ‘上边框
iTop = i
Str = Str + Stemp
ElseIf .Borders(xlEdgeBottom).LineStyle = 1 Then ’下边框,取得了一个人的数据
If iTop <> 0 Then
Str = Str + Stemp
bFound = True
End If
Else ‘一个人中间的数据进行连接
Str = Str + Stemp
End If
If bFound Then
wb.Sheets(1).Cells(iTop, sCol) = Str
Str = ""
bFound = False
Else
wb.Sheets(1).Cells(i, sCol) = ""
End If
'If .Borders(xlEdgeLeft).LineStyle = 1 Then MsgBox "Left"
'If .Borders(xlEdgeBottom).LineStyle = 1 Then MsgBox "Bottom"
'If .Borders(xlEdgeRight).LineStyle = 1 Then MsgBox "Right"
End With
Next i
wb.Close savechanges:=True
End Sub
发现的问题如下,本来不想用boolean的了,但是在.range下,好像还有.cell即使我限制了wb.Sheets(1).Cells(i, sCol)也同样取得的不是我想要的数据,cells(isCol).clear好像连格式也清空了,在这里就是我的单元格边框。
最后还想请问经过我这样赋值,字体改变了,怎样可以不改变字体。要是在with .range后面写.cells的话应怎样写 |
|