detrauti

How to access a renamed excel workbook

9 posts in this topic

#1 ·  Posted

Hi Everyone,

I am a beginner and I am currently learning and practicing what Autoit can do, so kindly pardon if it sound's silly.

What I want to do:

1.creat an excel file

2.entered some information

3.safed it with a certain name,

4.closed the file and reopen it

5. enter information in the renamed file

---> what do I have to write for the parameter:

$oWorkbook to acces my workbook?

Thank you for helping me

Share this post


Link to post
Share on other sites



#2 ·  Posted

to get a better understanding see the screenshot:

AutoIT error.png

Share this post


Link to post
Share on other sites

#3 ·  Posted

Simple answer is: Do not close the workbook if you still want to work with it :)


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
PowerPoint (2015-06-06 - Version 0.0.5.0) - Download - General Help & Support

Tutorials:
ADO - Wiki

 

Share this post


Link to post
Share on other sites

#4 ·  Posted

:-P thank you for the quick answer and I agree but only as the description of my problem is not that good.

 

Imagine to open an existing workbook called hello world. How can I (example) write some information in a certain cell?

Autoscript error1.png

Share this post


Link to post
Share on other sites

#5 ·  Posted

Where do you set variable $workbook?
 


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
PowerPoint (2015-06-06 - Version 0.0.5.0) - Download - General Help & Support

Tutorials:
ADO - Wiki

 

Share this post


Link to post
Share on other sites

#6 ·  Posted

okay thank you for this hint! As I mentioned these are my first steps after working though: "Program on Windows Using FREE Tools".  So the solution was:

$WorkbookToWriteIn=_Excel_BookOpen($oExcel,@ScriptDir&"\examplewb.xlsx")    ;open the workbook and define it as a varaible ($WorkbookToWriteIn)
Sleep(2000)
Beep(500, 1000)

_Excel_RangeWrite($WorkbookToWriteIn,1,"Hello world!","A11")

 

 

Share this post


Link to post
Share on other sites

#7 ·  Posted (edited)

From the error message in post #4 I assume you want to write a new row each time you open the workbook.
If this is true then I suggest to have a  look at the wiki. It explains how to work with ranges in Excel. You would need something like "UsedRange.Rows.Count + 1"

Edited by water

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
PowerPoint (2015-06-06 - Version 0.0.5.0) - Download - General Help & Support

Tutorials:
ADO - Wiki

 

Share this post


Link to post
Share on other sites

#8 ·  Posted

As water mentioned:

#include <Excel.au3>

;~ Read Only File
Local $sReadFile = @ScriptDir & "\ReadFile.xlsx"
;~ Save File Name
Local $sSaveFile = @ScriptDir & "\SaveFile.xlsx"

;~ Create Array
Local $aPeople[3][2] = [["John",34],["Steve",23],["Bob",41]]

;~ Open Excel
$oExcel = _Excel_Open()
;~ Open ReadFile.xlsx as Read Only
$oWorkBook = _Excel_BookOpen($oExcel, $sReadFile, True)
;~ Write Array to Excel starting at A1
_Excel_RangeWrite($oWorkBook, $oWorkBook.ActiveSheet, $aPeople)
;~ Save WorkBook
_Excel_BookSaveAs($oWorkBook, $sSaveFile, Default, True)
;~ Rewrite $aPeople to next empty row in Cell A
_Excel_RangeWrite($oWorkBook, $oWorkBook.ActiveSheet, $aPeople, "A" & $oWorkBook.ActiveSheet.UsedRange.Rows.Count + 1)
_Excel_BookSave($oWorkBook)

 

1 person likes this

Share this post


Link to post
Share on other sites

#9 ·  Posted

As you can see in the code posted by Subz he uses a prefix for variables. So you always know what to expect when using a variable (a string, an object, an integer ...).
More Good Coding Practices are described here.

1 person likes this

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
PowerPoint (2015-06-06 - Version 0.0.5.0) - Download - General Help & Support

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