PINTO1927

Excel write background

4 posts in this topic

Hello guys,

through this example that you find at the end I can not get to write a text in cell A1 example, without being open excel.

#include <Excel.au3>

$oExcel = _Excel_Open(False)
$sWorkbook = @DesktopDir & "\test\test.xlsx"
$oWorkbook = _Excel_BookOpen($oExcel, $sWorkbook)
_Excel_RangeWrite($oWorkbook, $oWorkbook.Activesheet, "Test" & @CRLF & "String")

 

while if I make "_Excel_Open(True)" visible, the text is written.

Share this post


Link to post
Share on other sites



What is the value of @error and @extended after _Excel_RangeWrite when running hidden?


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2017-04-18 - Version 1.4.8.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2017-02-27 - Version 1.3.1.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2015-04-01 - Version 0.4.0.0) - Download - General Help & Support - Example Scripts
Excel - Example Scripts - Wiki
Word - Wiki
Tutorials:
ADO - Wiki

 

Share this post


Link to post
Share on other sites

#3 ·  Posted (edited)

not returns no Error but adding _Excel_BookClose the edit is performed!

$oExcel = _Excel_Open(False)
$sWorkbook = @DesktopDir & "\test\test.xlsx"
$oWorkbook = _Excel_BookOpen($oExcel, $sWorkbook)
_Excel_RangeWrite($oWorkbook, $oWorkbook.Activesheet, "Test" & @CRLF & "String")
If @error Then
   MsgBox($MB_SYSTEMMODAL, "Excel UDF: _Excel_RangeWrite Example", "Error." & @CRLF & "@error = " & @error & ", @extended = " & @extended)
EndIf
_Excel_BookClose($oWorkbook, True)

I think I understand what was the problem ...

Edited by PINTO1927

Share this post


Link to post
Share on other sites

