Foreword
我通过google找到了这个,我发现它缺乏。因此,我将填写更多信息,解释发生了什么,并对代码进行一些优化。
解释
应该带给您的明显答案是:
是的,这是可以做到的。事实上,它比你想象的要简单。
我注意到你做了这个
newFileColIndex = filesToImport.ListColumns("New File Name").Index
这为您提供了标题“新文件名”的索引。
然后,当您决定检查列时,您忘记了索引实际上也是相对列位置。
因此,您应该执行与之前相同的操作,而不是列号
InStr(fName.Range(1, filesToImport.ListColumns("Column Name")), "DATE")
Let's dig a little deeper, and explain with not only words, but with pictures
In the picture above, the first row shows the absolute column index,
where A1 has a column index of 1, B1 has a column index of 2 and so on.
The ListObject
的标头有自己的相对索引,
其中,在此示例中,Column1 将具有列索引 1,Column2 将具有列索引 2,依此类推。这使我们能够利用ListRow.Range
使用数字或名称引用列时的属性。
为了更好地演示,这里有一个打印相对值的代码and上一个图像中“Column1”的绝对列索引。
Public Sub Example()
Dim wsCurrent As Worksheet, _
loTable1 As ListObject, _
lcColumns As ListColumns
Set wsCurrent = ActiveSheet
Set loTable1 = wsCurrent.ListObjects("Table1")
Set lcColumns = loTable1.ListColumns
Debug.Print lcColumns("Column1").Index 'Relative. Prints 1
Debug.Print lcColumns("Column1").Range.Column 'Absolute. Prints 3
End Sub
自从ListRow.Range
指的是范围,它变成了相对性问题,因为该范围在ListObject
.
So, for example, to reference Column2 in each iteration of ListRow
you could do like this
Public Sub Example()
Dim wsCurrent As Worksheet, _
loTable1 As ListObject, _
lcColumns As ListColumns, _
lrCurrent As ListRow
Set wsCurrent = ActiveSheet
Set loTable1 = wsCurrent.ListObjects("Table1")
Set lcColumns = loTable1.ListColumns
For i = 1 To loTable1.ListRows.Count
Set lrCurrent = loTable1.ListRows(i)
'Using position: Range(1, 2)
Debug.Print lrCurrent.Range(1, 2)
'Using header name: Range(1, 2)
Debug.Print lrCurrent.Range(1, lcColumns("Column2").Index)
'Using global range column values: Range(1, (4-2))
Debug.Print lrCurrent.Range(1, (lcColumns("Column2").Range.Column - loTable1.Range.Column))
'Using pure global range values: Range(5,4)
Debug.Print wsCurrent.Cells(lrCurrent.Range.Row, lcColumns("Column2").Range.Column)
Next i
End If
优化代码
正如所承诺的,这是优化后的代码。
Public Sub Code()
Dim wsCurrentSheet As Worksheet, _
loSourceFiles As ListObject, _
lcColumns As ListColumns, _
lrCurrent As ListRow, _
strFileNameDate As String
Set wsCurrentSheet = Worksheets("Lists")
Set loSourceFiles = wsCurrentSheet.ListObjects("tblSourceFiles")
Set lcColumns = loSourceFiles.ListColumns
For i = 1 To loSourceFiles.ListRows.Count
Set lrCurrent = loSourceFiles.ListRows(i)
If InStr(lrCurrent.Range(1, lcColumns("Column Name").Index), "DATE") <> 0 Then
strSrc = lrCurrent.Range(1, lcColumns("New File Name").Index).value
strReplace = Format(ThisWorkbook.Names("ValDate").RefersToRange, "yyyymmdd")
strFileNameDate = Replace(strSrc, "DATE", strReplace)
wbName = OpenCSVFile("Path" & strFileNameDate)
CopyData sourceFile:=CStr(strFileNameDate), _
destFile:="file", _
destSheet:="temp"
End If
Next i
End Sub
参考
个人经验。
MSDN
- 列表对象 http://msdn.microsoft.com/en-us/library/aa174247%28v=office.11%29.aspx
- 列表列 http://msdn.microsoft.com/en-us/library/aa174243%28v=office.11%29.aspx
- ListRows http://msdn.microsoft.com/en-us/library/aa174248%28v=office.11%29.aspx