
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
load 2 excel sheets from Sharepoint
hi ,
i want to load data from 2 excel sheets names below.
1.2014 US REMS Audit Workbook DOL
2.2015 US REMS Audit Workbook DOL
Tab Names:
2014 Audit stats Tracking
2015 Audit stats Tracking
local its working fine but it fails to load from sharepoint path
sharepoint path:http://xxxx.com/documents......
sub ScanFolder(Root)
for each FileExtension in 'xlsx'
for each FoundFile in filelist( Root & '\*.' & FileExtension)
Let vFileName = subfield('$(FoundFile)','\',SubStringCount('$(FoundFile)','\')+1);
if (Wildmatch(vFileName, '*US REMS Audit Workbook DOL*')) then
Let vYear = Left(vFileName,4);
Let vSheetName = vYear & ' Audit stats Tracking';
Audit_Stats_Tracking:
LOAD
[Audit Date],
[Pharmacy ID],
[Pharmacy Name],
Address,
City,
State,
PharmacyTYpe as [Pharmacy Type],
LeadAuditor as [Lead Auditor],
AuditType as [Audit Type],
AccountManagerName as [Account Manager Name],
MAAccountDirector as [MA Account Director],
[Product Audited],
[Overall Audit Result],
[Observation Yes or No],
[Repeat Observations],
[Tier 1],
[Tier 2],
[Number of Counselors],
[Date Audit Report Issued],
[Date Response Received],
//[Date Certificate sent],
[Effectiveness Check],
Comments,
[Observation Rating],
subfield('$(FoundFile)','\',SubStringCount('$(FoundFile)','\')+1) as FileName
FROM [$(FoundFile)] (ooxml, embedded labels, table is '$(vSheetName)');
End If
next FoundFile
next FileExtension
end sub
Call ScanFolder('$(vFilePath)') ;
Accepted Solutions

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Hi tamil Nagraj,Marcus Summer,
Thanks for Your Time.
As far as I know, the http and https protocols do not support file iteration using wildcard loads or FileList() loops. For loading data with ese protocols you will need to explicitly name the files.-By Jonathan dienst.
so i made some code change to read the Correct file from Share point
Here is the Code i modified.
set vFilePath ='http://teamsites..com/sites/Documents';
sub ScanFolder(Root)
for Vcurrent= 2014 to Year(today())+1
for each FileExtension in 'xlsx'
for each FoundFile in vFilePath&'\'&$(Vcurrent)&' US REMS Audit Workbook DOL'&'.'& FileExtension
Let vFileName = subfield('$(FoundFile)','\',SubStringCount('$(FoundFile)','\')+1);
if (Wildmatch(vFileName, '*US REMS Audit Workbook DOL*')) then
Let vYear = Left(vFileName,4);
Let vSheetName = vYear & ' Audit stats Tracking';
Audit_Stats_Tracking:
LOAD
[Audit Date],
[Pharmacy ID],
[Pharmacy Name],
Address,
City,
State,
PharmacyTYpe as [Pharmacy Type],
LeadAuditor as [Lead Auditor],
AuditType as [Audit Type],
AccountManagerName as [Account Manager Name],
MAAccountDirector as [MA Account Director],
[Product Audited],
[Overall Audit Result],
[Observation Yes or No],
[Repeat Observations],
[Tier 1],
[Tier 2],
[Number of Counselors],
[Date Audit Report Issued],
[Date Response Received],
//[Date Certificate sent],
[Effectiveness Check],
Comments,
[Observation Rating],
subfield('$(FoundFile)','\',SubStringCount('$(FoundFile)','\')+1) as FileName
FROM [$(FoundFile)] (ooxml, embedded labels, table is '$(vSheetName)');
End If
next FoundFile
next FileExtension
next Vcurrent
end sub
Call ScanFolder('$(vFilePath)') ;
its worked fine and iam able to read from Share point
Thanks,
Are.Babu


- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Have a look here Loading multiple Excel files from SharePoint on the answer from jontydkpi
- Marcus

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Hi Are Babu,
Change the Call ScanFolder line like below and try to reload the script.
Call ScanFolder('htttp://sharepointlink.com/Documents') ;
Let me know.

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Hi tamil Nagraj,Marcus Summer,
Thanks for Your Time.
As far as I know, the http and https protocols do not support file iteration using wildcard loads or FileList() loops. For loading data with ese protocols you will need to explicitly name the files.-By Jonathan dienst.
so i made some code change to read the Correct file from Share point
Here is the Code i modified.
set vFilePath ='http://teamsites..com/sites/Documents';
sub ScanFolder(Root)
for Vcurrent= 2014 to Year(today())+1
for each FileExtension in 'xlsx'
for each FoundFile in vFilePath&'\'&$(Vcurrent)&' US REMS Audit Workbook DOL'&'.'& FileExtension
Let vFileName = subfield('$(FoundFile)','\',SubStringCount('$(FoundFile)','\')+1);
if (Wildmatch(vFileName, '*US REMS Audit Workbook DOL*')) then
Let vYear = Left(vFileName,4);
Let vSheetName = vYear & ' Audit stats Tracking';
Audit_Stats_Tracking:
LOAD
[Audit Date],
[Pharmacy ID],
[Pharmacy Name],
Address,
City,
State,
PharmacyTYpe as [Pharmacy Type],
LeadAuditor as [Lead Auditor],
AuditType as [Audit Type],
AccountManagerName as [Account Manager Name],
MAAccountDirector as [MA Account Director],
[Product Audited],
[Overall Audit Result],
[Observation Yes or No],
[Repeat Observations],
[Tier 1],
[Tier 2],
[Number of Counselors],
[Date Audit Report Issued],
[Date Response Received],
//[Date Certificate sent],
[Effectiveness Check],
Comments,
[Observation Rating],
subfield('$(FoundFile)','\',SubStringCount('$(FoundFile)','\')+1) as FileName
FROM [$(FoundFile)] (ooxml, embedded labels, table is '$(vSheetName)');
End If
next FoundFile
next FileExtension
next Vcurrent
end sub
Call ScanFolder('$(vFilePath)') ;
its worked fine and iam able to read from Share point
Thanks,
Are.Babu
