# VBA Export to Word - as Unformatted Text



## PinkUnicorn (Jan 10, 2018)

Hi, I wonder if anyone could help me please?

I've written the following to export the data in a spreadsheet to Word, and this works fine (albeit slowly!).  However, it pastes into Word in a table form and because I then need to copy the Word information into a log in a separate system I need this to be in an unformatted text format.

Basically the excel document is being used to assess the risk of something, and I need the details of the risk assessment and the outcome to be able to be pasted into a log.  We can't do this directly from excel as the cells will not paste into the log without copying and pasting each one individually.  So the workaround I was looking at was exporting from excel into Word in an unformatted format, then copy that text and paste into the log.  Long winded, I know, but the best I can come up with.

Alternatively we could export to a text file which might be easier?

So - the code I am using is below and if anyone can tell me where I am going wrong that would be so fantastic (I have already spent hours trying to work this out myself and have now admitted that I really have no idea what I am doing!!)  I have tried to change "newObj.Range.Paste" to "newObj.Range.PasteSpecial Unformatted Text" but that just gets to opening Word and not going any further (so no paste taking place).

Happy to hear any alternative suggestions if there are thoughts on a better workaround to my idea too.

Thanks so much

Sub export_excel_to_word()
    Set obj = CreateObject("Word.Application")
    obj.Visible = True
    Set newObj = obj.Documents.Add
    ActiveSheet.UsedRange.Copy
    newObj.Range.Paste
    Application.CutCopyMode = False
    obj.Activate
    newObj.SaveAs FileName:=Application.ActiveWorkbook.Path & "" & ActiveSheet.Name
End Sub


----------



## NdNoviceHlp (Jan 10, 2018)

This might help... http://www.vbaexpress.com/forum/showthread.php?17784-Make-Word-table-from-XL&highlight=table 
Dave


----------



## PinkUnicorn (Jan 10, 2018)

Thank you.  However, that thread is about inserting the tables and I want to paste the text unformatted and with no tables etc.  Any thoughts please?


----------



## NdNoviceHlp (Jan 10, 2018)

I don't understand. Is your data in an XL table? Is it pasting to Word as a table? Is it pasting as a picture that U can't access? "I need the details of the risk assessment and the outcome to be able to be pasted into a log. We can't do this directly from excel as the cells will not paste into the log without copying and pasting each one individually."????
What type of log is it and why do U need to individually paste them instead of directly coding where they go? What's wrong with individually pasting them.. with code it can't take that long? A bit more info please. I'm guessing Macropod will be along to provide assistance. The more info provided the better. Dave


----------



## PinkUnicorn (Jan 10, 2018)

Thanks for the reply.

So the original data is in excel - it is a risk assessment with various formulas in the background to determine the level of risk depending on the outcome of the answers.  Text is added as well as part of the completion of the risk assessment.

We then need to get the information from the risk assessment, (including the original questions, text added, and outcome of the formulas doing their stuff) into a log within in a CRM system.  There are five columns and 47 rows to the excel sheet so to copy and paste individually would take a long time.  For info this is an emergency call out system so time and accuracy are of the essence.

So what I need to be able to do is to copy the whole of the data from excel and paste it into the log.  The only way I can think of doing this is to paste into Word in an unformatted format, and then copy and paste into the log.  As I say, possibly a text file would be quicker.  But I really can't work that one out.

We don't need the word doc to be saved anywhere, just opened and pasted into so a quick Cntrl A, Cntrl C can be done and then a paste into the log in CRM.

Hope this makes a bit more sense as to what I'm aiming for?  Thanks again


----------



## NdNoviceHlp (Jan 10, 2018)

I don't know what a CRM system is? What type of log:  XL, Word, html post to website? "There are five columns and 47 rows to the excel sheet so to copy and paste individually would take a long time. " Yes it would if U were to manually copy and then paste each cell. I don't see why if U can do it manually that U can't just code to do it all at once? I don't see how pasting to Word is going to help. Won't U will still have to extract the individual cell data and send it to the log? Keep posting, somehow we'll arrive at a solution. Dave


----------



## PinkUnicorn (Jan 10, 2018)

CRM is a customer records management system.  Lots of different fields for specific information, but this one has a 'free text' area where we need to record the information from the spreadsheet.
Unfortunately, it's all pretty basic and doesn't allow for pasting except for using Ctrl V.  Therefore no option to paste as unformatted text, otherwise we could just copy the whole excel and paste in - job done (that's what I'd hoped to start with).  

So, I need to export the data into an unformatted format, which can then be copied and pasted into its final location.


----------



## PinkUnicorn (Jan 10, 2018)

I think I have solved it!

My code is below and running it takes about four seconds and gives me the unformatted text on screen in a word doc, which can then simply be copied and pasted into its proper location.

Sub export_excel_to_word()
    Set obj = CreateObject("Word.Application")
    obj.Visible = True
    Set newObj = obj.Documents.Add
    ActiveSheet.UsedRange.Copy
    newObj.Range.PasteSpecial DataType:=2
    Application.CutCopyMode = False
    obj.Activate
End Sub


----------



## NdNoviceHlp (Jan 10, 2018)

