Jump to content
Sign in to follow this  
water

Update the Excel UDF that comes with AutoIt

Recommended Posts

If FileExists can't find the file then function _ESxcel-BookOpen returns @error = 2.
So the question is: Does AutoIt support the way you try to access files?

​Is this a different function? _ESxcel-BookOpen

I would assume, that none of the functions that I have tried so far open the file from a sharepoint site  - except for Run("C:\Program Files\Microsoft Office\Office15\EXCEL.EXE /h " & '"' & $sURL & '"')

I have read other threads where this is an issue, so I have to find a work around - maybe open it with Run command and then save it locally to each computer, but that seems to be a nasty work around, as the file is updated about every 2 months.


All by me:

"Sometimes you have to go back to where you started, to get to where you want to go." 

"Everybody catches up with everyone, eventually" 

"As you teach others, you are really teaching yourself."

From my dad

"Do not worry about yesterday, as the only thing that you can control is tomorrow."

 

WindowsError.gif

WIKI | Tabs; | Arrays; | Strings | Wiki Arrays | How to ask a Question | Forum Search | FAQ | Tutorials | Original FAQ | ONLINE HELP | UDF's Wiki | AutoIt PDF

AutoIt Snippets | Multple Guis | Interrupting a running function | Another Send

StringRegExp | StringRegExp Help | RegEXTester | REG TUTOR | Reg TUTOT 2

AutoItSetOption | Macros | AutoIt Snippets | Wrapper | Autoit  Docs

SCITE | SciteJump | BB | MyTopics | Programming | UDFs | AutoIt 123 | UDFs Form | UDF

Learning to script | Tutorials | Documentation | IE.AU3 | Games? | FreeSoftware | Path_Online | Core Language

Programming Tips

Excel Changes

ControlHover.UDF

GDI_Plus

Draw_On_Screen

GDI Basics

GDI_More_Basics

GDI Rotate

GDI Graph

GDI  CheckExistingItems

GDI Trajectory

Replace $ghGDIPDll with $__g_hGDIPDll

DLL 101?

Array via Object

GDI Swimlane

GDI Plus French 101 Site

GDI Examples UEZ

GDI Basic Clock

GDI Detection

Ternary operator

Share this post


Link to post
Share on other sites

​Is this a different function? _ESxcel-BookOpen

No, it is a typo :)


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2020-07-21 - Version 1.5.1.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2020-06-27 - Version 1.6.1.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX_GUI (NEW 2020-06-27 - Version 1.3.2.0) - Download
Outlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - Wiki
ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts
PowerPoint (2017-06-06 - Version 0.0.5.0) - Download - General Help & Support
Excel - Example Scripts - Wiki
Word - Wiki
Task Scheduler (2019-12-03 - Version 1.5.1.0) - Download - General Help & Support - Wiki

Tutorials:
ADO - Wiki, WebDriver - Wiki

 

Share this post


Link to post
Share on other sites

If FileExists can't find the file

​I tested this with the UNC, for the file in question with FileExist, and it does find the file, but I am still getting the error 3 extended -2147352567. Any idea why I am unable to load the file with _Excel_BookOpen?

 

EDIT

I am also able to find it with InetGetSize

Edited by nitekram
added InetGetSize

All by me:

"Sometimes you have to go back to where you started, to get to where you want to go." 

"Everybody catches up with everyone, eventually" 

"As you teach others, you are really teaching yourself."

From my dad

"Do not worry about yesterday, as the only thing that you can control is tomorrow."

 

WindowsError.gif

WIKI | Tabs; | Arrays; | Strings | Wiki Arrays | How to ask a Question | Forum Search | FAQ | Tutorials | Original FAQ | ONLINE HELP | UDF's Wiki | AutoIt PDF

AutoIt Snippets | Multple Guis | Interrupting a running function | Another Send

StringRegExp | StringRegExp Help | RegEXTester | REG TUTOR | Reg TUTOT 2

AutoItSetOption | Macros | AutoIt Snippets | Wrapper | Autoit  Docs

SCITE | SciteJump | BB | MyTopics | Programming | UDFs | AutoIt 123 | UDFs Form | UDF

Learning to script | Tutorials | Documentation | IE.AU3 | Games? | FreeSoftware | Path_Online | Core Language

Programming Tips

Excel Changes

ControlHover.UDF

GDI_Plus

Draw_On_Screen

GDI Basics

GDI_More_Basics

GDI Rotate

GDI Graph

GDI  CheckExistingItems

GDI Trajectory

Replace $ghGDIPDll with $__g_hGDIPDll

DLL 101?

Array via Object

GDI Swimlane

GDI Plus French 101 Site

GDI Examples UEZ

GDI Basic Clock

GDI Detection

Ternary operator

Share this post


Link to post
Share on other sites

