Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 344
  • Last Modified:

Excel Currency Conversion Update

EE Pros.,

Greetings!  I need a little help with a Currency Conversion capability.  I have a SS attached that shows a list of currencies (Dollars, Euros, etc.).  What I'm looking for is a Macro that when selected, would update the currencies by going out to a source (MS Money?) and refreshing the conversion rate.  That's it!

Thank you in advance.......

B.
Currency-Conversionv1.xlsm
0
Bright01
Asked:
Bright01
  • 11
  • 8
1 Solution
 
MacroShadowCommented:
While it's possible to do what you want using VBA, it is complicated. A much easier way would be to use Excel's built-in web-query.

Try this, if it is not what you need post back:
1. Select the DATA tab on your excel 2010 ribbon and select EXISITING CONNECTIONS
2. Select the option MSM MONEYCENTRAL INVESTOR CURRENCY RATES
3. Done!
0
 
Bright01Author Commented:
MacroShadow,

Thanks for the quick post!  

OK... I added MSM MONEYCENTRAL INVESTOR CURRENCY RATES but how do I use it?  Does it post results somewhere?  

I'm looking for a way to automate this and populate the model I attached.  Can I do that with this link?

Thank you,

B.
0
 
MacroShadowCommented:
All you have to do is set up the web table once, you get to choose where to save the table. The results will get refreshed automatically.

But, if you are looking to use the model you posted, I guess we will have to go the long way, the VBA way.
0
Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

 
Bright01Author Commented:
I have no problem checking this out.  How do I set up a Web Table?

B.
0
 
MacroShadowCommented:
Follow the instructions I posted, you will be prompted for the location of the table, either use the default, adjust the range or choose "A new worksheet".
0
 
Bright01Author Commented:
I did follow the instructions.  I selected MSM MONEYCENTRAL INVESTOR CURRENCY RATES. There is no "prompting for a location" when I select.  I'm assuming that gives you a currency table.  How do you get the conversion to happen?

Sorry for the confusion.

B.
0
 
Bright01Author Commented:
Aahhh.. Ok.... I got it to download the connection.  Let me work with this for a few minutes.

B.
0
 
MacroShadowCommented:
Did you click "Open" on the bottom of the window?
0
 
Bright01Author Commented:
OK... here's my challenge.  It seems like it always loads to the A column and shifts the other columns to the right.  I need it to load where I direct it (B25).  Secondly, I don't know how to add a formula that then changes the currency conversion by selection (drop down box ((K14))) that would change the conversion based on the base currency.   I've attached v2 for you to see what I did in bringing down the Money Connection.

Thank you for the assistance.

B.
Currency-Conversionv2.xlsm
0
 
Bright01Author Commented:
OK...got it to download to the appropriate point.  How do I get this finished up with the "selection" and the population of the results?

I've attached the latest sheet.

Thank you,

B.
Currency-Conversionv3.xlsm
0
 
MacroShadowCommented:
Here is your solution, the rates are from http://www.x-rates.com. I don't know if they're accurate or how often they're updated, check it out yourself.

Disclaimer: I take no responsibility for any damage that may result from the use of this sample.
Currency-Conversionv1.xlsm
0
 
Bright01Author Commented:
MacroShadow,

Much thanks!  It seems to work great.  I will be adding functionality to it shortly so as to enhance the actual macro to do more.  But you have definitely gotten me started.  And for that I am very grateful.

Thank you again for outstanding work.

B.
0
 
Bright01Author Commented:
MacroShadow,

Now that I'm working to incorporate this into my major WB, I have one additional question.

I'm not familiar with the constructs where this code resides. I usually use Macros that are either in modules or in the WSs.  Also the names on these objects are not clear.  Can I rename them so as I import them or copy them into my WB, I can quickly identify them as to what they are for?

Thank you,

B.
Currency-Function-Code.pdf
0
 
MacroShadowCommented:
Module's names can be changed but don't change the class module's name.

I'm not sure what the gibberish is all about, the first one is the worksheet module and the second one is the workbook module as you can see from the icons.
The code in them isn't critical, it just updates the rates when a. the value of the combobox is changed, and b. when the workbook is opened, respectively.
0
 
Bright01Author Commented:
Can I consolidate any of the Modules?  or Class Modules?  Can I move the code from the two modules that have the gibberish into a new module with clear naming convention?

Thank you,

b.
0
 
MacroShadowCommented:
All standard modules can be combined, just make sure that any declarations are kept at the  top of the module.
DON'T rename the class module.
The modules with the gibberish are not standard modules, as such they should not be renamed by the user. Copy the code from the first one to  the worksheet module that contains the the drop-down list of currencies, and the code from the second one to the workbook module.
0
 
Bright01Author Commented:
Very sorry; but I don't understand.  I won't rename the class module.  I'd like to get rid of the modules that have the gibberish (never seen that before) and when I try to rename them I get an error message.  I understand your directions on copying the code....but what about renaming the Sheet "Currency" as it was originally?  What gives?

B.
0
 
MacroShadowCommented:
You wrote: "Now that I'm working to incorporate this into my major WB"

Copy the code from the first gibberish'ly named module to the worksheet module that contains the the drop-down list of currencies in your major WB, and the code from the second gibberish'ly named module to the workbook module in your major WB.
0
 
Bright01Author Commented:
Got it!  Worked perfectly!  Thank you for the coaching.

B.
0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 11
  • 8
Tackle projects and never again get stuck behind a technical roadblock.
Join Now