Jump to content
gcue

excel selecting a sheet

Recommended Posts

gcue

hello world!

I am having trouble selecting a sheet to format it.  I have tried several ways but have had no luck.  I tried referencing the select.items($index) among other ways.  can anyone shed any light on the proper syntax i need to use?

thanks in advance

#include <Excel.au3>

$msg_normal = 0

$results_xls = @ScriptDir & "\Results.xlsx"

If FileExists($results_xls) Then
    FileDelete($results_xls)
EndIf

ProcessClose("excel.exe")

$oAppl = _Excel_Open(False)

$oWorkbook = _Excel_BookNew($oAppl, 2)

_Excel_SheetAdd($oWorkbook, -1, False, 2, "Sorted|UnSorted")
_Excel_SheetDelete($oWorkbook, "Sheet1")
_Excel_SheetDelete($oWorkbook, "Sheet2")

_Excel_RangeWrite($oWorkbook, "UnSorted", "SOURCE", "A1")
_Excel_RangeWrite($oWorkbook, "UnSorted", "FILE NAME", "B1")
_Excel_RangeWrite($oWorkbook, "UnSorted", "FILE EXTENSION", "C1")

_Excel_RangeWrite($oWorkbook, "Sorted", "SOURCE", "A1")
_Excel_RangeWrite($oWorkbook, "Sorted", "FILE NAME", "B1")
_Excel_RangeWrite($oWorkbook, "Sorted", "FILE EXTENSION", "C1")
_Excel_RangeWrite($oWorkbook, "Sorted", "FILE TYPE", "D1")
_Excel_RangeWrite($oWorkbook, "Sorted", "MODIFIED DATE", "E1")
_Excel_RangeWrite($oWorkbook, "Sorted", "TARGET", "F1")

;~ _Excel_BookClose($oWorkbook, True)
;~ $oWorkbook.ActiveSheet.Range("A1").SpecialCells($xlCellTypeLastCell).Row

;~  $oWorkbook.Sheets.Item("UnSorted").Activate

;~ $oAppl.ActiveWorkbook.Sheets(1).Select()

;~  $oAppl.Application.ScreenUpdating = False

$oWorkbook.Sheets("UnSorted").Rows("1:1").Select
$oWorkbook.Selection.Font.Bold = True
$oWorkbook.ActiveSheet.Selection.AutoFilter

$oWorkbook.ActiveSheet.Columns("A:C").AutoFit

$oWorkbook.ActiveSheet.Range("A2").Select
$oWorkbook.ActiveWindow.FreezePanes = True


;~  $oWorkbook.Sheets.Item("Sorted").Activate
$oWorkbook.Sheets.Item("Sorted").Rows("1:1").Select
$oWorkbook.Sheets.Item("Sorted").Selection.Font.Bold = True
$oWorkbook.Sheets.Item("Sorted").Selection.AutoFilter

$oWorkbook.Sheets.Item("Sorted").Columns("E:E").Select
$oWorkbook.Sheets.Item("Sorted").Selection.NumberFormat = "mm/dd/yyyy hh:mm AM/PM"

$oWorkbook.Sheets.Item("Sorted").Columns("A:F").AutoFit

$oWorkbook.Sheets.Item("Sorted").Range("A2").Select
$oWorkbook.Sheets.Item("Sorted").ActiveWindow.FreezePanes = True


;~ $oAppl.Application.ScreenUpdating = True

_Excel_BookSaveAs($oWorkbook, $results_xls)
_Excel_BookClose($oAppl)

ProcessClose("excel.exe")

ShellExecute($results_xls)

 

Share this post


Link to post
Share on other sites
water

I wouldn't work with Selections (that's the visible part as if the user had selected a few cells) but with Ranges. That's faster and easier.

You could have a look in the wiki to see how ranges can get formatted.

