Excel Enable Calculations from Read Only?

[Spring]

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) 

[water]

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

 

[Spring]

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

[water]

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.

[Spring]

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

[water]

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.

[water]

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)

 

[Spring]

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?

[JLogan3o13]

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?

[water]

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

Edited September 14, 2017 by water

[Spring]

LOL, he has 2013, I am using 2016.

[water]

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.