Looks like Excel has a problem with the path you specified.
Can you please add the following lines at hte top of your script so we get better error information?

#include <Debug.au3>
_DebugSetup()
_DebugCOMError()

 


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2020-07-21 - Version 1.5.1.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2020-06-27 - Version 1.6.1.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX_GUI (NEW 2020-06-27 - Version 1.3.2.0) - Download
Outlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - Wiki
ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts
PowerPoint (2017-06-06 - Version 0.0.5.0) - Download - General Help & Support
Excel - Example Scripts - Wiki
Word - Wiki
Task Scheduler (2019-12-03 - Version 1.5.1.0) - Download - General Help & Support - Wiki

Tutorials:
ADO - Wiki, WebDriver - Wiki

 

Share this post


Link to post
Share on other sites

 

@@ DEBUG COM Error encountered in SCRIPT.EXE (-1) :
    Number          = 0x80020009 (-2147352567)
    WinDescription  = Exception occurred.
    Description     = Microsoft Excel cannot access the file 'UNC path is correct'. There are several possible reasons:

• The file name or path does not exist.
• The file is being used by another program.
• The workbook you are trying to save has the same name as a currently open workbook.
    Source          = Microsoft Excel
    HelpFile        = xlmain11.chm
    HelpContext     = 0
    LastDllError    = 0
    Retcode         = 0x800A03EC

This is my call that is getting the error:

Local $oWorkbook = _Excel_BookOpen($oAppl, $sWorkbook, True, False)

 

 

Edited by nitekram
added some code

All by me:

"Sometimes you have to go back to where you started, to get to where you want to go." 

"Everybody catches up with everyone, eventually" 

"As you teach others, you are really teaching yourself."

From my dad

"Do not worry about yesterday, as the only thing that you can control is tomorrow."

 

WindowsError.gif

WIKI | Tabs; | Arrays; | Strings | Wiki Arrays | How to ask a Question | Forum Search | FAQ | Tutorials | Original FAQ | ONLINE HELP | UDF's Wiki | AutoIt PDF

AutoIt Snippets | Multple Guis | Interrupting a running function | Another Send

StringRegExp | StringRegExp Help | RegEXTester | REG TUTOR | Reg TUTOT 2

AutoItSetOption | Macros | AutoIt Snippets | Wrapper | Autoit  Docs

SCITE | SciteJump | BB | MyTopics | Programming | UDFs | AutoIt 123 | UDFs Form | UDF

Learning to script | Tutorials | Documentation | IE.AU3 | Games? | FreeSoftware | Path_Online | Core Language

Programming Tips

Excel Changes

ControlHover.UDF

GDI_Plus

Draw_On_Screen

GDI Basics

GDI_More_Basics

GDI Rotate

GDI Graph

GDI  CheckExistingItems

GDI Trajectory

Replace $ghGDIPDll with $__g_hGDIPDll

DLL 101?

Array via Object

GDI Swimlane

GDI Plus French 101 Site

GDI Examples UEZ

GDI Basic Clock

GDI Detection

Ternary operator

Share this post


Link to post
Share on other sites

Looks like you are out of look accessing the Excel file the way you try to do.

When you search the forum for "Excel Sharepoint" you will find some threads and - maybe - a solution.
 


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2020-07-21 - Version 1.5.1.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2020-06-27 - Version 1.6.1.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX_GUI (NEW 2020-06-27 - Version 1.3.2.0) - Download
Outlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - Wiki
ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts
PowerPoint (2017-06-06 - Version 0.0.5.0) - Download - General Help & Support
Excel - Example Scripts - Wiki
Word - Wiki
Task Scheduler (2019-12-03 - Version 1.5.1.0) - Download - General Help & Support - Wiki

Tutorials:
ADO - Wiki, WebDriver - Wiki

 

Share this post


Link to post
Share on other sites

Okay, I looked prior to posting here, and really could not find anything that would help.

But I think I found a work around. Since I was able to see that the file did exist with the UNC, I just did a FileCopy to a temp directory locally, and then read the file with the Excel UDF from that location - though it seems that it should work the other way - I guess it is an issue with SharePoint.

Good think I only want to read the file and not actually update the file, as I do not believe the same would be true, to FileCopy it to the sharepoint location. I do not want to test that, as the sharepoint server is live, but if anyone else could do it in a test environment, and could post the results, that would be great.

One more idea. Do you think it has to do with permissions via the Excel UDF when trying to access a file on sharepoint - just a thought?

Edited by nitekram
spelling

All by me:

"Sometimes you have to go back to where you started, to get to where you want to go." 

"Everybody catches up with everyone, eventually" 

"As you teach others, you are really teaching yourself."

From my dad

"Do not worry about yesterday, as the only thing that you can control is tomorrow."

 

WindowsError.gif

