CODEWITHSUNDEEP
excelvbscriptms-word
Cooper Nakoolak
Cooper Nakoolak

Reputation: 1

Converting word documents to excel with VBscript

I'm building a tool in VBscript that would convert all of the word documents in a folder into excel documents. I need to copy everything and keep the source formatting. This works manually by ctrl-a the word document and paste to the excel document. The main problem is with ActiveDocument (i'm unsure if it's activating my word document) and i'm unsure how to do the paste to the excel document.

Here is my code so far. It makes new excel files with the same name as the word documents, but it doesn't copy the contents and paste.

set shApp = CreateObject("shell.application")
currentPath = CreateObject("Scripting.FileSystemObject").GetAbsolutePathName(".") 
set shFolder = shApp.NameSpace( currentPath )
set files = shFolder.Items()



for each files in files
    if files.type = "Microsoft Word 97 - 2003 Document" OR files.type = "Microsoft Word Document" then
      msgbox("Converting: "&files.path)
      Call DocConvert() 'function call to function that converts .doc to .xls

end if
next


'Opens a word document copies the contents and pastes it into an excel document of the same name
Function DocConvert()

 On Error Resume Next

 Set objWord = GetObject(, "Word.Application")
        If Err <> 0 Then
            Set objWord = CreateObject("Word.Application")
        End If

 objWord.Visible = True
 objWord.Documents.Open(files.path)

 ActiveDocument.Range(0, 0).Select
 Selection.WholeStory
 Selection.Copy


 Set objExcel = CreateObject("Excel.Application")
 objExcel.Visible = True
 Set objWorkbook = objExcel.Workbooks.Add()

 ExcelSave = replace(files.path,"doc","xls")

 objWorkbook.SaveAs(ExcelSave)

 objWord.Quit
 objExcel.Quit

End Function

Upvotes: 0

Views: 1339

Answers (1)

Denis
Denis

Reputation: 1555

It looks like you don't really understand what you are doing...

Selection.Copy
Set objExcel = CreateObject("Excel.Application")
objExcel.Visible = True
Set objWorkbook = objExcel.Workbooks.Add()
ExcelSave = replace(files.path,"doc","xls")
objWorkbook.SaveAs(ExcelSave)

You make a copy of a selection and you don't paste it anywhere? How Excel is supposed to know where to put it? You need to Select a Cell and Paste what you have just copied. Of course you need to do this before you Quit the Excel and before you Save.

Upvotes: 0

Related Questions