cwoolsey

Opening an Excel sheet from Sharepoint with AutoIt

8 posts in this topic

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?

#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

 

Share this post


Link to post
Share on other sites



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"

 

Share this post


Link to post
Share on other sites

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

 

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites

Is this where the file is actually located?

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

 

Share this post


Link to post
Share on other sites

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


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

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.

Share this post


Link to post
Share on other sites

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.


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