WIKI | Tabs; | Arrays; | Strings | Wiki Arrays | How to ask a Question | Forum Search | FAQ | Tutorials | Original FAQ | ONLINE HELP | UDF's Wiki | AutoIt PDF

AutoIt Snippets | Multple Guis | Interrupting a running function | Another Send

StringRegExp | StringRegExp Help | RegEXTester | REG TUTOR | Reg TUTOT 2

AutoItSetOption | Macros | AutoIt Snippets | Wrapper | Autoit  Docs

SCITE | SciteJump | BB | MyTopics | Programming | UDFs | AutoIt 123 | UDFs Form | UDF

Learning to script | Tutorials | Documentation | IE.AU3 | Games? | FreeSoftware | Path_Online | Core Language

Programming Tips

Excel Changes

ControlHover.UDF

GDI_Plus

Draw_On_Screen

GDI Basics

GDI_More_Basics

GDI Rotate

GDI Graph

GDI  CheckExistingItems

GDI Trajectory

Replace $ghGDIPDll with $__g_hGDIPDll

DLL 101?

Array via Object

GDI Swimlane

GDI Plus French 101 Site

GDI Examples UEZ

GDI Basic Clock

GDI Detection

Ternary operator

Share this post


Link to post
Share on other sites

I do not think it is a permission issue. The UDF uses COM to access Excel and workbooks - so it might be a problem with Excels COM interface.


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2020-07-21 - Version 1.5.1.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2020-06-27 - Version 1.6.1.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX_GUI (NEW 2020-06-27 - Version 1.3.2.0) - Download
Outlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - Wiki
ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts
PowerPoint (2017-06-06 - Version 0.0.5.0) - Download - General Help & Support
Excel - Example Scripts - Wiki
Word - Wiki
Task Scheduler (2019-12-03 - Version 1.5.1.0) - Download - General Help & Support - Wiki

Tutorials:
ADO - Wiki, WebDriver - Wiki

 

Share this post


Link to post
Share on other sites

Looks like Excel has some methods to work with workbooks on servers.
Check CanCheckIn, CheckIn, CheckInWithVersion, LockServerFile, ServerPolicy for the Workbook object plus
CanCheckOut and CheckOut for the Workbooks object.

 

 

 
Edited by water

My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2020-07-21 - Version 1.5.1.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2020-06-27 - Version 1.6.1.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX_GUI (NEW 2020-06-27 - Version 1.3.2.0) - Download
Outlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - Wiki
ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts
PowerPoint (2017-06-06 - Version 0.0.5.0) - Download - General Help & Support
Excel - Example Scripts - Wiki
Word - Wiki
Task Scheduler (2019-12-03 - Version 1.5.1.0) - Download - General Help & Support - Wiki

Tutorials:
ADO - Wiki, WebDriver - Wiki

 

Share this post


Link to post
Share on other sites

Something like this should give you an idea how it works:

Private Sub testthis()
    ' Attempt to open a sharepoint file
    Dim oWB As Workbook, MyPath As String
    MyPath = "https://sharepoint.domain.com/folder1/folder2/folder%20number%20three/filename.xlsx"  ' Obviously this is just a sample path. My code has the proper path & filename
    If Workbooks.CanCheckOut(MyPath) = True Then
        MsgBox ("File on Sharepoint CAN be checked out.")
        Workbooks.CheckOut MyPath
        Set oWB = Workbooks.Open(MyPath)
        
    Else
        MsgBox ("File on Sharepoint can NOT be checked out." + Chr(13) + _
                "Make sure no one else is working in the file." + Chr(13) + _
                "Including yourself.")
        Exit Sub
    End If
    MsgBox (oWB.Worksheets("Specs-Undated-Small-Low%").UsedRange.Rows.Count)
    oWB.CheckIn (False)
    Set oWB = Nothing
End Sub

http://www.vbforums.com/showthread.php?728921-EXCEL2010-VBA-opening-another-Excel-file-from-a-sharepoint-site

Edited by water

My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2020-07-21 - Version 1.5.1.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2020-06-27 - Version 1.6.1.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX_GUI (NEW 2020-06-27 - Version 1.3.2.0) - Download
Outlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - Wiki
ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts
PowerPoint (2017-06-06 - Version 0.0.5.0) - Download - General Help & Support
Excel - Example Scripts - Wiki
Word - Wiki
Task Scheduler (2019-12-03 - Version 1.5.1.0) - Download - General Help & Support - Wiki

Tutorials:
ADO - Wiki, WebDriver - Wiki

 

Share this post


Link to post
Share on other sites

Thanks, I will look into that and if I have any luck, I will report my findings.


All by me:

"Sometimes you have to go back to where you started, to get to where you want to go." 

"Everybody catches up with everyone, eventually" 

"As you teach others, you are really teaching yourself."

From my dad

