Jump to content
Julia_Muc

Excel UDF Help

Recommended Posts

Julia_Muc

Hi wise guys of this really helpful Forum,

I tried to solve my Problem via searching in Internet and this Forum, but couln't find anything.

I'm trying to open an existing Excel file, Change something in it (normally refresh data Connections | in example here just send some text). Afterwards I want to Save (as) the file and Close it. Function _Excel_BookSave(as) sends an @error = 1 and is telling me, that my workbook is not an object. However if i just open the Excel workbook and try to save it without changing something in between open and save, there is no error and the file is getting saved. Any ideas how to solve my Problem?

By the way, using Excel 2013

;Test_SAve.au3

#include <Excel.au3>
#include <MsgBoxConstants.au3>

Local $FilePath = @UserProfileDir & "\Documents\Worksheet.xls"

Local $oExcel = _Excel_Open()
Global $oBook = _Excel_BookNew($oExcel)
ConsoleWrite($FilePath)
Send('test') ;commenting out this and the following line is making the save function work
Sleep(20000)
_Excel_BookSaveAs($oBook, $FilePath,Default,true)
    IF @error Then 
            MsgBox(48,"Error Saving Excel", "There was a error while saving the Excel file" & @CRLF & @error)
            
        Else
            MsgBox(0,"Success","File was successfully saved as " & $filepath)
        EndIf
_Excel_Close($oExcel, True)

Thanks a lot!

Share this post


Link to post
Share on other sites
Earthshine

I am looking at it. so far, for me, it opens, puts in the word test to the first cell in upper left of worksheet, and does not save. so I can test this and get back.

  • Like 1

My resources are limited. You must ask the right questions

 

Share this post


Link to post
Share on other sites
Earthshine

Remove 

Edited by Earthshine

My resources are limited. You must ask the right questions

 

Share this post


Link to post
Share on other sites
Earthshine

ok, fixed it. creates new, inputs text, saves as new file and tells user, then closes once user clicks ok. happy programming. I based the changes on the Example Script that I posted a link to, that is also embedded in the actual Help File for AutoIt.

;Test_SAve.au3

#include <Excel.au3>
#include <MsgBoxConstants.au3>

Local $FilePath = @UserProfileDir & "\Documents\Worksheet.xlsx"
Local $oExcel = _Excel_Open()

Local $oBook = _Excel_BookNew($oExcel)
If Not IsObj($oBook) Or ObjName($oBook, 1) <> "_Workbook" Then Exit SetError(1, 0, 0)

ConsoleWrite($FilePath)

_Excel_RangeWrite($oBook, Default, "Test", "A1")
If @error Then Exit MsgBox($MB_SYSTEMMODAL, "Excel UDF: _Excel_BookSave Example 1", "Error writing to cell 'A1'." & @CRLF & "@error = " & @error & ", @extended = " & @extended)

_Excel_BookSaveAs($oBook, $FilePath, Default, True)
If @error Then
    MsgBox(48,"Error Saving Excel", "There was a error while saving the Excel file" & @CRLF & @error)
Else
    MsgBox(0,"Success","File was successfully saved as " & $filepath)
EndIf

_Excel_Close($oExcel)

 

Edited by Earthshine

My resources are limited. You must ask the right questions

 

Share this post


Link to post
Share on other sites
Earthshine

So, it appears in your example that you posted that the  workbook is not an valid object.

it was returning 1, not an object

 

Also, the Send command is really messing it up.  After you use the Send command, it's not a valid object. use this:

_Excel_RangeWrite($oBook, Default, "Test", "A1")

you may have uncovered a bug in Send() function. It seems to invalidate the object, and i do not know why

Edited by Earthshine

My resources are limited. You must ask the right questions

 

Share this post


Link to post
Share on other sites
water

You should check @error after EACH call of an _Excel_* function.
You shouldn't mix automating Excel using COM (by the Excel UDF) and the Excel GUI.

;Test_SAve.au3

#include <Excel.au3>
#include <MsgBoxConstants.au3>

Local $FilePath = @UserProfileDir & "\Documents\Worksheet.xlsx"
Local $oExcel = _Excel_Open()
If @error Then Exit MsgBox($MB_SYSTEMMODAL, "_Excel_Open", "Error starting Excel application." & @CRLF & "@error = " & @error & ", @extended = " & @extended)
Local $oBook = _Excel_BookNew($oExcel)
If @error Then Exit MsgBox($MB_SYSTEMMODAL, "_Excel_BookNew", "Error creating new Workbook." & @CRLF & "@error = " & @error & ", @extended = " & @extended)
_Excel_RangeWrite($oBook, Default, "Test", "A1")
If @error Then Exit MsgBox($MB_SYSTEMMODAL, "_Excel_RangeWrite", "Error writing to cell 'A1'." & @CRLF & "@error = " & @error & ", @extended = " & @extended)
_Excel_BookSaveAs($oBook, $FilePath, Default, True)
If @error Then
    MsgBox(48, "_Excel_BookSaveAs", "There was a error while saving the Excel file" & @CRLF & @error)
Else
    MsgBox(0, "_Excel_BookSaveAs", "File was successfully saved as " & $filepath)
EndIf
_Excel_Close($oExcel)

BTW: Which version of Excel do you run? Just noticed that this info was posted before.

