CODEWITHSUNDEEP
excelvbams-wordformatpercentage
MightyMouseZ
MightyMouseZ

Reputation: 79

How to read the formatted percentage from a cell with VBA?

I am taking data from an Excel spreadsheet and inserting it into a Word document. Here is the code I am using which works as intended.

Private Sub insertData(wb As Excel.Workbook)
    Dim numBM As Integer
    Dim countBM As Integer
    Dim currentBM As String

    numBM = ActiveDocument.Bookmarks.Count

    For countBM = 1 To numBM
        currentBM = ActiveDocument.Bookmarks(countBM).Name
        ActiveDocument.Bookmarks(currentBM).Range.Text = wb.Names(currentBM).RefersToRange.Value2
    Next
End Sub

In Excel, I have some cells that are percentage type formatting. So the value of the cell is 0.857394723 but the cell displays "86%". How can I change my code so that "86%" is inserted into Word instead of "0.857394723"

Upvotes: 0

Views: 2551

Answers (2)

MightyMouseZ
MightyMouseZ

Reputation: 79

I actually figured out the answer to this, which is extremely simple. Instead of using ...RefersToRange.Value I can use ...RefersToRange.Text so the line becomes

ActiveDocument.Bookmarks(currentBM).Range.Text = wb.Names(currentBM).RefersToRange.Text

Upvotes: 1

Mathieu Guindon
Mathieu Guindon

Reputation: 71177

Format the value accordingly, using the Strings.Format method from the VBA standard library:

Dim formattedValue As String
formattedValue = Format(wb.Names(currentBM).RefersToRange.Value2,"0%")

ActiveDocument.Bookmarks(currentBM).Range.Text = formattedValue

Upvotes: 1

Related Questions