Jump to content

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)

 

Link to comment
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 (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 comment
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)

 

Link to comment
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 (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 comment
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 (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 comment
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.

Link to comment
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 (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 comment
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 (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 comment
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 (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 comment
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 (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 comment
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 (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 comment
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

 

Link to comment
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
 Share

  • Recently Browsing   0 members

    • No registered users viewing this page.
×
×
  • Create New...