Thanks for posting your outcome. I still don't understand the need for Word. You have it in the clipboard and are able to paste it to the document in the format U want. If U then copy it in preparation to place it to your free text area, don't U have the same info in the Clipboard that U put there before? Anyways, I'm glad U sorted it out. Dave


----------



## PinkUnicorn (Jan 10, 2018)

Thanks Dave.
The reason for needing word is that to copy from excel and then only Ctrl V to paste you have no option to paste special (and therefore can't select unformatted text).  It will paste but because of the limitations of the receiving system it goes all gobbledegook with the different cells and makes little sense.  Therefore I need to be able to paste the text but not the boxes/cells/tables which I get with a straightforward paste.

Thanks again for the help.


----------



## PinkUnicorn (Jan 10, 2018)

Hi, I wonder if anyone could help me please?

I've written the following to export the data in a spreadsheet to Word, and this works fine (albeit slowly!).  However, it pastes into Word in a table form and because I then need to copy the Word information into a log in a separate system I need this to be in an unformatted text format.

Basically the excel document is being used to assess the risk of something, and I need the details of the risk assessment and the outcome to be able to be pasted into a log.  We can't do this directly from excel as the cells will not paste into the log without copying and pasting each one individually.  So the workaround I was looking at was exporting from excel into Word in an unformatted format, then copy that text and paste into the log.  Long winded, I know, but the best I can come up with.

Alternatively we could export to a text file which might be easier?

So - the code I am using is below and if anyone can tell me where I am going wrong that would be so fantastic (I have already spent hours trying to work this out myself and have now admitted that I really have no idea what I am doing!!)  I have tried to change "newObj.Range.Paste" to "newObj.Range.PasteSpecial Unformatted Text" but that just gets to opening Word and not going any further (so no paste taking place).

Happy to hear any alternative suggestions if there are thoughts on a better workaround to my idea too.

Thanks so much

Sub export_excel_to_word()
    Set obj = CreateObject("Word.Application")
    obj.Visible = True
    Set newObj = obj.Documents.Add
    ActiveSheet.UsedRange.Copy
    newObj.Range.Paste
    Application.CutCopyMode = False
    obj.Activate
    newObj.SaveAs FileName:=Application.ActiveWorkbook.Path & "" & ActiveSheet.Name
End Sub


----------



## NdNoviceHlp (Jan 10, 2018)

Ok that bites. Just for fun, trial copying the info with code after U paste it to Word before U close the document. Run the sub. Then go to your free text area and paste. It should be there in the clipboard for U to paste. If that works U won't need to make Word visible. Run the sub, go to your free text area and paste. U may need to cancel the alerts before closing the document to make it seemless. Good luck. Dave


----------



## PinkUnicorn (Jan 10, 2018)

Thats a brilliant idea.  

And it works!  Fantastic, thank you.

Is there any way I can get the code not to display the word doc at all, or do I still need to have that displayed even though I can bypass the need to copy further from the word doc?  OR can I add to the sub to open the word doc and then close it immediately without saving?

You are a genius


----------



## PinkUnicorn (Jan 10, 2018)

Correction - it doesn't work.  It pastes as formatted text, so continues with the boxes/cells/tabular format which then turns to gobbledygook in the log.  At least with the macro running it only adds a couple of key strokes and the closing of the word doc so I think we will live with that.

Thanks again


----------



## NdNoviceHlp (Jan 10, 2018)

It worked and then didn't work? If it worked once it must be possible. Just change ...

```
obj.Visible = False
```
if U don't want to see the document. I think U had some obj.activate code in there that's not necessary and perhaps counter productive. U would probable need..
Application.displayAlerts=false
before the document close and then
Application.displyalerts=true
before ending the sub to make it run without noticing it. Trial saving the Word doc before copying the data and then closing the document. Seems like this should work. Dave


----------



## Macropod (Jan 10, 2018)

There really isn't any need for all this circumlocution with Word:

```
Sub Demo()
ActiveSheet.UsedRange.Copy
Shell "Notepad", vbNormalFocus
AppActivate "Untitled - Notepad"
SendKeys "^v^a^c^z%fx"
End Sub
```
The unformatted text is now in the clipboard, ready for pasting.


----------



## PinkUnicorn (Jan 11, 2018)

Oh my - you are an absolute genius.  This is perfect.  I had hoped there was a better way than my convoluted way, but I just couldn't think of one.

Thank you so much.  Really appreciate your help.


----------



## Macropod (Jan 11, 2018)

You could probably even extend the code to automatically pasting the unformatted content into your other application...


----------



## NdNoviceHlp (Jan 12, 2018)

> I'm guessing Macropod will be along to provide assistance.


 I should have pm'd U. Thanks for your assistance. I believed there was an easier way, I just didn't know what it was. Now I do. Have a nice day. Dave


----------



## NdNoviceHlp (Jan 12, 2018)

Missed the edit timer...grrr... 
ps. PinkUnicorn; See this thread here about clearing the clipboard. U may want to clear it first before copying your stuff. I have a notion that was the problem when things worked and then didn't work. Glad U got this resolved. Dave
https://www.mrexcel.com/forum/excel-questions/1037267-excel-crashing-1-out-5-times.html
Dave


----------

