vba 如何使用access vba从列名中获取excel列号
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/23435607/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
how to get excel column number from column name using access vba
提问by vuyy1182
I'm trying to get the excel column number (index) from the column name.
我正在尝试从列名中获取 excel 列号（索引）。
Dim ExcelApp As New Excel.Application
Dim ExcelBook As New Excel.Workbook
Dim ColName As string
ColName = "sample name"
Debug.Print ExcelBook.Worksheets("Sheet1").Range(ColName & 1).Column
But this code is not printing column number. I instead get runtime error 1004: Application-defined or object-defined error. How do i get return the excel column number from the column name?
但此代码不打印列号。我反而得到运行时错误 1004: Application-defined or object-defined error。如何从列名返回excel列号？
回答by RubberDuck
Please see the msdn documentation for the Range objectand the Column property.
有关 Range 对象和Column 属性，请参阅msdn 文档。
I believe you are confusing the "Header" name in your worksheet with the column's address.
我相信您将工作表中的“标题”名称与列的地址混淆了。
The code below will return the column index.
下面的代码将返回列索引。
Debug.Print ThisWorkbook.Worksheets("Sheet1").Range("A1").Column 'returns 1
Debug.Print ThisWorkbook.Worksheets("Sheet1").Range("B:B").Column 'returns 2
' to do this by row & col index, use the cells object
Debug.Print Thisworkbook.worksheets(1).Cells(1,5).Column 'returns 5
回答by ljman711
Not sure f the full code, but it appears as though you are telling it the name and column #.
不确定完整的代码，但看起来好像你在告诉它名称和列#。
Try: Debug.Print ExcelBook.Worksheets("Sheet1").Range(ColName).Column
尝试： Debug.Print ExcelBook.Worksheets("Sheet1").Range(ColName).Column
Also, if you are in the active workbook and worksheet, try stepping through and do a msgbox Range(ColName).Column
此外，如果您在活动工作簿和工作表中，请尝试逐步执行并执行 msgbox Range(ColName).Column
