Topic
  • 2 replies
  • Latest Post - ‏2012-11-25T19:59:50Z by SystemAdmin
SystemAdmin
SystemAdmin
3180 Posts

Pinned topic Open Excel template as a copy

‏2012-11-22T20:36:12Z |

Hi,

I got an Excel template with some predefined sheets stored on a server. The DXL macro is collection data and fills the template for the user. The problem is how to open a workbook based on a template in Excel. Using the DXL Open method below will open the actual file and result in user overwriting the template if he/she hits the "Save" button. I want to open the template as a copy, forcing the user to use the "SaveAs" button and leave the template untouched.

Open method in DXL:

bool openWorkBook(string filename) {
   if(!null(objWorkbooks)) {
      clear(args);
      put(args, cParamFileName, filename);
      
      if(!checkResult(oleMethod(objWorkbooks, cExcelMethodOpen, args))) {      // opens the file
         errorBox("Failed to open file");
         return(false);
      }
      
      fileOpen = true;
      return true;
   }
   
   errorBox("Workbooks collection not initialized.  Check to make sure your code gets it.");
   return(false);
}

 


In VBA you can solve this by the following code:

 

 

Private objExcel As Excel.Application
Set objExcel = CreateObject("Excel.Application")
objExcel.Workbooks.Add (filename)



I tried to use oleMethod with "Add" and filename as parameter but that didn't work.



 

 

bool addWorkBookTemplate(string filename) {
   if(!null(objWorkbooks)) {
      clear(args);
      put(args, cParamFileName, filename);
      
      if(!checkResult(oleMethod(objWorkbooks, cMethodAdd, args))) {
         errorBox("Add workbook template failed.");
         return(false);
      }
 
      fileOpen = true;
      return(true);
   }
   errorBox("Workbooks collection not initialized.  Check to make sure your code gets it.");
   return(false);
}



Is there a way to add an Excel template to a workbook or is the solution to copy the worksheets from my template to a new workbook?

/Mathias



 

Updated on 2014-01-06T14:43:33Z at 2014-01-06T14:43:33Z by iron-man
  • adevicq
    adevicq
    154 Posts

    Re: Open Excel template as a copy

    ‏2012-11-23T09:17:43Z  
    Hi,
    Just replace cParamFileName by the string "Template" in your arguments.
    Regards,
    Alain
  • SystemAdmin
    SystemAdmin
    3180 Posts

    Re: Open Excel template as a copy

    ‏2012-11-25T19:59:50Z  
    • adevicq
    • ‏2012-11-23T09:17:43Z
    Hi,
    Just replace cParamFileName by the string "Template" in your arguments.
    Regards,
    Alain

    Ahh.. so simple, thank you so much!

    The modified function:
     

    bool addWorkBookTemplate(string filename) {
       if(!null(objWorkbooks)) {
          clear(args);
          put(args, "Template", filename);
          
          if(!checkResult(oleMethod(objWorkbooks, cMethodAdd, args))) {
             errorBox("Add workbook template failed.");
             return(false);
          }
     
          fileOpen = true;
          return(true);
       }
       errorBox("Workbooks collection not initialized.  Check to make sure your code gets it.");
       return(false);
    }
    

     


    Regards
    /Mathias

     

    Updated on 2014-01-06T14:44:03Z at 2014-01-06T14:44:03Z by iron-man