Excel - show which columns are used on any chosen worksheet

[graybags]

Hi,

This has really stumped me!

My script so far allows a user to open a workbook and then reads in the names of the worksheets into an array.

But now I need to get the user to choose which column in their chosen worksheet has the data they are interested in, by bringing up a list of the used columns on the worksheet.

So they browse to a workbook, then they select the worksheet from a list, and then I'd like another list to appear showing "A B C D..." on so on, for however many columns that worksheet uses.

Is that possible?

Thanks!

[water]

I would display a MsgBox telling the user what to do (select a column then click the OK button).

[graybags]

2 minutes ago, water said:

I would display a MsgBox telling the user what to do (select a column then click the OK button).

That's what I've done in the past with a previous script, I was just hoping there was a way they could actually see their options and just tick one.

[water]

59 minutes ago, graybags said:

choose which column in their chosen worksheet has the data they are interested in

How could you determine which column the user needs to process (and hence create a list to select from)?
Excel provides the UsedRange object. This Range holds all cells which have ever been "touched". This means that if a cell contained data that was then deleted, it will be listed as "used".
Another idea: Could you select the columns by header cells like "PartNumber", "Price" ...?

[graybags]

Yeah, i figured Excel would remember a cell that had been touched and deleted and label that as used.  Damn.  I was hoping there was a way to see if the column something in, kind of a CurrentlyUsedRange object

The headers on each worksheet are different, so I can't do that.  Oh well, thanks for reading, I'll guess I have to just get the user to manually imput their column like I am.

[water]

For $oColumn In $oWorkSheet.UsedRange.Columns
    ConsoleWrite($oColumn.Address(False, True) & @CRLF)
Next

Should list all column addresses of the used Range.
$oWorkSheet is the object of the selected WorkSheet.

I couldn't test as I do not have a Windows machine available at the moment.

[graybags]

17 minutes ago, water said:

For $oColumn In $oWorkSheet.UsedRange.Columns
    ConsoleWrite($oColumn.Address(False, True) & @CRLF)
Next

Should list all column addresses of the used Range.
$oWorkSheet is the object of the selected WorkSheet.

I couldn't test as I do not have a Windows machine available at the moment.

#include <Excel.au3>
#include <MsgBoxConstants.au3>

; Create application object
Local $oExcel = _Excel_Open()
If @error Then Exit MsgBox($MB_SYSTEMMODAL, "Excel UDF: _Excel_BookOpen Example", "Error creating the Excel application object." & @CRLF & "@error = " & @error & ", @extended = " & @extended)

; Open an existing workbook and return its object identifier.
Local $sWorkbook = @ScriptDir & "\test.xlsx"
Local $oWorkbook = _Excel_BookOpen($oExcel, $sWorkbook)
If @error Then Exit MsgBox($MB_SYSTEMMODAL, "Excel UDF: _Excel_BookOpen Example 1", "Error opening '" & $sWorkbook & "'." & @CRLF & "@error = " & @error & ", @extended = " & @extended)
;MsgBox($MB_SYSTEMMODAL, "Excel UDF: _Excel_BookOpen Example 1", "Workbook '" & $sWorkbook & "' has been opened successfully." & @CRLF & @CRLF & "Creation Date: " & $oWorkbook.BuiltinDocumentProperties("Creation Date").Value)

; Read data from a single cell on the active sheet of the specified workbook
Local $sResult = _Excel_RangeRead($oWorkbook, Default, "A1")
If @error Then Exit MsgBox($MB_SYSTEMMODAL, "Excel UDF: _Excel_RangeRead Example 1", "Error reading from workbook." & @CRLF & "@error = " & @error & ", @extended = " & @extended)
;MsgBox($MB_SYSTEMMODAL, "Excel UDF: _Excel_RangeRead Example 1", "Data successfully read." & @CRLF & "Value of cell A1: " & $sResult)

$oWorksheet = $oWorkbook.Sheets(2).Activate

For $oColumn In $oWorksheet.UsedRange.Columns
    ConsoleWrite($oColumn.Address(False, True) & @CRLF)
Next

ok, just using that as a really simple example to test things, i'm getting an error:

 Variable must be of type "Object".:
For $oColumn In $oWorksheet.UsedRange.Columns
For $oColumn In $oWorksheet^ ERROR

what have I done wrong???

 

[water]

$oWorksheet = $oWorkbook.Sheets(2).Activate

should be

$oWorksheet = $oWorkbook.Sheets(2)

 

[graybags]

Ah yes, that works thanks.  I've run it for a workbook I have and I'm selecting various sheets.

For one it's still listing a huge list of rows (1871) for a worksheet with only 38 used ones.  I've scrolled down to that row and indeed that row and beyond have cells with borders, so someone has obviously done something odd with spreadsheet in the past, and I don't have any control of that.

If I create a blank sheet and copy and paste the "real" data the console tells me tells me:

$A1:$A38
$B1:$B38
$C1:$C38
$D1:$D38
$E1:$E38
$F1:$F38
$G1:$G38
$H1:$H38
$I1:$I38
$J1:$J38

...which is correct.  But I can't correct every sheet like I did as the user chooses the sheets.  It looks like I might have to bite the bullet and just ask the user to input the tab. 

Thanks again for your help, I thought we were getting close.  Somebody needs to write a UsedRangeWithDataActuallyInACell function

[water]

How many cells does the largest sheet hold?

[graybags]

Nothing huge, but it's down to the user to choose the sheet, I'll have no control of that, but realistically no more than 200 rows and maybe columns A-M, so 13 columns.  So around 2,500 cells.

[water]

Shouldn't be a problem.
Quick and dirty example:

Global $iSheet = 2
; create a Range holding all used cells including unused rows at the top and unused columns on the left
; See: https://www.autoitscript.com/wiki/Excel_Range#Used_Range
With $oWorkbook.Sheets($iSheet)
    $oRangeLast = .UsedRange.SpecialCells($xlCellTypeLastCell)
    $oRange = .Range(.Cells(1, 1), .Cells($oRangeLast.Row, $oRangeLast.Column))
EndWith
; Process the Range
Global $aUsedRange = _Excel_RangeRead($oWorkbook, $iSheet, $oRange)
For $iCol = 0 To UBound($aUsedRange, 2) - 1 ; Process all columns
    For $iRow = 0 To UBound($aUsedRange, 1) - 1 ; Process all rows
        If $aUsedRange[$iRow][$iCol] <> "" Then ; add checks for valid columns here
            ConsoleWrite(_Excel_ColumnToLetter($iCol + 1) & @CRLF)
            ExitLoop ; Process next column
        Next
    Next
Next

 

Edited January 22, 2023 by water

[graybags]

That's it!  Thanks so much, I really appreciate the help.

[water]

[graybags]

Oooh, just a couple of more things, sorry.  I can't seem to get the script to work if the spreadsheet has just one column, it doesn't write "A" as I'd expect.

Also, how would I write the result to an array?

Thanks!

[water]

Could you please add

_ArrayDisplay($aUsedRange)

after the _Excel_RangeRead statement to make sure the correct data is being read?

[graybags]

Yes, the correct data is being read, even with a sheet with just one column.

[water]

Could you please post a screenshot of the arraydisplay?

Edited January 23, 2023 by water

[graybags]

[water]

Looks good so far.
A worksheet with > 1 columns should result in a 2D array.

Can you now please post the screenshot of a worksheet with a single column? I fear that _Excel_RangeRead returns a 1D array in this case.