Create Macro For DDE Link
Apr 5, 2006
I am using an excel add-in to create a DDE link between a market data provider and my spreadsheet. ( Add-in is called SigTools.xla )
DDE link works fine and target cells are updated dynamically as market data changes so my excel sheet values are changing constantly.
The formula =WinRos|LAST!GOOG will update target cell with most recent market prtice for Google.
Since this formula is not an excel formula I can't reference excel cells with formula - ie =WinRos|LAST!Indirect&A3 or anything like this won't work.
I thought a Macro might work to simulate "typing in correct formula" and I created following Macro which I thought would work but unfortunately it does not.
Sub Macro2()
'
Dim strSymb As String
strSymb = Cells(4, 1).Value
Application.Goto Reference:="TargetCell"
ActiveCell.FormulaR1C1 = "=WinRos|LAST! & strSymb & "
End Sub
The idea was that Macro would behave just as if I typed in =WinRos|LAST! & strSymb & and substituted the symbol name contained in Cells(4, 1) - whatever that happenned to be.
Seems reasonable but I get an error "Application-defined or object-defined error"
Alternatively I could re-type the formula each time but it would be much better to somehow reference a specific symbol in a particular cell via functioning Macro.
View 2 Replies
ADVERTISEMENT
Aug 17, 2009
Is it possible to have a macro create a hyper link once data is entered into specific cell, i have a multi sheet work book used as a stock card system.
Each stock item has its own worksheet and the product code is entered in to cell E3
(which in turn has a macro in it to change the sheet name to match the product code).
the macro also inserts a row of info into row 5 of another sheet used as a summary sheet ( CONTROL.SHEET or SHEET 4) using copy and paste link.
i need a macro to generate a hyperlink in cell D4 of the control sheet between the row info and the matching product code sheet ( the product code is displayed in cell E4 of the control sheet.
I only input one item at a time and then manually cut and insert the row info into the stock list on control sheet. so every time i input a new item, the row info is always in row 5 of the control sheet.
View 9 Replies
View Related
Jun 28, 2013
I have two worksheets, one with detail monthly information and one with YTD information. So let's say the three numbers I want to capture in the YTD sheet are in columns B, G and I on the monthly sheet. January's data might be in B5, G5 and I5. February's data is in B12, G12 and I12 and so on.
On the YTD sheet in cell C2 I link to Monthly!B5 and in C3 I link to Monthly!B12 so cells C2 to C13 on the YTD sheet show the monthly totals from column B on the detail sheet. On the YTD sheet, cells C20 to C31 show the monthly totals from column G on the monthly sheet, so cells G5, G12, etc. And finally, cells C40 to C51 on the YTD sheet show monthly totals form column I on the monthly sheet.
In the past I've always created all these links manually. After creating the links in C2 to C13 on the YTD sheet, is there a way to use a formula in C21 that uses the link in C2 to create a link for G5?
View 6 Replies
View Related
Jan 30, 2014
im currently working on contact details for each of our client. Attached here is the sample worksheet im currently working on. As the title suggest is it possible if i click the company name i will be directed to its contact details on the other sheet?we need a worksheet that functions like this: if we click the company name, it will direct the user to its contact details or will display ONLY its contact details.
View 14 Replies
View Related
Jan 17, 2007
on a workbook I use.
The workbook (26-11GL.xls) contains a varying number of sheets.
Sheet 1 - Menu
Sheet 2 - DS1
Sheet 3 -DS2
Sheet 4 -DS3
Sheet 5 - DS4
Sheet 6 - Company1
Sheet 7 - Company2
Sheet 8 - Company3
Sheet 9 ....14 Company 4,5 etc
Sheet 15 - CompanyTotals
What I would like to do is when I open the workbook on the Menu page I would like to have some links / buttons that will automatically create a new worksheet called DS5 which has all the formatting, formulas etc from DS4 (or the last complete DS sheet)
If this is possible I would like to be able to add Company worksheets as both DS sheets and company sheets can have from 3 to 35 sheets in either area.
If possible I would like the new DS sheets to be named with the next valid number.
View 6 Replies
View Related
Apr 28, 2009
I have two files,
1. Form Templete & 2. Student Data Index. (Both files will be placed in same folder)
About Form Templete : This is a simple form, containing some fields. Fields which i require are Form No., Student's name & Standard.
About Student Data Index : This file is to maintain records.
Each time when I fill form for a new student i will copy paste the form templete file & rename it by form no. (Entered in the Form Templete file, Form No. field)
My question is : Is it possible that whenever i create a new file in the same folder with student's name the required fields in the Student data Index get filled automatically, apart from that the new file created get hyperlinked & when i click on form no. the form for that student gets open?
View 11 Replies
View Related
Mar 17, 2014
I downloaded a spreadsheet with multiple same names such as
Name Week ending Days off
John Smith 1/31 2
John Smith 2/7 1
John Smith 2/7 3
Peter Sun 1/31 1
Peter Sun 2/7 2
Andy Mann 3/14 3
If I want to sum these up by name, what's the best way to do that?
View 9 Replies
View Related
Aug 3, 2009
I know how to create a text link to other cells within a work book but how can I create a button which will do the same?
View 2 Replies
View Related
Nov 16, 2006
This link worked perfect for my needs: Create a List of Hyperlinked Files:
But is there any easy way to only show the filename insted of the full path for the name of the hyperlink? Now it shows it like :C:MyDocumentsTestings est1.xls
I only want it to show as: test1. I still want the hyperlink to work
View 2 Replies
View Related
Jan 27, 2008
How to create an area in excel where by if a customer enters an account number all of their account details would be automatically entered into the address fields?
View 9 Replies
View Related
Oct 9, 2013
I've been playing about with this for ages, I have row 'U' which I manually link to local JPG files. Basically it has the word "HERE" and linked to a JPG that opens when you click it.
I'm really looking to just RIGHT CLICK a cell in column U, it opens a file browser, when I select the file it places HERE in the cell and links it to the file I've selected.
View 5 Replies
View Related
Feb 12, 2014
I need to create a macro that can create a dynamic copy/paste loop. So far what I have is horribly inefficient. Each row in colmn A(minus the header) has a unique number in it. For each unique number, I need to paste it based on the number of column headers in row 1(minus column A). So, if there are 20 column headers, I need to copy cell A2 and paste it 19 times in another sheet. Then, I need to move to the next number in column A and do the same thing. Here's what I have:
[Code] .........
You can see that this is not dynamic. If I add another row to my table and rerun the macro, it will not catch it. I've attached a sample file to show you the big picture of what I'm trying to do. The data that I have is in Sheet1, and I'm trying to get it into the format in Sheet3. Rows/columns will be periodically added to the table in Sheet1, so the macro needs to be dynamic to catch that. The data in Sheet3 will always remain, and the macro will add the updated data below the old data in Sheet3.
FC_Macro_Sample.xlsm
View 8 Replies
View Related
Nov 4, 2008
I wonder if it is possible to bring data from DDE-links to variables in a macro without first bringing it to cells in Excel. The reason why I want to do this is because I only need the data when the macro is run (that is when a certain button is pushed). It would be a waste of system resources to continously link the data to cells in Excel. I asked the same question a few days ago at another forum but didn't get any reply.
View 2 Replies
View Related
May 11, 2007
Is it possible to link 3 buttons to one macro.
What I'm trying to do is have 3 buttons:
Load Button:
-Which makes a copy of the current sheet, renames it, and freezes its values
Print Button:
prints all the sheets
Clear button:
deletes all of the named sheets
I'm sure this can be done with an if then else statement, but I'm just not sure how to set it up to capture input.
View 9 Replies
View Related
Jul 12, 2009
I have two different worksheet that if data is changed then the user needs to go to the company's website to update information there as well.
Can a macro look at a range of cells and if a change is made then a message box would pop up and if the user said yes then they would be directed to the company's website.
View 9 Replies
View Related
Aug 17, 2009
Is there a way of embedding a formula in a worksheet so that the tab name of each worksheet is linked to a value in a cell on that sheet? (I don't want to run a macro)
In other words if the cell name is changed, the page name changes automatically.
View 9 Replies
View Related
May 15, 2014
I am looking for a macro that can click on a link within a website. This link produces a print window. Below is some similar code that I have used before but this link does not have an ID so I am not sure what to do. I am also posting the HTML Code and a jpeg of the HTML code.
[Code] .....
HTML Code:
<TR>
<TD height=24 noWrap>
<IMG alt="" src="/SharedFilesCB/LeftMenuControl/Images/spacer.gif" width=2 height=1>
<A onclick="if (bIsFrameLoaded) PrintReportClick(); return false;" onmouseover="status='';return true;" class=leftMenu href="">print reports</A></TD></TR>
F12_2014-05-15_17-22-08.jpg
View 2 Replies
View Related
Jul 11, 2012
I need to link information between two sheets.
In Sheet2 I have a list of products with their description in adjacent columns such as prices and other information. Sheet1 is a MasterSheet of some of the products listed in Sheet2. So I would like to write a macro that links the information of the product from Sheet2 to Sheet1 if I type the products name in Sheet1 (MasterSheet). The list of products (Sheet2) is updated in a regular basis, so by linking the info I would just need to update Sheet2 and Sheet1 would update as well.
View 4 Replies
View Related
Oct 29, 2012
Sub FollowLinks()
Dim i As Hyperlink
If (A1) = " http://www.ecb.int/stats/money/aggre...ng_amounts.zip " Then
OpenLinks " http://www.ecb.int/stats/money/aggre...ng_amounts.zip "
End If
End Sub
I'm really stuck, as I only just learnt VBA yesterday. I'd also like the macro to run when you press a button on the keyboard, is there a way to do this?
View 2 Replies
View Related
Mar 16, 2014
I have a large excel file with lots of data that it takes a long time to manipulate in excel. What I would like to do, is to import it in access, do some manipulations, and export it in excel again.
All these have to be automatic, so that the user does not do anything. Is it possible to do so through some macro?
View 1 Replies
View Related
Jan 9, 2008
I have one list in one sheet that is source for the userform and combobox used on another sheet. With userform I make a choice and data is entered in one cell.
But is it possible to compare the choice that I make in combobox with the list on the first sheet and then make a simple link between these two cells, so that cell in the second sheet is always showing what is in the cell on the first sheet.
Reason for this is that items on the list on the first sheet sometimes change and I need to update the second sheet automatically.
Private Sub cmdOK_Click()
With Sheets("Presentation").Range("V18:V32")
Set c = . Find(cbochange.Value, LookIn:=xlValues)
If Not c Is Nothing Then
firstAddress = c.Address
Do
ActiveCell.Offset(0, 2).Formula = firstAddress
Set c = .FindNext(c)
Loop While Not c Is Nothing And c.Address <> firstAddress
End If
End With
Unload Me
End Sub
View 3 Replies
View Related
Jan 29, 2013
I am trying to prepare macro to navigate web page and click on some tasks to complete it. As this web link is not access-able from outside or remotely. I am trying to work on it in bits. I have came across with a first problem of clicking on one of the link of this web page, below is the web page html source code. I want to click on last link "Total Outstanding Tasks".
HTML Code:
<td width="29%" nowrap>
<a id="A6" href="FindTask.aspx?StatusID=1,2,5,3&SLAhour=10&pageMnuOpt=4&mnuOpt=1|4&OtherMenu=true&SortOrder=1&CreatedBy=485&PreDefinedFilter=6&IsAssign=0" class="bodylink">Total Outstanding Tasks</a> </td>
<td width="29%" nowrap>
<a id="OutTasklnk" href="FindTask.aspx?StatusID=1,2,5,3&SLAhour=10&pageMnuOpt=4&mnuOpt=1|4&OtherMenu=true&SortOrder=1&AssignedTo=485&PreDefinedFilter=6&IsAssign=1" class="bodylink">Total Outstanding Tasks</a> </td>
<td width="29%" nowrap>
[code]....
View 1 Replies
View Related
Feb 8, 2008
I have a spreadsheet setup which includes a number of checkboxes set up using the forms toolbar.
I would like to link each of these checkboxes (in sheet1) to a cell reference in sheet2. I'm trying to write a macro that will do this for me to save me right clicking, choosing format control etc for each checkbox.
I found the code on this page: http://www.mrexcel.com/archive2/51300/59643.htm
which appears to be similar to what I am trying to achieve. In this case it creates the checkboxes in cells B3:B20 and links them to C3:C20.
I have already created all the checkboxes, and wish to use a macro to link them (for example) to C3:C20 in sheet2.
View 9 Replies
View Related
Jul 24, 2007
I have a sheet with 3 macros. It consists of two macros to produce results, and the third to bridge the two together where is all starts. I have built a userform in the sheet and am trying to get the variables in both sheets, to be user changeable and selectable via the userform.
In the attached data, the userform allows for the selection of two sheets, A and B. The user would select in A the sheet with data which is equivalent to Oval_An and in B the sheet with the data equivalent to Oval_DMA. The first macro, Find_75 runs, followed by the second macro, kTest, all working to produce results when hitting the Subtract button on the form. The Find_75 tolerance are also defined via the useform box, as well as kTest's compare tolerance.
View 6 Replies
View Related
Feb 18, 2008
I would like to write a macro that links cells in one workbook (working) to cells in another workbook (summary).
I don't want this to be a straight swap thought, I'd like it to contain the following formula:
=IF(Working!G8="a","a","")
The cells in the working workbook are listed vertically, while the cells in the summary workbook are listed horizontally.
ie. link G8-G18 in working to B3-K3 in summary
link H8-H18 in working to B4-K4 in summary
...etc
View 9 Replies
View Related
May 19, 2008
I am trying to write a code in VBA to collect data from several weekly workbooks, and list them up in a monthly workbook.
These weekly workbooks change names depending on the week, for example, we have:
Report week 1.xls
Report week 2.xls
Report week 3.xls
Report week 4.xls
Each report has the same layout, so the data I want will be in the same cells. However, as the report names change as the weeks in the year go on, I can't write the code for weeks 1-4 as they will not work with week 5 onwards!
I have found out how to chose a file of my choice using:
Sub GetImportFileName()
Dim FInfo As String
Dim FilterIndex As Integer
Dim Title As String
Dim FileName As Variant
' Set up list of file filters
FInfo = "All Files (*.*),*.*"
' Display *.* by default
FilterIndex = 5 ......................
View 9 Replies
View Related
Dec 3, 2013
I need to create a Macro that will link Horizontal data to vertical data in a separate sheet. Here's the Example:
I have data in a forecast spreadsheet that is horizontal, So, J2:U2 (12 columns for 12 months). This row of data needs to be linked in a single column of data on a separate sheet called "Price_Quantity". So I need C3:C14 (in price_quantity sheet) to be linked to the data in J2:U2 (in forecast sheet). Then C15:C26 needs to be linked to J3:U3, so on and so forth. 12 rows linking to 12 columns. All the way until C4959:C4970 is linked to J415:U415.
View 6 Replies
View Related
Sep 22, 2006
Is it possible to have a user form command button perform link or steps that are recorded with macro recorder? I am trying to reformat cut and paste a xls raw data file. I am trying to keep my file uploads to a min., but can upload an example if necessary.
View 4 Replies
View Related
Jan 12, 2007
I have set up a form where the user can enter the number of copies they want for 10 items (I will call them item1, item2 etc for ease)
Is it possible to then link the numbers in the form to separate parts of a large printing Macro?
View 9 Replies
View Related
Feb 22, 2014
Using excel 2011 for mac and would like to learn how to write and record a macro to link to a button to "go to a layout and cell"
View 4 Replies
View Related
