Create UDF For Currency Conversion With VBA Only - No External Connections

Feb 15, 2013

The main objective is to create a, UDF with 2 parameters (Currency, Date) in order to display currency exchange rate based on the currency and date. Let's say I want to find an exchange rate between USD/ EUR, then I would have a formula something like "=FX(EUR, 12/31/2011)". All currencies will be against USD.

The trick is that it will have to be created as Add-On so that it can be used in any Excel spreadsheets. The second trick is that I cannot have VBA to make any internal connections to other workbooks or applications.

So the only thing that I can come up with would be to write a VBA code that would include all values and currency rates in it. The structure that I have in mind would be something like:

Code:
Function Fx(Vl As String, Dte As Date)
Dim Rte As String
If Vl = "EUR" And Dte = "12/31/2011" Then
Rte = 0.7723
ElseIf Vl = "EUR" And Dte = "12/31/2010" Then
Rte = 0.7546

[code]....

The above code does not work!There will be at least twenty different currencies and at least 15 periods.

View 2 Replies


ADVERTISEMENT

External Data - Cleanup Existing Connections

Apr 26, 2013

While making my first foray into defining external connections (to an Oracle database) I ended up with an "Existing Connection" that reports "File could not be opened. It is either damaged or the file format is not valid. Try opening the application that created it and saving to a new file." Followed by "Unable to open or read this connection file. Either the file has been damaged or the file format is not valid."

Ok, fine. So how do I delete it? There seems to be no option for that in the "Existing Connections" dialog. A search for a *.odc or *.udcx file doesn't turn up anything with the name of this connection. A search of the registry doesn't turn up anything.

extdata.jpg

View 1 Replies View Related

Macro To Delete All External Data Connections

Mar 31, 2009

I am trying to write a macro to delete all connections from a very large excel file

