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]

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

 

[PinkUnicorn]

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]

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]

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]

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]

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]

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]

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]

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.