:)


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2017-04-18 - Version 1.4.8.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2017-02-27 - Version 1.3.1.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2015-04-01 - Version 0.4.0.0) - Download - General Help & Support - Example Scripts
Excel - Example Scripts - Wiki
Word - Wiki
Tutorials:
ADO - Wiki

 

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

  • Similar Content

    • singbass
      By singbass
      I have an issue with disk space on a server so I wrote a simple little script to check specific directories and save the sizes to an Excel spreadsheet.  For this script, I am still using version 3.3.8.1.  Everything works fine, I just have a question.
      #cs ---------------------------------------------------------------------------- AutoIt Version: 3.3.8.1 #ce ---------------------------------------------------------------------------- #include<date.au3> #include<excel.au3> #include<array.au3> $file = FileOpen(@ScriptDir & "\CMScriptDirList.txt", 0) If @error Then Exit ;--folder view still OK $oexcel = _ExcelBookOpen(@ScriptDir & "\CMScriptDirSizesCopy.xlsx") If @error Then Exit ;--folder view now parent folder $excelArray = _ExcelReadSheetToArray($oexcel) $lastrow = $excelArray[0][0] $lastcol = $excelArray[0][1] _ExcelWriteCell($oexcel, _NowCalc(), 1, $lastcol + 1) While 1 $line = FileReadLine($file) If @error Then ExitLoop $size = DirGetSize(StringStripWS($line, 3)) / 1024 / 1024 $iIndex = _ArraySearch($excelArray, $line, 0, 0, 0, 0, 1, 1) If @error Then ContinueLoop _ExcelWriteCell($oexcel, $size, $iIndex, $lastcol + 1) WEnd FileClose($file) _ExcelWriteFormula($oexcel, "=SUM(R2C" & $lastcol + 1 & ":R38C" & $lastcol + 1 & ")", 39, $lastcol + 1) _ExcelWriteFormula($oexcel, "=R39C" & $lastcol + 1 & "/1024", 40, $lastcol + 1) $oexcel.ActiveSheet.columns($lastcol).copy ;used to copy the format of the original last column of the spreadsheet $oexcel.ActiveSheet.columns($lastcol + 1).PasteSpecial(-4122, Default, Default, Default) ;this just pastes the format of the original last column to the new last column $oexcel.ActiveSheet.Range("A1").Select ;select cell A1 just to unselect the entire column from previous command $oexcel.columns.AutoFit ;auto sizes the column width _ExcelBookClose($oexcel, 1) ;save file when closing The script is compiled and sitting is a sub-directory on the server in question. The text file and the spreadsheet that are used are both in this same folder as well.  When I navigate to the folder and run the script by double-clicking on the executable, the process runs but the folder view where I ran the script will go back up one level so when the script completes, I am in the parent folder from where I started.  I have added message boxes throughout the script and have determined that the folder view goes back up one level at some point after the @error check for the file open and before the @error check for the ExcelBookOpen (where the comments are).
      I just wanted to know if someone can tell me why and if there is a way to prevent it. (Note: still using v3.3.8.1 on this machine but slowly converting scripts to v3.3.14.2).
       
       
    • KimberlyJillPereira
      By KimberlyJillPereira
      I am a newbie in AutoIt. May I know what is the code used to expand the width and height of excel cells because I want to insert pictures in the cell.I tried AutoFit but that doesnt work as I cant specify for the width and height. Only for column width I could expand by using .ColumnWidth but for the row I am not able to expand the row? How to do?? What is the code used ??Please help me and thank you.
    • KimberlyJillPereira
      By KimberlyJillPereira
      I am a newbie in AutoIt. May I know what is the code used to expand the width and height of excel cells because I want to insert pictures in the cell.I tried AutoFit but that doesnt work as I cant specify for the width and height. Only for column width I could expand by using .ColumnWidth but for the row I am not able to expand the row? How to do?? What is the code used ??Please help me and thank you.
    • FrancescoDiMuro
      By FrancescoDiMuro
      Good morning
      I would like to know if I can use the Excel UDF to manipulating a .csv file without having Office installed on the PC I'm going to work...
      I read somewhere that it could be done, but I'm here to ask and be sure of what I remember... 
      I'd like to post another question...
      How can I retrieve the handle of a windows from a PID of an .exe?
      I have my script that does a ShellExecute ( which returns the PID of the .exe ), and then, switching a parameter read from a .ini file, adapt the Window on the screen ( Maximize, Minimize, On Top )...
      I tried, but without having success with this:
       
      #Region ;**** Directives created by AutoIt3Wrapper_GUI **** #AutoIt3Wrapper_Outfile_x64=prova.exe #EndRegion ;**** Directives created by AutoIt3Wrapper_GUI **** #include <ButtonConstants.au3> #include <GUIConstantsEx.au3> #include <StaticConstants.au3> #include <WindowsConstants.au3> #include <MsgBoxConstants.au3> #include <AutoItConstants.au3> #include <WinAPIEx.au3> #include <Array.au3> Local $sFileConfigurazione = @ScriptDir & "\configurazione_exe.ini" If(FileExists($sFileConfigurazione)) Then Local $aSezioniIni = IniReadSection($sFileConfigurazione, "CONFIGURAZIONE_EXE") If @error Then MsgBox($MB_ICONERROR, "Errore!", "Errore durante l'apertura del file: " & @CRLF & $sFileConfigurazione & @CRLF & "Errore: " & @error) Else ; Lancio dell'applicativo indicato nel file di configurazione Local $iPID = ShellExecute($aSezioniIni[1][1]) Local $hWnd If($iPID <> 0) Then Local $aWinList = WinList() For $i = 1 To $aWinList[0][0] If(WinGetProcess($aWinList[$i][1] = $iPID)) Then $hWnd = $aWinList[$i][1] EndIf Next Switch($aSezioniIni[2][1]) Case $aSezioniIni[2][1] = "MIN" WinSetState($hWnd, "", @SW_MINIMIZE) Case $aSezioniIni[2][1] = "MAX" WinSetState($hWnd, "", @SW_MAXIMIZE) Case $aSezioniIni[2][1] = "TOP" WinSetOnTop($hWnd, "", $WINDOWS_ONTOP) EndSwitch EndIf EndIf EndIf It just set on top the .exe I'm launching...
      Thanks
    • FrancescoDiMuro
      By FrancescoDiMuro
      Good morning
      I was looking for a method with rename an Excel Sheet, but I didn't find a lot...
      So, I decided to make it in another way...
      Since I have to create a new Workbook, I thought that, creating a new Workbook, deleting the existing sheet, and adding a new one, would be almost the same...
      But I'm encountering a lot of issues, both when I delete the sheet and when I add the new sheet...
      This is what I do:
       
      ; Create the Excel Object... Local $oExcel_PRV_HW = _Excel_Open(False) If @error Then MsgBox($MB_ICONERROR, "Errore!", "Errore durante la creazione dell'oggetto Excel." & @CRLF & "Errore: " & @error & @CRLF & "Esteso: " & @extended) Else ; Create the Workbook with 1 worksheet... Local $oWorkbook_New = _Excel_BookNew($oExcel_PRV_HW, 1) ; Save the Workbook in order to open it and work with it... _Excel_BookSaveAs($oWorkbook_New, $sFilePreventivo, $xlOpenXMLWorkbook, True) If @error Then MsgBox($MB_ICONERROR, "Errore!", "Errore durante il salvataggio della cartella di lavoro." & @CRLF & "Errore: " & @error) EndIf ; Open the Workbook to work with... Local $oWorkbook_PRV_HW = _Excel_BookOpen($oExcel_PRV_HW, $sFilePreventivo) If @error Then MsgBox($MB_ICONERROR, "Errore!", "Errore durante l'apertura del file '" & $sFilePreventivo & "'." & @CRLF & "Errore: " & @error) Else ; Here I would add the _Excel_SheetDelete() and _Excel_SheetAdd() as I did, but they return errors... EndIf EndIf Can someone help me out, please? Thanks