"Do not worry about yesterday, as the only thing that you can control is tomorrow."

 

WindowsError.gif

WIKI | Tabs; | Arrays; | Strings | Wiki Arrays | How to ask a Question | Forum Search | FAQ | Tutorials | Original FAQ | ONLINE HELP | UDF's Wiki | AutoIt PDF

AutoIt Snippets | Multple Guis | Interrupting a running function | Another Send

StringRegExp | StringRegExp Help | RegEXTester | REG TUTOR | Reg TUTOT 2

AutoItSetOption | Macros | AutoIt Snippets | Wrapper | Autoit  Docs

SCITE | SciteJump | BB | MyTopics | Programming | UDFs | AutoIt 123 | UDFs Form | UDF

Learning to script | Tutorials | Documentation | IE.AU3 | Games? | FreeSoftware | Path_Online | Core Language

Programming Tips

Excel Changes

ControlHover.UDF

GDI_Plus

Draw_On_Screen

GDI Basics

GDI_More_Basics

GDI Rotate

GDI Graph

GDI  CheckExistingItems

GDI Trajectory

Replace $ghGDIPDll with $__g_hGDIPDll

DLL 101?

Array via Object

GDI Swimlane

GDI Plus French 101 Site

GDI Examples UEZ

GDI Basic Clock

GDI Detection

Ternary operator

Share this post


Link to post
Share on other sites

Thanks - I'm sure this will come in handy for future reference :)


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2020-07-21 - Version 1.5.1.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2020-06-27 - Version 1.6.1.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX_GUI (NEW 2020-06-27 - Version 1.3.2.0) - Download
Outlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - Wiki
ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts
PowerPoint (2017-06-06 - Version 0.0.5.0) - Download - General Help & Support
Excel - Example Scripts - Wiki
Word - Wiki
Task Scheduler (2019-12-03 - Version 1.5.1.0) - Download - General Help & Support - Wiki

Tutorials:
ADO - Wiki, WebDriver - Wiki

 

Share this post


Link to post
Share on other sites

By the way, not sure if it was missed or if it is because of the update to the forum, but the only links you have is for CanCheckIn and the CheckOut the other ones look like links but are not hyperlinks


All by me:

"Sometimes you have to go back to where you started, to get to where you want to go." 

"Everybody catches up with everyone, eventually" 

"As you teach others, you are really teaching yourself."

From my dad

"Do not worry about yesterday, as the only thing that you can control is tomorrow."

 

WindowsError.gif

WIKI | Tabs; | Arrays; | Strings | Wiki Arrays | How to ask a Question | Forum Search | FAQ | Tutorials | Original FAQ | ONLINE HELP | UDF's Wiki | AutoIt PDF

AutoIt Snippets | Multple Guis | Interrupting a running function | Another Send

StringRegExp | StringRegExp Help | RegEXTester | REG TUTOR | Reg TUTOT 2

AutoItSetOption | Macros | AutoIt Snippets | Wrapper | Autoit  Docs

SCITE | SciteJump | BB | MyTopics | Programming | UDFs | AutoIt 123 | UDFs Form | UDF

Learning to script | Tutorials | Documentation | IE.AU3 | Games? | FreeSoftware | Path_Online | Core Language

Programming Tips

Excel Changes

ControlHover.UDF

GDI_Plus

Draw_On_Screen

GDI Basics

GDI_More_Basics

GDI Rotate

GDI Graph

GDI  CheckExistingItems

GDI Trajectory

Replace $ghGDIPDll with $__g_hGDIPDll

DLL 101?

Array via Object

GDI Swimlane

GDI Plus French 101 Site

GDI Examples UEZ

GDI Basic Clock

GDI Detection

Ternary operator

Share this post


Link to post
Share on other sites

I tried the following - can you make sure it looks good. But it does come back as FALSE - I have tried it both on sharepoint and local, both have the same results

 

If $oAppl.CanCheckOut($sTempFileLocation) Then
                MsgBox('', '', 'true can check out')
            Else
                MsgBox('', '', 'Fales')
            EndIf

 


All by me:

"Sometimes you have to go back to where you started, to get to where you want to go." 

"Everybody catches up with everyone, eventually" 

"As you teach others, you are really teaching yourself."

From my dad

"Do not worry about yesterday, as the only thing that you can control is tomorrow."

 

WindowsError.gif

WIKI | Tabs; | Arrays; | Strings | Wiki Arrays | How to ask a Question | Forum Search | FAQ | Tutorials | Original FAQ | ONLINE HELP | UDF's Wiki | AutoIt PDF

AutoIt Snippets | Multple Guis | Interrupting a running function | Another Send

StringRegExp | StringRegExp Help | RegEXTester | REG TUTOR | Reg TUTOT 2

AutoItSetOption | Macros | AutoIt Snippets | Wrapper | Autoit  Docs

