05-07-2013 11:43 AM
I have an excel workbook with many sheets, weird formatting, pivot tables, and macros. What I do as part of a quarterly report is copy the workbook from one directory and paste it to another directory on another server. I have to do this for many workbooks and it takes forever.
I would very much like to automate this procedure in a SAS base program and implement this as part of a SAS EG stored process.
I know the basics of VBA, and DDE, I have some experience working with the Excel XP tagset.
Does anyone know if there is a way to import a multi-sheet workbook while retaining all of its weird formatting and tables and then turn around and export it again. Or to use SAS to copy paste the file without even importing it into SAS and having to worry about how SAS handles the data?
Thanks in advance!
05-07-2013 12:11 PM
If that's possible I'd like to see it. There are so many possibilites for "weird formatting".
Seems like the only reason to read data from EXCEL workbook is to process the values with SAS. If you're not doing that then leave it be.
When you say copy and paste takes forever can you give more detail about how that is being done.
05-07-2013 12:24 PM
It is true, I do feel like I am underutilizing SAS by trying to use it to simply copy paste files without any SAS processing. My motivation is that this is one step of a much larger quarterly project which does use a lot of SAS processing. I would love to have a the copy paste step be part of a stored process in SAS EG so that I can run the entire project in batch mode.
Current state; I literally sit here for half of a day digging through directories and copy pasting excel files. The actual data movement does not take very long. It is just a lot of files deeply embedded in different directories on different servers. Data delivery is fairly consistent, so I would love to be able to automate this process.
Another option I just thought of would be to write some code in another programming language which moves the files, and then have some SAS code where SAS initiates and executes the other code. I have seen something like this before, but I cannot find the white paper, does anyone know of a white paper on SAS initiating another outside program to run?
05-07-2013 06:45 PM
If you know how to write the syntax for your OS to move files you can either write a batch file to move and run that with the X command or run directy with the X command. You probably want the NOXWAIT system option as well.
Or Call System or %SYSEXEC might be helpful depending on your approach and operating system.
Need further help from the community? Please ask a new question.