I created this file by using a Macro to extract data from a external data source (url's listed on a sheet)

The issue is the 1000+ connections on this file make it next to impossible to operate

I cannot recreate the file

macro to delete all connections in all sheets in a given work book.

[URL]

Macro used :

Sub Dump()
Dim myURL
Sheets("Sheet1").Select
ActiveCell.Offset(1, 0).Select
myURL = Worksheets("Sheet1").Range(ActiveCell.Address).Text
Sheets("Sheet2").Select
Range("A65536").Select

[code].....

View 3 Replies View Related

Currency Conversion

Jun 23, 2008

I am working on a sheet that logs sales enquiries and quotations. The quotations may be made in either Dollars, Euros or Pounds. However, I want the 'reporting' column to be Pounds. I would appreciate any advice on the simplest way of achieving this.

Imagine column A is dollars, column B is Euros and column C is pounds. Most of the time the quotations are done in pounds and the other two columns will be blank. Also, there will only ever be one figure quoted per row. Would it be easier to create a fourth column (D) to consolidate the three separate figures together? We are using fixed exchange rates for the year, so to convert to pounds we will multiply any dollar quotations by 0.5 and any euro quotations will be multiplied by 0.69.

View 9 Replies View Related

Currency Conversion: Convert In GBP

Apr 10, 2009

i have an order book which lists the value of each order against the currency it was ordered in. So in column A i have a list of currencies (THB, GBP, AUD, USD, the user clicks the appropriate one) and in column B i have the figure (23.50). I want a formula that can convert all these in GBP so that i can report the order totals in one currency in column C. =(IF A1="THB", B1/50, "") can i adapt this to include all variables? I will add a table with the currency conversion rates.

View 2 Replies View Related

Automatic Currency Conversion

Aug 16, 2006

I'm using excel 2000

Is there anyway to format cells such that I can enter values as Euros and they automatically convert to £

eg If I enter 14.60 (Euros) it automatically displays as £10.00, my conversion rate of 1.46 is constant throughout

I know I can do the copy paste special divide thing but the above would be a much nicer solution

View 9 Replies View Related

Currency Conversion By Dropdown

Jun 12, 2008

I have a sheet of prices in USD, and want to be able to convert those numbers to Euro. I realize there is no formula to do this...

I was going to list USD prices in cell A (which I was going to hide), and then in B have a formula to multiple the contents of cell A with the exchange rate.

Is there a way I can create a dropdown cell and based on the contents of the dropdown change the currency items in cell B?

View 3 Replies View Related

Function To Create Currency Pairs

Jun 22, 2014

I am trying to figure out a function that creates currency pairs, for example EUR/USD etc based on a limitation.

I attach you a workbook with the details. The first step I have accomplished, I am having trouble to the second step as you will see in the sheet.

Test.xlsx

View 8 Replies View Related

Excel 2007 :: Automatically Converting All CURRENCY Symbols To ONLY Currency Selected

Mar 11, 2012

How to make EXCEL 2007 Automatically convert all my CURRENCY symbols in all cells formatted for CURRENCY in THE SAME SHEET to the one and ONLY currency selected in JUST ONE cell ? (i mean, after conversion, all of them are Not just display currency symbols, but Calculable monetary values like regular Excel's built-in currency format)

I am a dummy in VBA so I really am not familiar at all if the solution is really one of VBA. I don't even know how to start programming VBA at all in Excel.

View 3 Replies View Related

Currency Additions (show The Total Of Each Currency Separtely)

Apr 23, 2009

I have several rows which lists different currencies - USD, GBP, EURO, JPY etc.,

A1 usd 5000
A2 GBP 7000
A3 USD 3400
A4 EUR 12000
A5 JPY 89000
A6 EUR 1000


At the bottom I want to show the total of each currency separtely.

View 13 Replies View Related

Remove All Connections?

Feb 9, 2010

I've got a little program which uses webqueries to import data from anywhere between10 and 200 web pages. It's working pretty well, but I need a way to clear out the residual connections via the code.

The part of the macro which imports the data was simply recorded and very slightly modified. I've included it below... but what I'd like to do once the data has been imported is delete the connection.

View 3 Replies View Related

DB Connection Lockout Other DB Connections

Mar 7, 2007

I have a series of external database connections that I run in Excel, connecting to a Server based DB and running some SELECT statements. (ingres). We may have up to dozen of client machines accessing the ingres DB at any time. All is good at this point but when excel goes to INSERT some calculated data back to the main database, it appears that my Excel spreadsheet connection is locking the database when it connects, keeping other people from using their client connections.

first question: Does anyone know if the connection excel uses to run sql statements via external database connection will indeed prevent other users from connecting to the main database?

second question: if so, is there a setting or something that can make excel more "passive"?

View 9 Replies View Related

How To Enable Data Connections

Dec 16, 2011

I have a workbook which uses data connections to our sage accounts product. Everytime i open the sheet i get the "Data Connections have been disabled" security message, and need to go in and enable them. Is there any way of enabling the data connections by default? I only want to do it for this one workbook - i still want to be prompted when i open other workbooks.

View 2 Replies View Related

Access Databse Connections In VBA

Jul 22, 2006

From within VBA I need to open a database connection and issue a select and store the results in my spreadsheet. The issue is that I only JUST learned that you can do this and I dont know where I can look for instructions. Incase it matters, I am trying to connect to a Sybase DB. I have used perl, c# and a little bit of Java to do this but never from with in Basic (let alone basic within excel!)

View 9 Replies View Related

Close Data Connections After Refresh

May 3, 2013

I currently have a spreadsheet that has data connections to a file on a shared drive. On opening the file I have it update the data connections so that the data needed is always correct. The file I use is to be distributed out to work colleagues to use and on testing it works really well apart from if you have the file open and somebody tried to open the original file where the data is pulled from for the connection. It says it currently in use by 'another user'.

Is there any code to add to workbook_open that will close/disable the data connection links once the data has been updated? The code I currently have is this:

VB:
Private Sub Workbook_Open()
ActiveWorkbook.RefreshAll
Application.Wait Now + TimeValue("00:00:02")
Userform1.Show
End If
End Sub

What this basically does is gives it time to refresh the data connections and show a progress userform. The only thing is the connections stay live and I don't want that as the connection file is then locked. I'm hoping its something simple like ActiveWorkbook.CloseDataConnection added in after the userform has been shown (or something along them lines ). Also the data connections would have to be able to reconnect on open so that they can refresh again.

View 5 Replies View Related

Defining Column Reference With ADO Connections Using VBA

May 16, 2008

It extracts Employee ID's and there Names from our database and lists them in columns A:C

What I want to do is list the ID's manually and get it to extract only the Names for the ID's I listed in Column A...

I am not sure how to amend this code to look at ID's listed in column A instead of extracting the whole database.

I know I need to add a "WHERE ID =" clause in the SQL but not sure how to say WHERE ID = A1, A2, etc.

View 14 Replies View Related

Editing Data Connections In 2003

Oct 20, 2009

I have a workbook with multiple worksheets and over 25 Data Connections using MS Query to connect to different tables in an SQL Server database.

The problem is that the SQL Server database is now being moved to a different server. The database name and the schema of the tables will be unchanged. Only the server name needs to be changed from XXXXX to YYYYY.

I have been unable to find a way to successfully edit the data connections in Excel 2003. A web search produced one potential option. However, that did not work for me very well. Thought the code is designed to cycle through all tablequeries and pivotqueries, the change is only made to one of the data connections. Here is the option I tried:

http://support.microsoft.com/kb/816562

Note that it is very easy to edit data connections in Excel 2007. So I also tried opening the .xls file in Excel 2007. I used the Data Connections button under the Data tab in the ribbon and was successful in editing the connections to point to the new server. However, the graphs used to display the queried data behave weird. The "format axis" settings and chart sizes change. In general, default display settings for charts seems to differ between Excel 2003 and 2007.

I used Excel 2007 to only edit the data connections and saved the workbook in the original .xls format

View 3 Replies View Related

Multiple Sorts In Connections To Other Databases?

Nov 14, 2012

I have a connection to an SQL database using microsoft query.

The query definition includes: ORDER BY AAStocktrans.Code But I need a two level sort along the lines of:

ORDER BY AAStocktrans.Code THEN ORDER BY AAStocktrans.Date but obviously that is not correct - cannot use THEN. what I do use to make this work?

View 2 Replies View Related

VBA To Change Workbook Data Connections

Jun 2, 2014

My issue is we have a remote office, using an access DB which links to an Excel file. We sometimes send the files to other people to make changes, but the link is then broken (it was a network location).

You can just change it manually, or accept the many prompts that come up, but it needs doing at each end, each time.
I thought I'd do a routine to change the connection paths, this is what I have...

Code:
Sub alan()
Dim i As Long
Dim cnt As Long
Dim modtext As String, modfile As String
Dim modrange As String, OldPath As String, NewPath As String

OldPath = "\192.168.1.100MultimediaCommissioning Folder"
NewPath = "C:matts"

[Code] ...

I added the sourcedatafile part to the routine afterwards, because the connection string part worked fine. But I now get Run-time error '1004' Application-defined or object-defined error...

View 3 Replies View Related

Closing Existing Connections To Access Databases

Jan 17, 2014

I've created several access databases to which I've connected my excel files. However I've either deleted/moved these databases but my connections are still showing in Data -> Existing Connections tabs.

I've tried going to C:Program FilesMicrosoft OfficeOffice14QUERIES but no such queries exist.

Is there any way to clear my list ?

View 2 Replies View Related

Slicer Connections Lost When Copying To New Workbook

Jun 12, 2014

The main objective of the code is to create different workbooks that are going to be send to different people(with only their data).

I am using the filter to delete the data that I don't need and then create a new workbook, save it and close it. This has been working fine.

The issue is that I have two slicers in the 'DistMenu' sheet. When I open again the newest workbook, they slicers are disconnected to the pivot tables. This can be fix manually by selecting the slicers --> PivotTables Connections etc

I use the macro recording to get a code and work from there, but is giving me error '1004' "Unable to get the PivotTables property of the Worksheet class".

View 1 Replies View Related

Excel 2007 :: Delete ALL Connections In Workbook Without Having To Specify Names

Oct 20, 2008

The following code deletes a specific connection in a workbook:-

ActiveWorkbook.Connections("text123").Delete

How can I code this so that I can delete ALL connections in a workbook without having to specify the names (as these will always be different).

View 3 Replies View Related

VBA - Save File As New Workbook BUT Without Existing Data Connections

May 23, 2013

I'm trying to use vba to create an offline backup file for a sheet that is linked to an access database. the code I have is simple and worked for a file that is not linked to any database.

Code:
Sheets("Data").Select
Sheets("Data").Copy
Application.DisplayAlerts = False
ActiveWorkbook.SaveAs Filename:=myfilename
Application.DisplayAlerts = True

Now that I insert it into the actual file, which is linked to the database I get an error. I also tried to move the display alert up, before the select instructions, but also this does not work. this is not what I wanted since I want this file not to possibly connect to any database.

View 9 Replies View Related

Opening Excel Workbook With Embedded Connections Using VBA And Auto Refreshing

Apr 3, 2014

I have an Excel Workbook with Showcase Queries embedded into it.

Upon a manual open, the user is prompted to either refresh data or keep data since last save, the user then has to enter a username and password to allow the query to access the database.

Using the code below to open the workbook, no such prompts are given. Is the code overriding the prompts and automatically refreshing the data without password? [Given nature of data, visual validation of refresh is not possible]

If not, how can we code a data refresh through showcase query with user/pw req's?

[Code] ......

View 1 Replies View Related

Removing Data Connections And / Or Query Tables Before Saving File?

Oct 10, 2013

I've tried to remove connections after saves and have been unsuccessful.

HTML Code:
' Delete Additional Connections
' If ActiveWorkbook.Connections.Count > 0 Then
' For i = 1 To ActiveWorkbook.Connections.Count
' ActiveWorkbook.Connections.Item(1).Delete
' Next i
' Else
' MsgBox ActiveWorkbook.Connections.Count
'
' End If

HTML Code:
' Remove named ranges
For Each nm In ActiveWorkbook.Names
nm.Delete
Next nm

I've tried both of these and the Excel file is still attached with XML to the query table. I get an error message saying the format is different then if you click okay it shows its connected to the database still. Is there an aggressive command that will severe all connections before saving? What commands to run to stop this connection after save to another workbook?

View 2 Replies View Related

Open / Refresh Connections To ODBC - Recalculate One Sheet / Save And Close

Mar 25, 2014

I am using Task scheduler to open a workbook which has some connections to an ODBC that are set to refresh on opening.

I need to automate:-

One of the sheets needs to recalculate for the sort by ascending code to run on the sheet.

Save and then close after say 2 minutes of opening (to give the ODBC connections enough time to finish).

View 2 Replies View Related

Hyperlink Conversion

Jul 30, 2009

I have just been told that one of our servers is being replaced this weekend. I have thousands of hyperlinks in excel docs that reference this server. Other than manually reestablishing the links one by one to the new server, is there a way to do some kind of global change to replace the old server name with the new server name?

These are not hyperlink formulas, but rather Insert>>Hyperlink.

Or, is there a way to convert these hyperlinks to hyperlink formulas that would omit reference to the server and thus not be broken during the conversion?

Grasping at straws because I cannot envision having to relink these one by one.

View 3 Replies View Related

Yen Conversion Macro

Feb 12, 2009

I have a worksheet with values in yen in the C column. My boss (who will not get it through her head that I am not a programmer) wants a macro that will take a yen/$ conversion rate (e.g., 88 yen/$) in cell J1 and convert all the amounts in column C into dollars with a 5% markup and rounding up. If I was doing this as a worksheet calculation rather than a macro, the calculation would look like this:

View 7 Replies View Related

PDF Conversion Code

Feb 9, 2010

The following codes publish the worksheets "Estimate" & "Photos" to a PDF file, it all works great accept that the sheets remain selected in the WB after the sheets have been published. How do I unselect the estimate and photos sheets?

View 6 Replies View Related

Scoring Conversion

Nov 4, 2008

If I have a scoring grade for different categories (8 in total) & the grades looks something like that: 1a=500, 1b=400, 1c=300, 2a= 250, 2b=210...& so on till 4c, but with different numbers for each category.

I need to come up with a total score of points if a person is given a certain grade from the above. ex. if they scored 2a on a certain category, 1c on
another & with all the rest of 8 categories scored. The number i need to come up with is the total points.

View 9 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved