Opening an Excel sheet from Sharepoint with AutoIt

[cwoolsey]

I am trying to open one of my Server backup log Excel Sheets from my AutoIt script, which will then be transferring data from an email attachment I get to update and automate this backup log process. Everything works fine until I get to opening the Excel sheet from SharePoint which is where is just doesn't work. Any thoughts?

expandcollapse popup

#include <Date.au3>
#include <Excel.au3>
;HDQ AVAMAR

;First open Excel
$HDQExcelInstance = _Excel_Open()

;Obtain the filepath name
$Date = _NowDate()
$DateArray = StringSplit($Date, '/')
$DateMon = $DateArray[1]
$DateDay = $DateArray[2]
$DateYear = $DateArray[3]
$Date = $DateMon & '_' & $DateDay & '_' & $DateYear
$Filepath = "P:\IT_Interns\Avamar Automation\Avamar Backup Attachments\" & $Date & "\HDQ\Activities - Exceptions.*"

;Search for the right file to open

;Open the workbook within Excel
$HDQExcel = _Excel_BookOpen($HDQExcelInstance, $Filepath)

$HDQExceptionsStartDateCol = 'R'
$HDQExceptionsClientNameCol = 'C'
$HDQExceptionsCodeCol = 'E'
$i = 1
$HDQExceptionsData = "Something"

While (Not($HDQExceptionsData = ""))
    $i = $i + 1
    $HDQExceptionsStartDateLoc = $HDQExceptionsStartDateCol & $i
    $HDQExceptionsData = _Excel_RangeRead($HDQExcel, Default, $HDQExceptionsStartDateLoc)
    $Year = StringLeft($HDQExceptionsData, 4)
    $HDQExceptionsData = StringTrimLeft($HDQExceptionsData, 4)
    $Month = StringLeft($HDQExceptionsData, 2)
    $HDQExceptionsData = StringTrimLeft($HDQExceptionsData, 2)
    $Day = StringLeft($HDQExceptionsData, 2)

    ; If the month has 2 characters AND the first character is 0, like 09 for the 9th month, remove the 0
    If ((StringLen($Month) = 2) And (StringLeft($Month, 1) = 0)) Then $Month = StringTrimLeft($Month, 1)

    ; If the day has 2 characters AND the first character is 0, like 08 for the 8th day of the month, remove the 0
    If ((StringLen($Day) = 2) And (StringLeft($Day, 1) = 0)) Then $Day = StringTrimLeft($Day, 1)

    ; Create the correctly formatted Date
    $Date = $Month & "/" & $Day & "/" & $Year

    $ClientName = _Excel_RangeRead($HDQExcel, Default, $HDQExceptionsClientNameCol & $i)

    $ErrorCode = _Excel_RangeRead($HDQExcel, Default, $HDQExceptionsCodeCol & $i)

    $AVAMARExcelInstance = _Excel_Open()

    $AVAMARExcel = _Excel_BookOpen($AVAMARExcelInstance, "http://sharepoint/SiteDirectory/InformationServices/SystemsTeam/Shared Documents/Systems Administration/HDQBackupLog.xlsx")


WEnd

 

[erik7426]

The problem is in you $Filepath variable. You need to specify the file extension.

So, this...

$Filepath = "P:\IT_Interns\Avamar Automation\Avamar Backup Attachments\" & $Date & "\HDQ\Activities - Exceptions.*"

Should look something like this...

$Filepath = "P:\IT_Interns\Avamar Automation\Avamar Backup Attachments\" & $Date & "\HDQ\Activities - Exceptions.xlsx"

 

[cwoolsey]

That isn't where the problem occurs, if I take out the last 2 lines of code, the program works just fine with the .* wildcard. The name of the file isn't Activites - Exceptions.xlsx, the file changes everyday when a new email is sent with a new attachment. The actual name of the file for today is: Activities - Exceptions.20150904.130002utc.csv

And this name changes everyday.

The problem is opening the file from SharePoint (the last 2 lines of code).

If I create the following script, it will still not open:

#include <Excel.au3>
$AVAMARExcelInstance = _Excel_Open()

$AVAMARExcel = _Excel_BookOpen($AVAMARExcelInstance, "http://sharepoint/SiteDirectory/InformationServices/SystemsTeam/Shared%20Documents/Systems%20Administration/HDQBackupLog.xlsx")

 

[cwoolsey]

Update: I have also tried to simply use the Run function with the filepath to open the file and it still won't open.

[erik7426]

Is this where the file is actually located?

$Filepath = "P:\IT_Interns\Avamar Automation\Avamar Backup Attachments\" & $Date & "\HDQ\Activities - Exceptions.*"

 

[water]

What is the value of @error and @extended after calling _Excel_BookOpen?

[cwoolsey]

Erik, that line is NOT the issue. The issue is with the last two lines of the original code. The SharePoint Filepath is the problem not the local P: drive filepath.

The value of Error is 2 showing that the SharePoint filepath does not exist but I know for a fact that it does because I can run it in IE and it opens just fine, or I can open it from within Excel manually.

[water]

I remember that there was a similar discussion lately. Unfortunately I can't find the thread at the moment.
Will post the link as soon as I find something.

[sudeepjd]

I know this is an old post and I was having the same problem recently and came across this thread when searching for a solution. The problem is that in the _Excel_BookOpen() function there is a FileExists() check. This check does not work if you are using a http:// filepath, which you are using since the file is on the Sharepoint server.

To get around this you need to use the UNC filepath for that folder. To get this open the site in the Windows Explorer browser. Or from Sharepoint in the menu go to Open in File Explorer. Right click and go to Properties. Under location copy the location string. It should be something like

 \\sharepoint\SiteDirectory\InformationServices\SystemsTeam\Shared%20Documents\Systems%20Administration

with this folder as a path, you should be able to open the Excel file since using this UNC path FileExists works and consequently _Excel_BookOpen should also work.

I know I am late on this post. But I hope it will help someone else out there looking for the same solution.