I have both Excel 2002 and 2007 on my machine. ExportToXl automatically goes to 2002 version. Will 2007 support it and if so how to I change the settings so that in exports to 2007?
Thanks
paul
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
Paul, I suspect you have to change the setTemplate.sas code to use either xlsx or xlsm file suffix. I think the best way is to add a parameter, e.g xl_type and make the relevant changes for ddecmd variable concatenations.
I have a need to use this feature too. I may give it a go and see if I have any success
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
Yes I have. I use it all the time. I used it with 2003 and now have it working to open 2010 excel but still saves it as an xls. Haven't made those changes yet.
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
I started doing it - I was planning on making the file suffix a parameter but ran out of time. I found that the changes required are more widespread than just setTemplate.
The standard ExportToExcel still works fine in later versions of Excel as noted by paulsparrow but only in 2003 xls file fomat… because that is what is used in the code as a static file suffix.
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
I had this program working on Excel 2003. Upgraded machine and now using 2010 and 2007 and yes I get the same error. I have also tried going through all the macros replacing xls with xlsx and can't get that to work. Maybe one day Nate will get a chance to sit down and re-do it. Great program when it worked.
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
My colleague removed the '.xls' file suffix altoghther in the .sas code files. That way you can simply use the entire excel file name in the tmplname or savename parameters. It works a treat.
The sas files updated are:
setTemplate
loadNames
inputData
closeDDE
checkParms
setVariables
I'll ask him if I can distribute the modded files - it will be all care no responsibility (as per usual!)
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
I have both Excel 2002 and 2007 on my machine. ExportToXl automatically goes to 2002 version. Will 2007 support it and if so how to I change the settings so that in exports to 2007?
Thanks
paul
Paul, I suspect you have to change the setTemplate.sas code to use either xlsx or xlsm file suffix. I think the best way is to add a parameter, e.g xl_type and make the relevant changes for ddecmd variable concatenations.
I have a need to use this feature too. I may give it a go and see if I have any success
Has anyone gotten this to work? I am having trouble getting this to work.
Yes I have. I use it all the time. I used it with 2003 and now have it working to open 2010 excel but still saves it as an xls. Haven't made those changes yet.
I started doing it - I was planning on making the file suffix a parameter but ran out of time. I found that the changes required are more widespread than just setTemplate.
The standard ExportToExcel still works fine in later versions of Excel as noted by paulsparrow but only in 2003 xls file fomat… because that is what is used in the code as a static file suffix.
I had this program working on Excel 2003. Upgraded machine and now using 2010 and 2007 and yes I get the same error. I have also tried going through all the macros replacing xls with xlsx and can't get that to work. Maybe one day Nate will get a chance to sit down and re-do it. Great program when it worked.
My colleague removed the '.xls' file suffix altoghther in the .sas code files. That way you can simply use the entire excel file name in the tmplname or savename parameters. It works a treat.
The sas files updated are:
setTemplate
loadNames
inputData
closeDDE
checkParms
setVariables
I'll ask him if I can distribute the modded files - it will be all care no responsibility (as per usual!)