0

I have an issue regarding vba. I`m trying to copy the text content from word documents into an excel sheet to a specific cell. Now, I have no trouble copying the content as a single string, but I would like to keep formatting. I have tried using the following:

ActiveSheet.PasteSpecial Format:="Microsoft Word 8.0 Document Object"

But sadly it only copies an image of the document (albeit in the right cell).

Is there a way to do this using vba? There are hundreds of documents, therefore doing it by hand is not feasible.

Alternatively, If this is not possible, I would also be happy, if the documents are pasted as html with indications for line breaks and spacing.

Thank you for your help!

Edit 28/7, 13:18:

'Create an instance of the FileSystemObject
Set objFSO = CreateObject("Scripting.FileSystemObject")
'Get the folder object
Set objFolder = objFSO.GetFolder("C:\afloder")
i = 10

'loop through each file in the directory and prints their names
For Each objFile In objFolder.Files
    'print file name
    temp = CStr(objFile.Name)
    splittedTemp = Split(temp, ".")
    Cells(i + 1, 1) = splittedTemp(0)

    'paste in the content
    Doc = CStr("C:\afolder\" & objFile.Name)
    Set WordDoc = Word.Documents.Open(Doc)
    Word.ActiveDocument.Content.Copy

    'Call the paste procedure
    Worksheets("Communication Template").Cells(i + 1, 9).Select
    DoEvents
    ActiveSheet.PasteSpecial Format:="Microsoft Word 8.0 Document Object", DisplayAsIcon:=False

    WordDoc.Close savechanges:=False
    Set WordDoc = Nothing

    i = i + 1
Next objFile

Set WordApp = Nothing
Bruno Bieri
  • 9,724
  • 11
  • 63
  • 92
igma
  • 15
  • 7
  • Can you post rest of your code? that 1 line will probably make more sense if we can see the rest of the code – Zac Jul 28 '17 at 10:33
  • I have posted the rest for this (not the string copy paste). Hope it helps ! – igma Jul 28 '17 at 11:25
  • There are several answers to this question on StackOverflow: [this one](https://stackoverflow.com/a/12248308/4717755) from @SiddartthRout is comprehensive. – PeterT Jul 28 '17 at 14:40
  • Hey @PeterT thank you for your comment! I have seen and read this before I posted, however it does not seem to deal with my issue, as it is copying tables and not textual content and formatting of the word document. As I said, I can copy the content of the document, but I need to either: A, Keep the formatting (line breaks, spacing, etc) of the document, or B, Copy is as html where linebreaks, spacing, etc are marked. – igma Aug 02 '17 at 12:36

0 Answers0