excel selecting a sheet

[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

expandcollapse popup

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

 

[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.

[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!  

expandcollapse popup

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

 

[water]

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

$oExcel.ActiveWindow.FreezePanes = True

 

[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

[gcue]

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

$oExcel.ActiveWindow.FreezePanes = True

 

​makes sense - thank you!!!!

[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.

[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.
 

[gcue]

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

[gcue]

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

[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.

[gcue]

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

[Jewtus]

I think you can use 

$oApp2 = _Excel_Open(False)

[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.

[gcue]

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

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

 

[gcue]

im doing this... also using win 7 excel 2010

 

$oExcel = _Excel_Open(False, False, False, False, True)
    $oWorkbook = _Excel_BookNew($oExcel, 2)

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

 

Edited June 8, 2015 by gcue

[water]

Insert

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

before

$oExcel.Visible = True

 

Edited June 8, 2015 by water

[gcue]

that seems to work much better

thank you thank you!!!!

[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