How to recall a path from clipboard for a macro to use

[makhtoot]

Hello Dear Friends
I have a very simple Macro Code but want to alter a part of it. i want instead of the line in front of the Address, paste the path from the clipboard. the code is like this:

Sub Macro1()
'
' Macro1 Macro
'
' Keyboard Shortcut: Ctrl+d
'
ActiveSheet.Hyperlinks.Add Anchor:=Selection, Address:="D:\Test\0157.jpg"
End Sub

Thank you in advance

 

[pgc01]

Hi

Try the following:

- in the VBEditor in Tools->References, set the reference to

Microsoft Forms 2.0 Object Library

- Try:

Sub Macro1()
'
' Macro1 Macro
'
' Keyboard Shortcut: Ctrl+d
'
Dim DObj As DataObject
Dim s As String
 
' Get the text in the clipboard into s
Set DObj = New DataObject
DObj.GetFromClipboard
s = DObj.GetText
Set DObj = Nothing
 
' add the hyperlink
ActiveSheet.Hyperlinks.Add Anchor:=Selection, Address:=s
End Sub

 

[makhtoot]

Thanks for your Help, But it turned out that the code copies the upper cell of the current cell that i run the macro on with the same name and hyperlink. any suggestion? thank you very much anyway

 

[pgc01]

Thanks for your Help, But it turned out that the code copies the upper cell of the current cell that i run the macro on with the same name and hyperlink. any suggestion? thank you very much anyway

Sorry, this makes no sense to me.

Please do the following test.

- select some text and press CTRL-C to copy the text into the clipboard

- execute the code below.
You should see a message box with the text in the clipboard.

Please test.

Sub Test()
Dim DObj As DataObject
Dim s As String
 
' Get the text in the clipboard into s
Set DObj = New DataObject
DObj.GetFromClipboard
s = DObj.GetText
Set DObj = Nothing
 
' display the text from the clipboard
MsgBox s
End Sub

 

[makhtoot]

Hello and thank you for your help
i assume that i should run this code in VB console, but when i run it it gives me a compile error and highlights the first line of the code for me???

 

[pgc01]

Hello and thank you for your help
i assume that i should run this code in VB console, but when i run it it gives me a compile error and highlights the first line of the code for me???

Did you set the reference, as I wrote in my post?



Also a remark:

If you get an error when running code always post somthing like:

It highlighted this line

' copy the line here '

and displayed this error message

' the text of the error message here '

 

[makhtoot]

Hello Dear Friend
Here is the error in the form you showed me to post:

It highlighted this line in blue while error window opens:
 
[B]DObj As DataObject[/B]
 
when i click ok on error window, it highlights this line in yellow:

[B]Sub Test()[/B]

and here is the error message:

[B]Compile Error
User-Defind type not defined[/B]

Thanks in advance

 

[pgc01]

Ah, much easier.

Your problem seem to be exactly what I've diagnosed before. I'm almost sure you did not set the reference to the library!!

Please refer to my first post in this thread to set the reference to the MsForms library and then try running this last code I posted again.

 

[makhtoot]

Hello dear friend
Thank you for the heads up, i activated the library the first time you told me to, but since it wasn't found in the library list, i had to select it manually from the system32 folder so it could load it in the excel 2007. i think that's why it was not activated and i had to load it again. it worked and it gave me a list of all the files i copied into clipboard. what next?

 

[makhtoot]

any news my friends?