SCITE | SciteJump | BB | MyTopics | Programming | UDFs | AutoIt 123 | UDFs Form | UDF

Learning to script | Tutorials | Documentation | IE.AU3 | Games? | FreeSoftware | Path_Online | Core Language

Programming Tips

Excel Changes

ControlHover.UDF

GDI_Plus

Draw_On_Screen

GDI Basics

GDI_More_Basics

GDI Rotate

GDI Graph

GDI  CheckExistingItems

GDI Trajectory

Replace $ghGDIPDll with $__g_hGDIPDll

DLL 101?

Array via Object

GDI Swimlane

GDI Plus French 101 Site

GDI Examples UEZ

GDI Basic Clock

GDI Detection

Ternary operator

Share this post


Link to post
Share on other sites

You need to use the Workbooks object, so it should look like:

If $oAppl.Workbooks.CanCheckOut($sTempFileLocation) Then
    MsgBox('', '', 'true can check out')
Else
    MsgBox('', '', 'Fales')
EndIf

 

Edited by water

My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2020-07-21 - Version 1.5.1.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2020-06-27 - Version 1.6.1.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX_GUI (NEW 2020-06-27 - Version 1.3.2.0) - Download
Outlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - Wiki
ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts
PowerPoint (2017-06-06 - Version 0.0.5.0) - Download - General Help & Support
Excel - Example Scripts - Wiki
Word - Wiki
Task Scheduler (2019-12-03 - Version 1.5.1.0) - Download - General Help & Support - Wiki

Tutorials:
ADO - Wiki, WebDriver - Wiki

 

Share this post


Link to post
Share on other sites

By the way, not sure if it was missed or if it is because of the update to the forum, but the only links you have is for CanCheckIn and the CheckOut the other ones look like links but are not hyperlinks

​In first place I posted this in the wrong forum:

Maybe just a copy & paste problem. All this methods can be foun in the Workboosk and Workbook objects:

Workbooks Object
Workbook Object

 


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2020-07-21 - Version 1.5.1.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2020-06-27 - Version 1.6.1.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX_GUI (NEW 2020-06-27 - Version 1.3.2.0) - Download
Outlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - Wiki
ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts
PowerPoint (2017-06-06 - Version 0.0.5.0) - Download - General Help & Support
Excel - Example Scripts - Wiki
Word - Wiki
Task Scheduler (2019-12-03 - Version 1.5.1.0) - Download - General Help & Support - Wiki

Tutorials:
ADO - Wiki, WebDriver - Wiki

 

Share this post


Link to post
Share on other sites

Okay. Well found something strange. After I made your change, I was able to get true (meaning the method said I could check it out), so I did a checkout to see if that would make a difference, but now I am unable to get it so say true again, and not sure how to check it in again lol.
I even went to the sharepoint server and tried to check it out there, and checked it back in, but still not able to check if I can check it out. It has been a long night - I will try again tonight when I am working.


All by me:

"Sometimes you have to go back to where you started, to get to where you want to go." 

"Everybody catches up with everyone, eventually" 

"As you teach others, you are really teaching yourself."

From my dad

"Do not worry about yesterday, as the only thing that you can control is tomorrow."

 

WindowsError.gif

WIKI | Tabs; | Arrays; | Strings | Wiki Arrays | How to ask a Question | Forum Search | FAQ | Tutorials | Original FAQ | ONLINE HELP | UDF's Wiki | AutoIt PDF

AutoIt Snippets | Multple Guis | Interrupting a running function | Another Send

StringRegExp | StringRegExp Help | RegEXTester | REG TUTOR | Reg TUTOT 2

AutoItSetOption | Macros | AutoIt Snippets | Wrapper | Autoit  Docs

SCITE | SciteJump | BB | MyTopics | Programming | UDFs | AutoIt 123 | UDFs Form | UDF

Learning to script | Tutorials | Documentation | IE.AU3 | Games? | FreeSoftware | Path_Online | Core Language

Programming Tips

Excel Changes

ControlHover.UDF

GDI_Plus

Draw_On_Screen

GDI Basics

GDI_More_Basics

GDI Rotate

GDI Graph

GDI  CheckExistingItems

GDI Trajectory

Replace $ghGDIPDll with $__g_hGDIPDll

DLL 101?

Array via Object

GDI Swimlane

GDI Plus French 101 Site

GDI Examples UEZ

GDI Basic Clock

GDI Detection

Ternary operator

Share this post


Link to post
Share on other sites

This line from the VB example I posted above checks the Excel workbook in and drops all changes made to the workbook.

