Jump to content

Excel - show which columns are used on any chosen worksheet


Go to solution Solved by water,

Recommended Posts

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!

Link to post
Share on other sites

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

My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2022-02-19 - Version 1.6.1.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts
OutlookEX (2021-11-16 - Version 1.7.0.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX_GUI (2021-04-13 - Version 1.4.0.0) - Download
Outlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - Wiki
PowerPoint (2021-08-31 - Version 1.5.0.0) - Download - General Help & Support - Example Scripts - Wiki
Task Scheduler (NEW 2022-07-28 - Version 1.6.0.1) - Download - General Help & Support - Wiki

Standard UDFs:
Excel - Example Scripts - Wiki
Word - Wiki

Tutorials:
ADO - Wiki
WebDriver - Wiki

 

Link to post
Share on other sites
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.

Link to post
Share on other sites
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" ...?

My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2022-02-19 - Version 1.6.1.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts
OutlookEX (2021-11-16 - Version 1.7.0.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX_GUI (2021-04-13 - Version 1.4.0.0) - Download
Outlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - Wiki
PowerPoint (2021-08-31 - Version 1.5.0.0) - Download - General Help & Support - Example Scripts - Wiki
Task Scheduler (NEW 2022-07-28 - Version 1.6.0.1) - Download - General Help & Support - Wiki

Standard UDFs:
Excel - Example Scripts - Wiki
Word - Wiki

Tutorials:
ADO - Wiki
WebDriver - Wiki

 

Link to post
Share on other sites

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.

Link to post
Share on other sites
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.

My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2022-02-19 - Version 1.6.1.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts
OutlookEX (2021-11-16 - Version 1.7.0.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX_GUI (2021-04-13 - Version 1.4.0.0) - Download
Outlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - Wiki
PowerPoint (2021-08-31 - Version 1.5.0.0) - Download - General Help & Support - Example Scripts - Wiki
Task Scheduler (NEW 2022-07-28 - Version 1.6.0.1) - Download - General Help & Support - Wiki

Standard UDFs:
Excel - Example Scripts - Wiki
Word - Wiki

Tutorials:
ADO - Wiki
WebDriver - Wiki

 

Link to post
Share on other sites
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???

 

Link to post
Share on other sites
$oWorksheet = $oWorkbook.Sheets(2).Activate

should be

$oWorksheet = $oWorkbook.Sheets(2)

 

My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2022-02-19 - Version 1.6.1.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts
OutlookEX (2021-11-16 - Version 1.7.0.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX_GUI (2021-04-13 - Version 1.4.0.0) - Download
Outlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - Wiki
PowerPoint (2021-08-31 - Version 1.5.0.0) - Download - General Help & Support - Example Scripts - Wiki
Task Scheduler (NEW 2022-07-28 - Version 1.6.0.1) - Download - General Help & Support - Wiki

Standard UDFs:
Excel - Example Scripts - Wiki
Word - Wiki

Tutorials:
ADO - Wiki
WebDriver - Wiki

 

Link to post
Share on other sites

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 :)

Link to post
Share on other sites

How many cells does the largest sheet hold?

My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2022-02-19 - Version 1.6.1.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts
OutlookEX (2021-11-16 - Version 1.7.0.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX_GUI (2021-04-13 - Version 1.4.0.0) - Download
Outlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - Wiki
PowerPoint (2021-08-31 - Version 1.5.0.0) - Download - General Help & Support - Example Scripts - Wiki
Task Scheduler (NEW 2022-07-28 - Version 1.6.0.1) - Download - General Help & Support - Wiki

Standard UDFs:
Excel - Example Scripts - Wiki
Word - Wiki

Tutorials:
ADO - Wiki
WebDriver - Wiki

 

Link to post
Share on other sites
  • Solution

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 by water

My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2022-02-19 - Version 1.6.1.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts
OutlookEX (2021-11-16 - Version 1.7.0.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX_GUI (2021-04-13 - Version 1.4.0.0) - Download
Outlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - Wiki
PowerPoint (2021-08-31 - Version 1.5.0.0) - Download - General Help & Support - Example Scripts - Wiki
Task Scheduler (NEW 2022-07-28 - Version 1.6.0.1) - Download - General Help & Support - Wiki

Standard UDFs:
Excel - Example Scripts - Wiki
Word - Wiki

Tutorials:
ADO - Wiki
WebDriver - Wiki

 

Link to post
Share on other sites

:)

My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2022-02-19 - Version 1.6.1.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts
OutlookEX (2021-11-16 - Version 1.7.0.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX_GUI (2021-04-13 - Version 1.4.0.0) - Download
Outlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - Wiki
PowerPoint (2021-08-31 - Version 1.5.0.0) - Download - General Help & Support - Example Scripts - Wiki
Task Scheduler (NEW 2022-07-28 - Version 1.6.0.1) - Download - General Help & Support - Wiki

Standard UDFs:
Excel - Example Scripts - Wiki
Word - Wiki

Tutorials:
ADO - Wiki
WebDriver - Wiki

 

Link to post
Share on other sites

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!

Link to post
Share on other sites

Could you please add

_ArrayDisplay($aUsedRange)

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

My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2022-02-19 - Version 1.6.1.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts
OutlookEX (2021-11-16 - Version 1.7.0.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX_GUI (2021-04-13 - Version 1.4.0.0) - Download
Outlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - Wiki
PowerPoint (2021-08-31 - Version 1.5.0.0) - Download - General Help & Support - Example Scripts - Wiki
Task Scheduler (NEW 2022-07-28 - Version 1.6.0.1) - Download - General Help & Support - Wiki

Standard UDFs:
Excel - Example Scripts - Wiki
Word - Wiki

Tutorials:
ADO - Wiki
WebDriver - Wiki

 

Link to post
Share on other sites

Could you please post a screenshot of the arraydisplay?

Edited by water

My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2022-02-19 - Version 1.6.1.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts
OutlookEX (2021-11-16 - Version 1.7.0.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX_GUI (2021-04-13 - Version 1.4.0.0) - Download
Outlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - Wiki
PowerPoint (2021-08-31 - Version 1.5.0.0) - Download - General Help & Support - Example Scripts - Wiki
Task Scheduler (NEW 2022-07-28 - Version 1.6.0.1) - Download - General Help & Support - Wiki

Standard UDFs:
Excel - Example Scripts - Wiki
Word - Wiki

Tutorials:
ADO - Wiki
WebDriver - Wiki

 

Link to post
Share on other sites

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.

My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2022-02-19 - Version 1.6.1.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts
OutlookEX (2021-11-16 - Version 1.7.0.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX_GUI (2021-04-13 - Version 1.4.0.0) - Download
Outlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - Wiki
PowerPoint (2021-08-31 - Version 1.5.0.0) - Download - General Help & Support - Example Scripts - Wiki
Task Scheduler (NEW 2022-07-28 - Version 1.6.0.1) - Download - General Help & Support - Wiki

Standard UDFs:
Excel - Example Scripts - Wiki
Word - Wiki

Tutorials:
ADO - Wiki
WebDriver - Wiki

 

Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
  • Recently Browsing   0 members

    No registered users viewing this page.

×
×
  • Create New...