Microsoft Office Forums

Go Back   Microsoft Office Forums > Microsoft Word > Word Tables

Reply
 
LinkBack Thread Tools Display Modes
  #1  
Old 09-11-2017, 02:08 AM
Stockturn Stockturn is offline Windows 10 Office 2010 32bit
Novice
 
Join Date: Sep 2017
Location: Somerset UK
Posts: 1
Stockturn is on a distinguished road
Default Manipulating Data in Cells

Hi All,



It's more than likely that I'm trying to achieve the impossible; but here is my problem.

I have a supplier who sends me a Word Document which is a Price List with the products priced in their local currency and I need to convert this into my local currency; sounds simple on the face of it but I have several problems

Problems: -
  1. I need to retain the document format; it contains Headers/Footers, Banners and Graphics and I must not lose these.
  2. The Exchange Rate is constantly moving, albeit I do have a fixed 90 Day Rate.
  3. The document contains multiple Tables (1 for each Product).

Currently I am sitting down with a calculator and manually editing each cell, a process which takes several hours to complete. I have been scratching my head for a few days now find some easy solution to this and automate the manipulation of the cell holding the prices. I'll include a screen snip so you can see what I'm up against.

Any feedback would be cool.
Attached Images
File Type: jpg PList1.JPG (69.1 KB, 3 views)
Reply With Quote
  #2  
Old 09-11-2017, 04:45 AM
macropod's Avatar
macropod macropod is offline Windows 7 64bit Office 2010 32bit
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 16,561
macropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to behold
Default

You could do the conversions with a macro like:
Code:
Sub ConvCurrency()
Application.ScreenUpdating = False
Dim Tbl As Table, r As Long, c As Long, xchg As Single, strVal As String
xchg = CSng(InputBox("Exchange Rate to Apply?", "ForEx Conversion", "1.00"))
If xchg <= 0 Then Exit Sub
For Each Tbl In ActiveDocument.Tables
  With Tbl
    c = .Columns.Count
    For r = 1 To .Rows.Count
      With .Cell(r, c).Range
        strVal = Split(.Text, vbCr)(0)
        If IsNumeric(strVal) Then
          .Text = Format(CSng(strVal) * xchg, ",0.00")
        Else
          .Text = "USD"
        End If
      End With
    Next
  End With
Next
Application.ScreenUpdating = True
End Sub
For PC macro installation & usage instructions, see: http://www.gmayor.com/installing_macro.htm
__________________
Cheers,
Paul Edstein
[MS MVP - Word]
Reply With Quote
Reply

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
Manipulating 3D objects in Powerpoint 2013 MScrazy PowerPoint 0 05-27-2014 02:29 PM
How to populate cells in Sheet2 with Data Source query using cell data from Sheet1 bobznkazoo Excel 2 03-27-2014 11:14 AM
Manipulating Task data from email. dvad Outlook 0 05-10-2011 01:55 PM
manipulating cross-reference fields _wim_ Word 0 12-10-2010 05:52 AM
Manipulating checkboxes in XML Ivo Word 0 12-06-2005 09:04 AM


All times are GMT -7. The time now is 04:13 AM.


Powered by vBulletin® Version 3.8.1
Copyright ©2000 - 2017, Jelsoft Enterprises Ltd.
SEO by vBSEO ©2011, Crawlability, Inc.
MSOfficeForums.com is not affiliated with Microsoft