oWB.CheckIn (False)

 


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2020-07-21 - Version 1.5.1.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2020-06-27 - Version 1.6.1.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX_GUI (NEW 2020-06-27 - Version 1.3.2.0) - Download
Outlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - Wiki
ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts
PowerPoint (2017-06-06 - Version 0.0.5.0) - Download - General Help & Support
Excel - Example Scripts - Wiki
Word - Wiki
Task Scheduler (2019-12-03 - Version 1.5.1.0) - Download - General Help & Support - Wiki

Tutorials:
ADO - Wiki, WebDriver - Wiki

 

Share this post


Link to post
Share on other sites

I used $oAppl.Workbooks.CheckIn(False) - going off your other example.


All by me:

"Sometimes you have to go back to where you started, to get to where you want to go." 

"Everybody catches up with everyone, eventually" 

"As you teach others, you are really teaching yourself."

From my dad

"Do not worry about yesterday, as the only thing that you can control is tomorrow."

 

WindowsError.gif

WIKI | Tabs; | Arrays; | Strings | Wiki Arrays | How to ask a Question | Forum Search | FAQ | Tutorials | Original FAQ | ONLINE HELP | UDF's Wiki | AutoIt PDF

AutoIt Snippets | Multple Guis | Interrupting a running function | Another Send

StringRegExp | StringRegExp Help | RegEXTester | REG TUTOR | Reg TUTOT 2

AutoItSetOption | Macros | AutoIt Snippets | Wrapper | Autoit  Docs

SCITE | SciteJump | BB | MyTopics | Programming | UDFs | AutoIt 123 | UDFs Form | UDF

Learning to script | Tutorials | Documentation | IE.AU3 | Games? | FreeSoftware | Path_Online | Core Language

Programming Tips

Excel Changes

ControlHover.UDF

GDI_Plus

Draw_On_Screen

GDI Basics

GDI_More_Basics

GDI Rotate

GDI Graph

GDI  CheckExistingItems

GDI Trajectory

Replace $ghGDIPDll with $__g_hGDIPDll

DLL 101?

Array via Object

GDI Swimlane

GDI Plus French 101 Site

GDI Examples UEZ

GDI Basic Clock

GDI Detection

Ternary operator

Share this post


Link to post
Share on other sites

To Checkout a workbook you need to use the Workbooks object. To CheckIn again you need to use the Workbook object as returned by _Excel_BookOpen.


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2020-07-21 - Version 1.5.1.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2020-06-27 - Version 1.6.1.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX_GUI (NEW 2020-06-27 - Version 1.3.2.0) - Download
Outlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - Wiki
ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts
PowerPoint (2017-06-06 - Version 0.0.5.0) - Download - General Help & Support
Excel - Example Scripts - Wiki
Word - Wiki
Task Scheduler (2019-12-03 - Version 1.5.1.0) - Download - General Help & Support - Wiki

