VBA Export to Word - as Unformatted Text

[PinkUnicorn]

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]

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]

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]

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]

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]

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]

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]

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

 

[NdNoviceHlp]

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]

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