VBScript: Find last column used LETTER value (ex. "F"), not numerical value

[GJDouglas]

I am relatively new to Scripting in general, and need help with determining the LETTER of the last column used in a VBScript. The CVS file I am reading in the VBScript has data in columns A thru F. Therefore, want to figure out how to get the value of "F" returned (to be used later in the script). Most google searches return how to do it in VBA, but not VBScript. I have tried several of the googled "suggestions", but nothing has worked.

Here's my script's set up:

Set fso = CreateObject("Scripting.FileSystemObject")
file = PathAndFileName.csv
With CreateObject("Excel.Application")
Set WB = .Workbooks.Open(file)

Suggested, but failed, attempts to get the letter value of the last column used:
1. LastColumn = WB.ActiveSheet.Cells(1, Columns.Count).End(xlToLeft).Column
2. LastColumn = WB.ActiveSheet.UsedRange.Column + WB.ActiveSheet.UsedRange.Columns.Count - 1
3. LastColumn = WB.ActiveSheet.Columns.Cells(1, Columns.Count).End(xlToLeft).Column
4. LastColumn = WB.ActiveSheet.Cells.SpecialCells(xlCellTypeLastCell).Column
5. LastColumn = Split(WB.ActiveSheet.Cells.Find(What:="*", After:=[A1], SearchOrder:=xlByColumns, SearchDirection:=xlPrevious).Cells.Address(1, 0), "$")(0)
6. LastColumn = WB.ActiveSheet.UsedRange.Column

Could someone please enlighten me on VBScript code that would actually work to return the LETTER value of the last column used?
Thanks....

 

[DanteAmor]

Hi and welcome to MrExcel

Try:

dim LastColumn as string
LastColumn = Split(wb.ActiveSheet.Cells(1, Columns.Count).End(xlToLeft).Address(1, 0), "$")(0)

 

[GJDouglas]

Thank you very much! I'll give it a try when I get into the office tomorrow!

 

[Rick Rothstein]

What exactly do you mean by "the last column used"? I am asking because you could have formulas displaying the empty text string "" located after the last visible value in a row... do you want the column letter of the last visible value or the column letter of the last cell with anything it even if that is a formula displaying "" (the empty text string)?

 

[GJDouglas]

Hi Rick,

I'm looking for the right-most column out of the whole table that has visible data in it. I would not want the column selected if it doesn't have any visible data in the entire column (i.e. a formula). Hope that makes sense. Thanks!

 

[GJDouglas]

Hi and welcome to MrExcel

Try:

dim LastColumn as string
LastColumn = Split(wb.ActiveSheet.Cells(1, Columns.Count).End(xlToLeft).Address(1, 0), "$")(0)

I'm sorry Dante.... I spoke to soon (I had quickly looked at your response from my phone, and had missed your comment that the code was for VBA). I am not looking for the code in VBA -- I'm need the code for finding the letter value of the last column that has data in VBSCRIPT. I do appreciate you taking the time to respond.

 

[DanteAmor]

I'm not sure, but try the following.
Replace my test file "c:\trabajo\testfile.csv" with your file.

Sub LastCol()
  Set fso = CreateObject("Scripting.FileSystemObject")
  Set objexcel = CreateObject("Excel.Application")
  objexcel.Visible = True
  Set objWB = objexcel.Workbooks.Open("c:\trabajo\testfile.csv")
  Set objSH = objWB.Worksheets(1)
  cell = objSH.Cells(1, objSH.Columns.Count).End("-4159").Address
  a = Split(cell, "$")
  MsgBox a(1)
  objexcel.Quit
End Sub