ExcelBanter

ExcelBanter (https://www.excelbanter.com/)
-   Excel Discussion (Misc queries) (https://www.excelbanter.com/excel-discussion-misc-queries/)
-   -   Pasting text into VBA macro? (https://www.excelbanter.com/excel-discussion-misc-queries/453999-pasting-text-into-vba-macro.html)

Terry Pinnell[_4_] February 7th 18 08:19 PM

Pasting text into VBA macro?
 
This recorded VBA macro inserts a specific picture into A22 and resizes
it to 85% of its original size.

Sub InsertPicture_Gf4()
'
Range("A30").Select
ActiveSheet.Pictures.Insert( _

"C:\Users\terry\Dropbox\FinishedWalks\20170809Day0 2Trevone-Porthcothan-J-u502-red-m8.8-Gf4-Elev.jpg"
_
).Select
Selection.ShapeRange.ScaleHeight 0.85, msoFalse, msoScaleFromTopLeft
Application.CommandBars("Format Object").Visible = False
Rows("27:27").Select
End Sub

But how can I edit that so that the filename
"C:\Users\terry\Dropbox\FinishedWalks\20170809Day0 2Trevone-Porthcothan-J-u502-red-m8.8-Gf4-Elev.jpg"
is replaced by whatever text is on the clipboard please?

Terry, East Grinstead, UK

killme2008 February 8th 18 01:15 AM

Use function "GetClipBoardString" to replace your jpg's address.



Private Function GetClipBoardString() As String
On Error Resume Next
Dim MyData As New DataObject
GetClipBoardString = ""
MyData.GetFromClipboard
GetClipBoardString = MyData.GetText
Set MyData = Nothing
End Function

GS[_6_] February 8th 18 04:15 AM

Pasting text into VBA macro?
 
This recorded VBA macro inserts a specific picture into A22 and resizes
it to 85% of its original size.

Sub InsertPicture_Gf4()
'
Range("A30").Select
ActiveSheet.Pictures.Insert( _

"C:\Users\terry\Dropbox\FinishedWalks\20170809Day0 2Trevone-Porthcothan-J-u502-red-m8.8-Gf4-Elev.jpg"
_
).Select
Selection.ShapeRange.ScaleHeight 0.85, msoFalse, msoScaleFromTopLeft
Application.CommandBars("Format Object").Visible = False
Rows("27:27").Select
End Sub

But how can I edit that so that the filename
"C:\Users\terry\Dropbox\FinishedWalks\20170809Day0 2Trevone-Porthcothan-J-u502-red-m8.8-Gf4-Elev.jpg"
is replaced by whatever text is on the clipboard please?

Terry, East Grinstead, UK


I'm curious how the file path gets in the ClipBoard...

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

Terry Pinnell[_4_] February 8th 18 08:00 PM

Pasting text into VBA macro?
 
GS wrote:

This recorded VBA macro inserts a specific picture into A22 and resizes
it to 85% of its original size.

Sub InsertPicture_Gf4()
'
Range("A30").Select
ActiveSheet.Pictures.Insert( _

"C:\Users\terry\Dropbox\FinishedWalks\20170809Day0 2Trevone-Porthcothan-J-u502-red-m8.8-Gf4-Elev.jpg"
_
).Select
Selection.ShapeRange.ScaleHeight 0.85, msoFalse, msoScaleFromTopLeft
Application.CommandBars("Format Object").Visible = False
Rows("27:27").Select
End Sub

But how can I edit that so that the filename
"C:\Users\terry\Dropbox\FinishedWalks\20170809Day0 2Trevone-Porthcothan-J-u502-red-m8.8-Gf4-Elev.jpg"
is replaced by whatever text is on the clipboard please?

Terry, East Grinstead, UK


I'm curious how the file path gets in the ClipBoard...


Hi Garry,

From a Macro Express Pro macro. I use a command to copy a variable
called FullTrackName (derived in earlier stages) to the clipboard.

I'm struggling to apply killme2008's advice. And would welcome some
further advice please. How exactly do I insert that specified JPG
picture at cell A30 of my (active) worksheet, TrackData-New.xlsm?

I was assuming it would be a relatively simple matter of replacing that
full filename with a line or two of code that would in effect PASTE it
into the simple macro I posted, and hence perform the insertion.


Terry, East Grinstead, UK

Terry Pinnell[_4_] February 8th 18 08:01 PM

Pasting text into VBA macro?
 
killme2008 wrote:


Use function "GetClipBoardString" to replace your jpg's address.



Private Function GetClipBoardString() As String
On Error Resume Next
Dim MyData As New DataObject
GetClipBoardString = ""
MyData.GetFromClipboard
GetClipBoardString = MyData.GetText
Set MyData = Nothing
End Function


Thanks, much appreciated. But I'm still very much a VBA novice so could
you help me implement that in my specific macro please?

Terry, East Grinstead, UK

Terry Pinnell[_4_] February 8th 18 09:30 PM

Pasting text into VBA macro?
 
Terry Pinnell wrote:

GS wrote:

This recorded VBA macro inserts a specific picture into A22 and resizes
it to 85% of its original size.

Sub InsertPicture_Gf4()
'
Range("A30").Select
ActiveSheet.Pictures.Insert( _

"C:\Users\terry\Dropbox\FinishedWalks\20170809Day0 2Trevone-Porthcothan-J-u502-red-m8.8-Gf4-Elev.jpg"
_
).Select
Selection.ShapeRange.ScaleHeight 0.85, msoFalse, msoScaleFromTopLeft
Application.CommandBars("Format Object").Visible = False
Rows("27:27").Select
End Sub

But how can I edit that so that the filename
"C:\Users\terry\Dropbox\FinishedWalks\20170809Day0 2Trevone-Porthcothan-J-u502-red-m8.8-Gf4-Elev.jpg"
is replaced by whatever text is on the clipboard please?

Terry, East Grinstead, UK


I'm curious how the file path gets in the ClipBoard...


Hi Garry,

From a Macro Express Pro macro. I use a command to copy a variable
called FullTrackName (derived in earlier stages) to the clipboard.

I'm struggling to apply killme2008's advice. And would welcome some
further advice please. How exactly do I insert that specified JPG
picture at cell A30 of my (active) worksheet, TrackData-New.xlsm?

I was assuming it would be a relatively simple matter of replacing that
full filename with a line or two of code that would in effect PASTE it
into the simple macro I posted, and hence perform the insertion.


Terry, East Grinstead, UK


I finally got this working:

Sub InsertPicture_Gf4()
'
Dim MyData As DataObject
Dim strClip As String

Set MyData = New DataObject
MyData.GetFromClipboard
strClip = MyData.GetText
Range("A30").Select
ActiveSheet.Pictures.Insert(strClip).Select
Selection.ShapeRange.ScaleHeight 0.85, msoFalse, msoScaleFromTopLeft
Application.CommandBars("Format Object").Visible = False
Rows("27:27").Select
End Sub

HOWEVER, it will only work if I place it in Personal.xlsb. If I put it
in the worksheet in which I want to work I get this error:
"Compile error:
User-defined type not defined".

I did observe the obscure instruction I had found, namely that for this
to work you must "Have at least one UserForm in your project".
https://www.dropbox.com/s/clwdmrz4z1...rking.jpg?dl=0

Anyone know the reason for this failure please?

Terry, East Grinstead, UK



GS[_6_] February 8th 18 09:32 PM

Pasting text into VBA macro?
 
Try...

Option Explicit


Sub InsertPicture_Gf4()
'Insert a picture at the active cell
ActiveCell.Pictures.Insert(Get_ClipboardText).Sele ct
Selection.ShapeRange.ScaleHeight 0.85, msoFalse, msoScaleFromTopLeft
Application.CommandBars("Format Object").Visible = False
Rows("27:27").Select
End Sub

Private Function Get_ClipboardText$()
' Requires a ref to Microsoft Forms 2.0 Library

Dim doData As New DataObject
On Error Resume Next
doData.GetFromClipboard: Get_ClipboardText = doData.GetText
Set doData = Nothing
End Function

...but why use ME? Why not browse for a specific file same as
InsertPictureFromFile does?

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

GS[_6_] February 8th 18 10:17 PM

Pasting text into VBA macro?
 
To implement InsertPictureFromFile requires no dependancy on the Forms lib nor
the clipboard...

Option Explicit


Sub InsertPictureFromFile()
'Inserts picture-from-file at the active cell
Dim sFile$

sFile = Get_FileToOpen: If sFile = "" Then Exit Sub
ActiveSheet.Pictures.Insert(sFile).Select
Selection.ShapeRange.ScaleHeight 0.85, msoFalse, msoScaleFromTopLeft
'Assumes the follow 'custom' toolbar exists
Application.CommandBars("Format Object").Visible = False
Rows("27:27").Select
End Sub

Function Get_FileToOpen$(Optional FileTypes$)
If FileTypes = "" Then FileTypes = "All Files ""*.*"", (*.*)"

Dim vFile
vFile = Application.GetOpenFilename(FileTypes)
Get_FileToOpen = IIf(vFile = False, "", vFile)
End Function

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

GS[_6_] February 9th 18 12:30 AM

Pasting text into VBA macro?
 
Typo...

Function Get_FileToOpen$(Optional FileTypes$)
If FileTypes = "" Then FileTypes = "All Files ""*.*"", *.*"
Dim vFile
vFile = Application.GetOpenFilename(FileTypes)
Get_FileToOpen = IIf(vFile = False, "", vFile)
End Function

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

Terry Pinnell[_4_] February 9th 18 08:27 AM

Pasting text into VBA macro?
 
GS wrote:

Typo...

Function Get_FileToOpen$(Optional FileTypes$)
If FileTypes = "" Then FileTypes = "All Files ""*.*"", *.*"
Dim vFile
vFile = Application.GetOpenFilename(FileTypes)
Get_FileToOpen = IIf(vFile = False, "", vFile)
End Function


Thanks Garry. I'm using your edited version below:

Sub InsertPictureFromFile()
'Inserts picture-from-file at the active cell
Dim sFile$

sFile = Get_FileToOpen: If sFile = "" Then Exit Sub
ActiveSheet.Pictures.Insert(sFile).Select
Selection.ShapeRange.ScaleHeight 0.85, msoFalse, msoScaleFromTopLeft
'Assumes the follow 'custom' toolbar exists
Application.CommandBars("Format Object").Visible = False
Rows("27:27").Select
End Sub

Function Get_FileToOpen$(Optional FileTypes$)
If FileTypes = "" Then FileTypes = "All Files ""*.*"", *.*"
Dim vFile
vFile = Application.GetOpenFilename(FileTypes)
Get_FileToOpen = IIf(vFile = False, "", vFile)
End Function

But instead of immediately pasting the image from the text on the
clipboard (into fixed cell A30) it brings up a browser dialog.

I'm happy enough using my fully working macro below (based on my
trial/error adaptations from killme2008's), but I'm keen to know why
(unlike yours) it fails when placed outside PERSNAL.xlsb.

Sub InsertPicture_Gf4()
'
Dim MyData As DataObject
Dim strClip As String

Set MyData = New DataObject
MyData.GetFromClipboard
strClip = MyData.GetText
Range("A30").Select
ActiveSheet.Pictures.Insert(strClip).Select
Selection.ShapeRange.ScaleHeight 0.85, msoFalse, msoScaleFromTopLeft
Application.CommandBars("Format Object").Visible = False
Rows("27:27").Select
End Sub

Terry, East Grinstead, UK


All times are GMT +1. The time now is 03:13 PM.

Powered by vBulletin® Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
ExcelBanter.com