How to pull data as numbers from Word to Excel using VBA?-CodePudding

I'm trying to pull data from tables in a Word document to Excel. I'm able to pull it as text but I don't know how to pull the numbers as numbers.

Sub extractData()

    Dim wd As New Word.Application
    Dim doc As Word.Document
    Dim sh As Worksheet

    wd.Visible = True

    Set doc = wd.Documents.Open(ActiveWorkbook.Path & "C:\Users\itays\Desktop\TTd.docx")
    Set tbl = doc.Tables
    Set sh = ActiveSheet

    For i = 1 To 17
        sh.Cells(i, 1).Value = tbl(5).Rows(i).Cells(1).Range.Text

    Next
    For i = 1 To 17
        sh.Cells(i, 2).Value = tbl(5).Rows(i).Cells(2).Range.Text
    Next

    Range("a:e").Columns.AutoFit

    doc.Close

End Sub

Basically, I need the second For command to pull the data as a number and not as a text.

CodePudding user response：

Word handles text, not numbers. You have to make sure that the text in the second column comes out as a number by converting it to the correct data type. First you have to strip out the text you cannot convert, like linebreaks and table formatting. There are several ways to do this, the following is my example. Trim removes whitespace, Val keeps just the digits, CLng converts it to a Long.

sh.Cells(i, 2).Value = CLng(Val(Trim(tbl(1).Rows(i).Cells(2).Range.Text)))

By the way, the path when you open the Word document looks really weird?

EDIT

You need to clean the data before converting. Adding a Replace-command to change the commas to periods, then convert to a Double instead of a Long to handle the decimal value with CDbl:

sh.Cells(i, 2).Value = CDbl(Val(Trim(Replace(tbl(1).Rows(i).Cells(2).Range.Text, ",", "."))))

CodePudding user response：

Try:

For i = 1 To 17
    sh.Cells(i, 1).Value = Split(tbl(5).Cell(i, 1).Range.Text, vbCr)(0)
    sh.Cells(i, 2).Value = Split(tbl(5).Cell(i, 2).Range.Text, vbCr)(0)
Next