The following function is designed to loop through all workbooks in the active folder and copy all columns of the first worksheet of each workbook into columns of a single worksheet of a newly created workbook.
On Success ... returns the name of the new workbook created.
On Failure ... returns an appropriate error message.
Hopefully this will work for you ...
void MergeWorkbookData()
{
WorksheetPage wP;
if(wP.Create("origin", CREATE_HIDDEN)==false)
{
out_str("Workbook Create Fail ...");
goto ErrorExit;
}
string strWbkName=wP.GetName();
Worksheet Wks(wP.Layers(0));
if(Wks.IsValid()==false)
{
out_str("Worksheet Validation Fail ...");
goto ErrorExit;
}
// Delete all columns in the Worksheet Wks ..
for(int i=(Wks.GetNumCols()-1);i>=0;i--)
{
Wks.DeleteCol(i);
}
Folder Fld=Project.ActiveFolder();
foreach(PageBase pB in Fld.Pages)
{
if(pB.GetType()!=EXIST_WKS)
{
continue;
}
if(strWbkName.CompareNoCase(pB.GetName())!=0)
{
Page pG(pB);
if(pG.IsValid()==false)
{
out_str("Workbook Validation Fail ...");
goto ErrorExit;
}
Worksheet Wk1=pG.Layers(0);
if(Wk1.IsValid()==false)
{
out_str("Worksheet Validation Fail ...");
}
for(int j=0;j<Wk1.GetNumCols();j++)
{
int ColIndex=Wks.AddCol();
if(ColIndex==-1)
{
out_str("Worksheet AddCol function fail ...");
goto ErrorExit;
}
if(Wk1.CopyTo(Wks, j, j, 0, Wk1.GetNumRows(), ColIndex, -1)!=0)
{
out_str("Worksheet CopyTo function fail ...");
goto ErrorExit;
}
}
}
}
out_str("Job Done ... Data Collated into Workbook "+strWbkName);
return;
ErrorExit:
if(wP.IsValid()==true)
{
wP.Destroy();
}
}
Need Help, merging a lot of workbooks
Posted - 07/15/2013 : 12:07:49 AM
