VBS-获取excel最大列数

**

获取excel最大列数对应的列名

**
Dim columnMax,WriteColumnName

excelPath = WScript.Arguments(0)
'excelPath = “C:\Users***\Desktop\test.xlsx”
Set allExcel = CreateObject(“Excel.Application”)
allExcel.Visible = False
Set all = allExcel.Workbooks.Open(excelPath)
Set allSheet = allExcel.WorkSheets(1)

'获取列数以及对应的列名
Function GetColumnName(col)
   Dim num
   num = col-1
   If num<26 Then
       GetColumnName= Chr(Asc("A") + num)
   Else 
       GetColumnName= Chr(Asc("A")+(num\26)-1)&Chr(Asc("A")+(num Mod 26))
   End If
End Function

'获取最大列数
columnMax = allExcel.ActiveSheet.UsedRange.Columns.Count+1
'MsgBox columnMax
col=1
FindStatus=FALSE
Do Until col=columnMax
Data=allSheet.Cells(1,col)
'MsgBox Data
If StrComp(Data,“建卡状态”,1)=0 Then
StatusColumnName = GetColumnName(col)
'MsgBox “建卡状态列名：”&StatusColumnName
FindStatus=TRUE
End If
If StrComp(Data,“建卡编号”,1)=0 Then
BuildIdColumnName = GetColumnName(col)
'MsgBox “建卡编号列名：”&BuildIdColumnName
FindStatus=TRUE
End If
If StrComp(Data,“建卡资产编号”,1)=0 Then
AssetIdColumnName = GetColumnName(col)
'MsgBox “建卡资产编号列名：”&AssetIdColumnName
FindStatus=TRUE
End If
col=col+1
Loop

If StrComp(FindStatus,“TRUE”,1)=0 Then
'MsgBox “不添加建卡状态、建卡编号和建卡资产编号”&FindStatus
Else
'添加建卡状态
StatusColumnName = GetColumnName(columnMax)
allSheet.cells(1,StatusColumnName).value=“建卡状态”

'建卡的最新序号
columnMax = allExcel.ActiveSheet.UsedRange.Columns.Count+1
BuildIdColumnName = GetColumnName(columnMax)
allSheet.cells(1,BuildIdColumnName).value="建卡编号"

'建卡的资产编号
columnMax = allExcel.ActiveSheet.UsedRange.Columns.Count+1
AssetIdColumnName = GetColumnName(columnMax)
allSheet.cells(1,AssetIdColumnName).value="建卡资产编号"

End If
'MsgBox “状态列名：”&StatusColumnName&“编号列名：”&BuildIdColumnName&“资产编号：”&AssetIdColumnName
WriteColumnName=StatusColumnName&“+”&BuildIdColumnName&“+”&AssetIdColumnName
'MsgBox WriteColumnName

'关闭allExcel
all.Save
all.Close
allExcel.Quit
WScript.StdOut.WriteLine(WriteColumnName)