Tutorials:
ADO - Wiki, WebDriver - 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
Sign in to follow this  

  • Recently Browsing   0 members

    No registered users viewing this page.

  • Similar Content

    • By Langmeister
      Hi, I'm sure that this is a simple question regarding webdriver udf but my search on the forum did not result in the exact same scenario that I need. I will adapt it to my corporate needs but at first I want it to work with an example everybody can reproduce if needed.
      I navigate to https://www.daysoftheyear.com/ and want to find out what special day today is. Using the following Code returns the wanted text that today is 'shark awareness day' in the console but it is not pasted in notepad as it is not copied as it probably should be.
      #include "wd_core.au3" #include "wd_helper.au3" Local $sDesiredCapabilities, $sSession, $sID _WD_Option("Driver", "C:\Program Files (x86)\AutoIt3\chromedriver.exe") _WD_Option('Port', 9515) _WD_Option('DriverParams', '--log-path="' & @ScriptDir & '\chrome.log"') $sDesiredCapabilities = '{"capabilities": {"alwaysMatch": {"goog:chromeOptions": {"w3c": true }}}}' _WD_Startup() $sSession = _WD_CreateSession($sDesiredCapabilities) _WD_Navigate($sSession, "https://www.daysoftheyear.com/?timezone_offset=nan") _WD_LoadWait($sSession) Sleep(3000) Local $sElement = _WD_FindElement($sSession, $_WD_LOCATOR_ByXPath, "/html/body/div[2]/header/div/div[1]/div/div[2]/h3/a") _WD_ElementAction($sSession, $sElement, 'text') ClipGet() Run("notepad.exe") Sleep(500) Send("^v") Scite returns this, therefore the text has been found but the last mile to paste it to notepad or excel does not work for me.
      __WD_Get: URL=HTTP://127.0.0.1:9515/session/b72166b774d7fefb258b3721fc1d4306/element/657e6b5d-31a9-46a8-85c4-acb7f72bf659/text __WD_Get: StatusCode=200; $iResult = 0; $sResponseText={"value":"Shark Awareness Day"}... _WD_ElementAction: {"value":"Shark Awareness Day"}... Thanks in advance! 
       
    • By pat4005
      A tiny UDF that can shoot magic packets at your computers to wake them the heck up. All credits to Olish.
      The only parameter it needs to be specified – is your machine's MAC-address (ip address (the second parameter), at which you will be sending magic packet is generating automatically from @IPAddress1 macro)
      Example:
      _WoL_WakeDevice('001CC0CAED7A') ; the second parameter (if necessery) must be a broadcast address of your local network segment (i.e. 192.168.0.255 for a network 192.168.0.0)  
      _WakeOnLan.au3
    • By GOSM
      Hey,
      as part of a larger Programm I am trying to copy the values from one cell in an Excel file to another. The code I am using is the folowing.
       
      Local $oExcel = _Excel_Open() If @error Then Exit MsgBox($MB_SYSTEMMODAL, "Excel UDF: _Excel_RangeCopy Example", "Error creating the Excel application object." & @CRLF & "@error = " & @error & ", @extended = " & @extended) _Excel_RangeCopyPaste($oExcel.ActiveSheet, "V4:W43", "X4", False, $xlPasteValues, Default, True) If @error Then Exit MsgBox($MB_SYSTEMMODAL, "Excel UDF: _Excel_RangeCopy Example 4", "Error copying rows." & @CRLF & "@error = " & @error & ", @extended = " & @extended) MsgBox($MB_SYSTEMMODAL, "Excel UDF: _Excel_RangeCopy Example 4", "2 Rows successfully pasted from the clipboard to row 1.") As I understood it the option $xlPasteValues should do the trick  but it is still copying the inserted formulas. I attached a test code and excel file.
      Thanks for the help Simon
      Copy Values Test.zip
    • By Chimp
      An "improper" "unusual" use of Excel
      the script allows the creation of artistic images by simply coloring the background of the individual cells of the Excel workbook.
      Although definitely useless ... I find it quite funny though
      have a good time
      many thanks to @UEZ , @Malkey , @water
      p.s.
      I think the pixelite + color to array process can be simplified, but I used the two ready-made functions provided by UEZ and Malkey. I thank both of you (credits  and links in listing)
      p.p.s.
      strange behaviour: while excell is filling cells, if you move the mouse pointer off the excell window, the fill speed increases ... (?)

       
      ; =============================================================================================================================== ; Name ..........: Excel in art ; Description ...: This script allows the creation of artistic images in Excel from a choosed picture. ; The picture is done by simply coloring the background of the individual cells of an Excel workbook. ; Although definitely useless, I find it quite funny though ; ; Return values .: An artistic image in an Excel workbook ; Author ........: Addiego Gianni (chimp) ; Modified ......: ; Remarks .......: Many thanks to UEZ, Malkey and Water ; Related .......: ; Link ..........: ; Example .......: ; =============================================================================================================================== #include <GDIPlus.au3> #include <Excel.au3> _PixelsToCells(50) ; <-- Parameter 50 is the cumber of horizontal Excel cells to be filled MsgBox(64, "All done", "Excel art is ready") Func _PixelsToCells($iHcells = 50) ; Check application object Local $oExcel = _Excel_Open() If Not IsObj($oExcel) Then MsgBox(16, "Error", "Sorry, You need to have 'Excel' intalled") ; Choose Image File Local $sPath = FileOpenDialog("Choose Image File", @ScriptDir & "", "Images (*.gif;*.png;*.jpg;*.bmp)| All (*.*)") If $sPath = '' Then Exit MsgBox(16, "Error", "Sorry, no image was chosen") ; Create a new Excel workbook $oWorkbook = _Excel_BookNew($oExcel, 1) ; initialize GDI+ _GDIPlus_Startup() Local $hBmp = _GDIPlus_BitmapCreateFromFile($sPath) Local $iWidth = _GDIPlus_ImageGetWidth($hBmp) ; get image width ; Local $iHeight = _GDIPlus_ImageGetHeight($hBmp) ; not needed here Local $iStep = $iWidth / $iHcells ; calculate the pixelation factor Local $hBitmap_new = _GDIPlus_PixelateBitmap($hBmp, $iStep) ; pixelate the image Local $aPixelColors = _FileImageToArray($hBitmap_new) ; get pixel colors ; reduce Excel columns width Local $xx = 1, $yy = 1 For $iCol = 0 To UBound($aPixelColors, 2) - 1 Step $iStep $oWorkbook.Sheets(1).Columns($xx).ColumnWidth = 1 $xx += 1 Next ; reduce Excel rows height For $iRow = 0 To UBound($aPixelColors) - 1 Step $iStep $oWorkbook.Sheets(1).Rows($yy).RowHeight = 9 $yy += 1 Next $xx = 1 $yy = 1 For $iRow = 0 To UBound($aPixelColors) - 1 Step $iStep For $iCol = 0 To UBound($aPixelColors, 2) - 1 Step $iStep $oWorkbook.Sheets(1).Range(_Excel_ColumnToLetter($xx) & $yy).Interior.Color = Number("0x" & $aPixelColors[$iRow][$iCol]) $xx += 1 Next $yy += 1 $xx = 1 Next _GDIPlus_BitmapDispose($hBmp) _GDIPlus_Shutdown() EndFunc ;==>_PixelsToCells ; by UEZ ; https://www.autoitscript.com/forum/topic/167707-imagepixelate/?do=findComment&comment=1227509 Func _GDIPlus_PixelateBitmap($hBitmap, $iPixelate, $bSmooth = 1) Local $iWidth = _GDIPlus_ImageGetWidth($hBitmap), $iHeight = _GDIPlus_ImageGetHeight($hBitmap) Local $iNewW = Round($iWidth / $iPixelate, 0), $iNewH = Round($iHeight / $iPixelate, 0) Local $hBitmap_scaled = _GDIPlus_BitmapCreateFromScan0($iNewW, $iNewH) Local $hCtxt = _GDIPlus_ImageGetGraphicsContext($hBitmap_scaled) Local $iInterpolation = 5 If $bSmooth Then $iInterpolation = $GDIP_INTERPOLATIONMODE_BILINEAR _GDIPlus_GraphicsSetInterpolationMode($hCtxt, $iInterpolation) _GDIPlus_GraphicsDrawImageRect($hCtxt, $hBitmap, 0, 0, $iNewW, $iNewH) _GDIPlus_GraphicsDispose($hCtxt) Local $hBitmap_pixelated = _GDIPlus_BitmapCreateFromScan0($iWidth, $iHeight) $hCtxt = _GDIPlus_ImageGetGraphicsContext($hBitmap_pixelated) _GDIPlus_GraphicsSetInterpolationMode($hCtxt, $GDIP_INTERPOLATIONMODE_NearestNeighbor) _GDIPlus_GraphicsDrawImageRectRect($hCtxt, $hBitmap_scaled, 0, 0, $iNewW, $iNewH, -$iPixelate, -$iPixelate, $iWidth + 2 * $iPixelate, $iHeight + 2 * $iPixelate) _GDIPlus_GraphicsDispose($hCtxt) Return $hBitmap_pixelated EndFunc ;==>_GDIPlus_PixelateBitmap ; by Malkey ; https://www.autoitscript.com/forum/topic/112540-is-there-a-function-for-reading-images-into-2d-arrays/?do=findComment&comment=788472 Func _FileImageToArray($hImage) Local $Reslt, $stride, $format, $Scan0, $iIW, $iIH ; , $hImage Local $v_Buffer, $width, $height ; _GDIPlus_Startup() ; $hImage = _GDIPlus_ImageLoadFromFile($sFileName) $iIW = _GDIPlus_ImageGetWidth($hImage) $iIH = _GDIPlus_ImageGetHeight($hImage) ProgressOn("Progress Bar", "Filling a " & $iIW & " x " & $iIH & " size array.", "0 percent") $Reslt = _GDIPlus_BitmapLockBits($hImage, 0, 0, $iIW, $iIH, $GDIP_ILMREAD, $GDIP_PXF32ARGB) ;Get the returned values of _GDIPlus_BitmapLockBits () $width = DllStructGetData($Reslt, "width") $height = DllStructGetData($Reslt, "height") $stride = DllStructGetData($Reslt, "stride") $format = DllStructGetData($Reslt, "format") $Scan0 = DllStructGetData($Reslt, "Scan0") Local $aArray[$height][$width] For $j = 0 To $iIH - 1 For $i = 0 To $iIW - 1 $v_Buffer = DllStructCreate("dword", $Scan0 + ($j * $stride) + ($i * 4)) $aArray[$j][$i] = StringRegExpReplace(Hex(DllStructGetData($v_Buffer, 1), 6), "(.{2})(.{2})(.{2})", "\3\2\1") ; To RGB format Next ProgressSet(Int(100 * $j / ($iIH)), Int(100 * $j / ($iIH)) & " percent") Next _GDIPlus_BitmapUnlockBits($hImage, $Reslt) ProgressOff() _GDIPlus_ImageDispose($hImage) Return $aArray EndFunc ;==>_FileImageToArray  
    • By MrCreatoR
      This UDF allows to create formatted label using pseudo element RichLabel (RichEdit actually). Formating is set by using special modificator similar to <font> tag in Html.
      Notes: This UDF is a transformation-continuation of related UDF


      Example:
      Download:
      GUIRichLabel_1.2.zip
      Small syntax related fix: GUIRichLabel_1.1.zip
      GUIRichLabel_1.1.zip
       
      History version:
×
×
  • Create New...