Copy from formula bar to clipboard

[stavrosbezas]

I am having trouble programming a macro to copy the text from the formula to the clipboard so I can use it in another program. Selection.Copy doesn't work when pasting, just copies a blank line. Here is an example of what I want to do:

Run Macro
Copy data from formula bar, (ie. JohnSmith)
Continue to Run Rest of Macro (which access external program and waits for it to return)
While waiting for it to return I want to paste that same data I copied into a field in the External Application.
When external Application is done, Excel detects and proceeds with the rest of the macro.

Everything else is working FLAWLESSLY and now I just need it to do this seemingly very simple task. Please advise!

 

[DPlank]

Can you paste the code you have already?

The text in the formula bar is the text in the activecell.

try assigning something like:

textiwant = activecell.value

then pass the variable to wherever it's needed to be pasted.

Dave

"Probably the only Test Analyst on Tek-Tips"

Take a look at Forum1393 & sign up if you'd like

 

[stavrosbezas]

Here is what i have so far, After the message box but before it runs the shell command is when I want it to be copied. Your response is a little unclear, as I am not as Advanced at this as I like to act like I am. Could you dumb it down for me? Thanks for the quick response as well!

Sub Update()
'
' Update Macro
' Macro recorded 2/2/2005 by SBezas
'

'Will need to customize filename for each client

    Dim Response As Integer
    ' Displays a message box with the yes and no options.
    Response = MsgBox(prompt:="Update Rebalance Sheet?", Buttons:=vbYesNo)
    ' If statement to check if the yes button was selected.
    If Response = vbYes Then
    FindIt = Dir("g:\temp\exports\rebal.xls")
    If Not Len(FindIt) = 0 Then
    Kill "g:\temp\exports\rebal.xls"
    End If
  
    
    Shell "G:\Axys3\axys32.exe script rebal1.scr"
    FindIt = Dir("g:\temp\exports\rebal.xls")
    While Len(FindIt) = 0
    FindIt = Dir("g:\temp\exports\rebal.xls")
    Wend
    Workbooks.Open Filename:="G:\temp\exports\rebal.xls"
    Columns("A:D").Select
    Selection.Copy
    Windows("AA_Sector_Master.xls").Activate
    Range("A1").Select
    ActiveSheet.Paste
    Range("F4").Select
    Windows("rebal.xls").Activate
    Columns("A:D").Select
    Selection.ClearContents
    Range("A1").Select
    ActiveWindow.Close savechanges:=False 'true ???
    FindIt = Dir("g:\temp\exports\rebal.xls")
    If Not Len(FindIt) = 0 Then
    Kill "g:\temp\exports\rebal.xls"
    Windows("AA_Sector_Master.xls").Activate
    End If
    Columns("A:E").Select
    Range("E1").Activate
    Selection.EntireColumn.Hidden = True
    Range("F1").Activate
    Else
    ' The no button was selected.
    MsgBox "Operation Cancelled"
    End If

End Sub

 

[stavrosbezas]

Any furthur advice? Thank you.

 

[stavrosbezas]

Dplank, where have you gone, I need your help please!