Spring

Excel Enable Calculations from Read Only?

12 posts in this topic

#1 ·  Posted

Hello, I have an excel file that I need to open from a SharePoint site, refresh all, then save and close.  Problem is, the excel doc opens in read only.  So while I can open, refresh and close the document, it will not save.  To do this manually, I just have to hit the Edit Workbook button at the top of the document.  Anyone know of a way to get this to work?  Any help would be appreciated.  My current script is below

 

#include <Excel.au3>

Local $oExcel = _Excel_Open() 

Local $oWorkbook = _Excel_BookOpen($oExcel, "filenamefullpath") 

$oExcel.Workbooks(1).RefreshAll ; refresh

_Excel_BookClose ( $oWorkbook , True )

_Excel_close ($oExcel) 

Share this post


Link to post
Share on other sites



#2 ·  Posted

Here you will find an idea how to checkout/modify/checkin a Excel workbook stored in SharePoint.

 


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

#3 ·  Posted

Ok, forgive me, I have a VB macro that already does this.  I just cant use the macro to run hourly on a shared computer, which is why I'm looking at AutoIt.  Using a macro, I don't need the check in/check out anything.  Here is my macro that works...  How do I put the two together?

 

Private Sub Auto_Open()
'
RefreshMyDocs

Application.Quit

'
End Sub

Private Sub RefreshMyDocs()
    RefreshDoc "myfile.xlsx"
End Sub
Private Sub RefreshDoc(AbsolutePathToFile As String)
    Dim W As Workbook
    Set W = Workbooks.Open(AbsolutePathToFile)
    EnableCalculation = True
    W.RefreshAll
Application.DisplayAlerts = False
W.SaveAs "myfile.xlsx", AccessMode:=xlExclusive, ConflictResolution:=Excel.XlSaveConflictResolution.xlLocalSessionChanges
W.Close
End Sub

Share this post


Link to post
Share on other sites

#4 ·  Posted

As I understand it this macro is run by Excel when you open the Workbook. It Needs to be part ot the Excel file.
https://bettersolutions.com/vba/events/auto-open.htm

The AutoIt script is run as a separate program.

I fear you can only combine this two parts by translating your AutoIt script to VBA and add it to the Workbook. Then call the function from Auto_Open.


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

#5 ·  Posted

I cant use the VB in my spreadsheet do to the shared computer it needs ran on.  There is already a macro called the same thing being ran on it so it will not work, which is why I thought AutoIt would be the best way to go.  The link above sent me to a page with VB code, which is why i thought you were talking about macros.  I can't use a macro for what i am trying to do...

Share this post


Link to post
Share on other sites

#6 ·  Posted

I see.
Translating the VBA macro to AutoIt isn't a big task.
Will post an example as soon as I find some spare time.

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

#7 ·  Posted

Could be something like this:

#include <Excel.au3>

Local $sFullPath = "filenamefullpath"
Local $xlExclusive = 3 ; Exklusive mode
Local $xlLocalSessionChanges = 2 ; The local user's changes are always accepted

Local $oExcel = _Excel_Open()
Local $oWorkbook = _Excel_BookOpen($oExcel, $sFullPath)

$oWorkbook.Activesheet.EnableCalculation = True
$oWorkbook.RefreshAll
$oExcel.DisplayAlerts = False
$oWorkbook.SaveAs("myfile.xlsx", Default, Default, Default, Default, Default, $xlExclusive, $xlLocalSessionChanges)

_Excel_BookClose($oWorkbook, True)
_Excel_Close($oExcel)

 

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

That works great on my computer, running Windows 10, does not work on my coworkers computer running Windows 7.  Trying to run it on his computer will not refresh.  It will open, save and close the file, but just the refresh does not work.  Any ideas why?  This should be compatible with 7, correct?

Share this post


Link to post
Share on other sites

Since you're working in Excel, not with Windows, isn't the most likely consideration the application not the OS? Is he running the exact same version of Office as you are?


√-1 2^3 ∑ π, and it was delicious!

Share this post


Link to post
Share on other sites

#10 ·  Posted (edited)

Which version of Office is he running on the Windows 7 machine?
I was too slow ;)

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

LOL, he has 2013, I am using 2016.

Share this post


Link to post
Share on other sites

The RefreshAll method is available in Excel 2013 as well.
You need to add a COM error handler so we know where the problem arises and all available error information.
Please check ObjEvent in the help file for more details.


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