BTW: Why do you run ProcessClose? That's a pretty brute method of closing Excel. Simply use _Excel_Open/_Excel_Close and let the UDF do all the work.


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2018-10-19 - Version 1.4.10.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (2018-09-01 - Version 1.3.4.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts
PowerPoint (2017-06-06 - Version 0.0.5.0) - Download - General Help & Support
Excel - Example Scripts - Wiki
Word - Wiki
 
Tutorials:

ADO - Wiki

 

Share this post


Link to post
Share on other sites
gcue

the need for processclose used to exist from an older version of the udf - the process wouldnt close out clean most of the time.  i just have been carrying it forward to be safe.  ill test without it.

got most of it working except the freezepanes tried a few things but cant seem to get it working..

thanks for your help!  

#include <Excel.au3>

$msg_normal = 0

$results_xls = @ScriptDir & "\Results.xlsx"

If FileExists($results_xls) Then
    FileDelete($results_xls)
EndIf

ProcessClose("excel.exe")

$oAppl = _Excel_Open(False)

$oWorkbook = _Excel_BookNew($oAppl, 2)

_Excel_SheetAdd($oWorkbook, -1, False, 2, "Sorted|UnSorted")
_Excel_SheetDelete($oWorkbook, "Sheet1")
_Excel_SheetDelete($oWorkbook, "Sheet2")

_Excel_RangeWrite($oWorkbook, "UnSorted", "SOURCE", "A1")
_Excel_RangeWrite($oWorkbook, "UnSorted", "FILE NAME", "B1")
_Excel_RangeWrite($oWorkbook, "UnSorted", "FILE EXTENSION", "C1")

_Excel_RangeWrite($oWorkbook, "Sorted", "SOURCE", "A1")
_Excel_RangeWrite($oWorkbook, "Sorted", "FILE NAME", "B1")
_Excel_RangeWrite($oWorkbook, "Sorted", "FILE EXTENSION", "C1")
_Excel_RangeWrite($oWorkbook, "Sorted", "FILE TYPE", "D1")
_Excel_RangeWrite($oWorkbook, "Sorted", "MODIFIED DATE", "E1")
_Excel_RangeWrite($oWorkbook, "Sorted", "TARGET", "F1")


$oWorkbook.Sheets("UnSorted").Activate

$oRange = $oWorkBook.ActiveSheet.Range("A1").EntireRow
$oRange.Font.Bold = True
$oRange.AutoFilter

$oRange.Columns("A:C").AutoFit

$oWorkBook.ActiveSheet.Range("A2").Select
$oWorkbook.ActiveWindow.FreezePanes = True



$oWorkbook.Sheets("Sorted").Activate

$oRange = $oWorkBook.Activesheet.Range("E1").EntireColumn
$oRange.NumberFormat = "mm/dd/yyyy hh:mm AM/PM"

$oRange = $oWorkBook.ActiveSheet.Range("A1").EntireRow
$oRange.Font.Bold = True
$oRange.AutoFilter

$oRange.Columns("A:F").AutoFit

$oWorkBook.ActiveSheet.Range("A2").Select
$oWorkbook.ActiveWindow.FreezePanes = True

_Excel_BookSaveAs($oWorkbook, $results_xls)
_Excel_BookClose($oAppl)

ShellExecute($results_xls)

 

Share this post


Link to post
Share on other sites
water

ActiveWindow is a property of the application object not the workbook. Hence it should be:

$oExcel.ActiveWindow.FreezePanes = True

 


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2018-10-19 - Version 1.4.10.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (2018-09-01 - Version 1.3.4.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts
PowerPoint (2017-06-06 - Version 0.0.5.0) - Download - General Help & Support
Excel - Example Scripts - Wiki
Word - Wiki
 
Tutorials:

ADO - Wiki

 

Share this post


Link to post
Share on other sites
water

BTW:
What is ShellExecute for? It's just wasting processing time when you simply want to display the resulting workbook.
Try:

_Excel_BookSaveAs($oWorkbook, $results_xls)
$oExcel.Visible = True

My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2018-10-19 - Version 1.4.10.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (2018-09-01 - Version 1.3.4.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts
PowerPoint (2017-06-06 - Version 0.0.5.0) - Download - General Help & Support
Excel - Example Scripts - Wiki
Word - Wiki
 
Tutorials:

ADO - Wiki

 

Share this post


Link to post
Share on other sites
gcue

ActiveWindow is a property of the application object not the workbook. Hence it should be:

$oExcel.ActiveWindow.FreezePanes = True

 

​makes sense - thank you!!!!

Share this post


Link to post
Share on other sites
gcue

BTW:
What is ShellExecute for? It's just wasting processing time when you simply want to display the resulting workbook.
Try:

_Excel_BookSaveAs($oWorkbook, $results_xls)
$oExcel.Visible = True

thanks! - ​that definitely seems to work better.  i have a question, do i need to have the user exit excel before i start creating/writing to a file through the UDF?  the old UDF had issues with that - excel writes would appear even though id specify invisible and would interrupt the user's use of excel.

Share this post


Link to post
Share on other sites
water

It's causing problems when the user edits a cell and you want to write to the Workbook at the same time.

You could call _Excel_Open with parameter $bInteractive set to False. So mouse/keyboard are blocked for the user unstil released by you.
 


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2018-10-19 - Version 1.4.10.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (2018-09-01 - Version 1.3.4.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts
PowerPoint (2017-06-06 - Version 0.0.5.0) - Download - General Help & Support
Excel - Example Scripts - Wiki
Word - Wiki
 
Tutorials:

ADO - Wiki

 

Share this post


Link to post
Share on other sites
gcue

i think id rather just tell the user to exit excel before hand.  blocking mouse/kb is even more intrusive i think.

Share this post


Link to post
Share on other sites
gcue

wait - do you mean editing the cell of the same workbook?  or any workbook?

Share this post


Link to post
Share on other sites
water

I think "same Workbook".
If you like you can tell Excel to open a new instance, run it inivisible and under your full control without any possible way for the user to interfere.


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2018-10-19 - Version 1.4.10.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (2018-09-01 - Version 1.3.4.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts
PowerPoint (2017-06-06 - Version 0.0.5.0) - Download - General Help & Support
Excel - Example Scripts - Wiki
Word - Wiki
 
Tutorials:

ADO - Wiki

 

Share this post


Link to post
Share on other sites
gcue

how do you open a new instance seperate from what the user is using?

Share this post


Link to post
Share on other sites
Jewtus

I think you can use 

$oApp2 = _Excel_Open(False)

Share this post


Link to post
Share on other sites
water
_Excel_Open(False, False, False, False, True)

Forces the UDF to open a new instance. This instance is invisible, does no screen updating, blocks all user input.


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2018-10-19 - Version 1.4.10.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (2018-09-01 - Version 1.3.4.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts
PowerPoint (2017-06-06 - Version 0.0.5.0) - Download - General Help & Support
Excel - Example Scripts - Wiki
Word - Wiki
 
Tutorials:

ADO - Wiki

 

Share this post


Link to post
Share on other sites
gcue

weird.. that didnt open the excel file for me.  instead i got 2 grey screen instances of excel.

Share this post


Link to post
Share on other sites
water

This works fine here (Excel 2010 on Windows 7):

#include <Excel.au3>
$oExcel = _Excel_Open(False, False, False, False, True)
ConsoleWrite(@error & @CRLF)
$oWorkbook = _Excel_BookOpen($oExcel, "C:\temp\test.xlsx")
ConsoleWrite(@error & @CRLF)
_Excel_Close($oExcel)
ConsoleWrite(@error & @CRLF)

 


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2018-10-19 - Version 1.4.10.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (2018-09-01 - Version 1.3.4.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts
PowerPoint (2017-06-06 - Version 0.0.5.0) - Download - General Help & Support
Excel - Example Scripts - Wiki
Word - Wiki
 
Tutorials:

ADO - Wiki

 

Share this post


Link to post
Share on other sites
water

Insert

$oExcel.ScreenUpdating = True
$oExcel.Interactive = True

before

$oExcel.Visible = True

 

Edited by water

My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2018-10-19 - Version 1.4.10.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (2018-09-01 - Version 1.3.4.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts
PowerPoint (2017-06-06 - Version 0.0.5.0) - Download - General Help & Support
Excel - Example Scripts - Wiki
Word - Wiki
 
Tutorials:

ADO - Wiki

 

Share this post


Link to post
Share on other sites
gcue

that seems to work much better

thank you thank you!!!!

Share this post


Link to post
Share on other sites
gcue

i think something else to keep in mind along the lines of having excel open...  is to check to see if the file is already open

running this first.

$results_xls = @ScriptDir & "\Results.xlsx"
        
    $file_usage = FileOpen($results_xls, 1)

    If $file_usage = -1 Then
        MsgBox($msg_error, $script_name, $results_xls & " is in use." & @CRLF & _
                @CRLF & _
                'Please close it before continuing.')       
        Return
    EndIf

    FileClose($file_usage)

    If FileExists($results_xls) Then
        FileDelete($results_xls)
    EndIf

 

Share this post


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

×