Importing an Excel workbook and retaining all formatting with DDE?

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 9
Accepted Solution

Importing an Excel workbook and retaining all formatting with DDE?

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!


Accepted Solutions
Solution
‎05-07-2013 06:55 PM
Super User
Posts: 19,789

Re: Importing an Excel workbook and retaining all formatting with DDE?

Posted in reply to DavidBess

See this discussion.

Essentially, you can use SAS X commands to copy the file over.

View solution in original post


All Replies
Respected Advisor
Posts: 3,799

Re: Importing an Excel workbook and retaining all formatting with DDE?

Posted in reply to DavidBess

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.

Occasional Contributor
Posts: 9

Re: Importing an Excel workbook and retaining all formatting with DDE?

Posted in reply to data_null__

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?

Thanks Again! 

Super User
Posts: 11,343

Re: Importing an Excel workbook and retaining all formatting with DDE?

Posted in reply to DavidBess

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.

Solution
‎05-07-2013 06:55 PM
Super User
Posts: 19,789

Re: Importing an Excel workbook and retaining all formatting with DDE?

Posted in reply to DavidBess

See this discussion.

Essentially, you can use SAS X commands to copy the file over.

🔒 This topic is solved and locked.

Need further help from the community? Please ask a new question.

Discussion stats
  • 4 replies
  • 267 views
  • 2 likes
  • 4 in conversation