Excel UDF Help

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

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

[Earthshine]

Remove 

Edited November 27, 2017 by Earthshine

[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 November 27, 2017 by Earthshine

[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 November 27, 2017 by Earthshine

[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 November 27, 2017 by water

[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 November 27, 2017 by Earthshine

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

 

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

 

 

;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 November 27, 2017 by Earthshine

[Julia_Muc]

thanks a lot guys, will immediately try your great tips

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

[Julia_Muc]

Sorry, Image wasn't posted in my previous text

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

[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

 

Refresh data from a Microsoft Query, the Data Connection Wizard, or Web query

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 November 29, 2017 by Earthshine