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, 2 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,137
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
Please reply to this thread with any new information or opinions.

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 07:33 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