Jump to content
gcue

excel selecting a sheet

Recommended Posts

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

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 (2018-12-03 - Version 1.4.11.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2019-03-02 - Version 1.3.5.0) - Download - General Help & Support - Example Scripts - Wiki
Outlook Tools (2019-01-22 - Version 0.1.0.0) - Download - General Help & Support
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

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

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 (2018-12-03 - Version 1.4.11.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2019-03-02 - Version 1.3.5.0) - Download - General Help & Support - Example Scripts - Wiki
Outlook Tools (2019-01-22 - Version 0.1.0.0) - Download - General Help & Support
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

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 (2018-12-03 - Version 1.4.11.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2019-03-02 - Version 1.3.5.0) - Download - General Help & Support - Example Scripts - Wiki
Outlook Tools (2019-01-22 - Version 0.1.0.0) - Download - General Help & Support
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

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

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

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 (2018-12-03 - Version 1.4.11.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2019-03-02 - Version 1.3.5.0) - Download - General Help & Support - Example Scripts - Wiki
Outlook Tools (2019-01-22 - Version 0.1.0.0) - Download - General Help & Support
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

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

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

Share this post


Link to post
Share on other sites

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 (2018-12-03 - Version 1.4.11.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2019-03-02 - Version 1.3.5.0) - Download - General Help & Support - Example Scripts - Wiki
Outlook Tools (2019-01-22 - Version 0.1.0.0) - Download - General Help & Support
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
_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 (2018-12-03 - Version 1.4.11.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2019-03-02 - Version 1.3.5.0) - Download - General Help & Support - Example Scripts - Wiki
Outlook Tools (2019-01-22 - Version 0.1.0.0) - Download - General Help & Support
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

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

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 (2018-12-03 - Version 1.4.11.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2019-03-02 - Version 1.3.5.0) - Download - General Help & Support - Example Scripts - Wiki
Outlook Tools (2019-01-22 - Version 0.1.0.0) - Download - General Help & Support
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

Insert

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

before

$oExcel.Visible = True

 

Edited by water

My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (2018-12-03 - Version 1.4.11.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2019-03-02 - Version 1.3.5.0) - Download - General Help & Support - Example Scripts - Wiki
Outlook Tools (2019-01-22 - Version 0.1.0.0) - Download - General Help & Support
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

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

×
×
  • Create New...