Excel 2013 uses .XLSX as default format. So you need to set the extension to .XLSX as well as Earthshine mentiond in post #4.
 

Local $FilePath = @UserProfileDir & "\Documents\Worksheet.xlsx"

 

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
Earthshine

She told us everything in her initial post

 

Error checking or not it’s the Send command that screwing it up and I already posted that

Edited by Earthshine

My resources are limited. You must ask the right questions

 

Share this post


Link to post
Share on other sites
water

Can't test at the moment. And as I have a german language version of Office I'm sure Excel here doesn't behave the same way as your version.

Depending which control of Excel (a cell or the ribbon or ...) consumes the sent string Excel might close the opened workbook.
So in the script $oBook is still unchanged. But as the Book no longer exists IsObj or  Objname return the error.

If Not IsObj($oWorkbook) Or ObjName($oWorkbook, 1) <> "_Workbook" Then Return SetError(1, 0, 0)

 


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
Earthshine

i tested it a bit. if you replace her. Replace Send with _Excel_RangeWrite and it works great! i agree, always error check and log. anyway, this modified sample based on the OP sample runs great, even on Office 2016/365

 

;Test_SAve.au3

#include <Excel.au3>
#include <MsgBoxConstants.au3>

Local $FilePath = @UserProfileDir & "\Documents\Worksheet.xls"

Local $oExcel = _Excel_Open()
Global $oBook = _Excel_BookNew($oExcel)
ConsoleWrite($FilePath)
_Excel_RangeWrite($oBook, Default, "Test", "A1")
If @error Then Exit MsgBox($MB_SYSTEMMODAL, "Excel UDF: _Excel_BookSave Example 1", "Error writing to cell 'A1'." & @CRLF & "@error = " & @error & ", @extended = " & @extended)

;~ Send('test') ;commenting out this and the following line is making the save function work
;~ Sleep(20000)
_Excel_BookSaveAs($oBook, $FilePath,Default,true)
    IF @error Then
            MsgBox(48,"Error Saving Excel", "There was a error while saving the Excel file" & @CRLF & @error)

        Else
            MsgBox(0,"Success","File was successfully saved as " & $filepath)
        EndIf
_Excel_Close($oExcel, True)

HOWEVER, make sure you use the right extension, like, use .xlsx because when I opened the generated output from the test I get this. If I create Workbook.xlsx, it opens fine with the latest versions of office, and since she used Default, I would think she needs to create .xlsx

Water already talked about this and I figured it out by trying to help. LOL.

 

Capture.PNG

 

;Test_SAve.au3

#include <Excel.au3>
#include <MsgBoxConstants.au3>

Local $FilePath = @UserProfileDir & "\Documents\Worksheet.xlsx"

Local $oExcel = _Excel_Open()
Global $oBook = _Excel_BookNew($oExcel)
ConsoleWrite($FilePath)
_Excel_RangeWrite($oBook, Default, "Test", "A1")
If @error Then Exit MsgBox($MB_SYSTEMMODAL, "Excel UDF: _Excel_BookSave Example 1", "Error writing to cell 'A1'." & @CRLF & "@error = " & @error & ", @extended = " & @extended)

;~ Send('test') ;commenting out this and the following line is making the save function work
;~ Sleep(20000)
_Excel_BookSaveAs($oBook, $FilePath,Default,true)
    IF @error Then
            MsgBox(48,"Error Saving Excel", "There was a error while saving the Excel file" & @CRLF & @error)

        Else
            MsgBox(0,"Success","File was successfully saved as " & $filepath)
        EndIf
_Excel_Close($oExcel, True)

this runs correctly using .xlsx

Edited by Earthshine

My resources are limited. You must ask the right questions

 

Share this post


Link to post
Share on other sites
Julia_Muc

thanks a lot guys, will immediately try your great tips

Share this post


Link to post
Share on other sites
Julia_Muc

so, i just tested and you're right. Send messes it up and the example script of Earthshine is working - even on a german Excel Basis :-)

 

However in my initial script, I don't want to sent some text, that was just for me to make it more easy to test. Actually I want to refresh all the included data Connections in the Excel. At the Moment i'm doing this with

Send('{ALT}' & 'v' & 'k' & 'l')

for pressing the button in Excel.

 

Is there some Excel Function for it. As it is _Excel_RangeWrite for substituting Send Text?

Share this post


Link to post
Share on other sites
Julia_Muc

ok, last post from me so far. I found the

$excel.Workbooks(1).RefreshAll

that helps. Any other ideas or comments on the Topic?

I really appreciate your help. Thanks a lot | Dankeschön :-)

  • Like 1

Share this post


Link to post
Share on other sites
Earthshine

https://support.office.com/en-us/article/Refresh-connected-imported-data-e76a38b0-e2e1-400b-9f2f-c87b9b18c092

I am not sure if Excel UDF can do what you need. You may need the IUIAutomation stuff to go after refreshing imported data or database queries

Quote

 

Do one of the following:

  • To refresh a specific data connection in the workbook, click a cell in the external data range. On the Datatab, in the Connections group, click the arrow next to Refresh All, and then click Refresh.

  • To refresh all data connections in the workbook, click Refresh All on the Data tab, in the Connectionsgroup.

Note: If you have more than one workbook open, you must repeat the operation in each workbook.

 

Edited by Earthshine

My resources are limited. You must ask the right questions

 

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

×

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.