Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Old February 7th 18, 09:19 PM posted to microsoft.public.excel.misc
external usenet poster
 
First recorded activity by ExcelBanter: Aug 2015
Posts: 142
Default 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

  #2   Report Post  
Old February 8th 18, 02:15 AM
Junior Member
 
First recorded activity by ExcelBanter: Jan 2018
Posts: 16
Default

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
  #3   Report Post  
Old February 8th 18, 05:15 AM posted to microsoft.public.excel.misc
external usenet poster
 
First recorded activity by ExcelBanter: Apr 2015
Posts: 958
Default 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
  #4   Report Post  
Old February 8th 18, 09:00 PM posted to microsoft.public.excel.misc
external usenet poster
 
First recorded activity by ExcelBanter: Aug 2015
Posts: 142
Default 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
  #5   Report Post  
Old February 8th 18, 09:01 PM posted to microsoft.public.excel.misc
external usenet poster
 
First recorded activity by ExcelBanter: Aug 2015
Posts: 142
Default 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


  #6   Report Post  
Old February 8th 18, 10:30 PM posted to microsoft.public.excel.misc
external usenet poster
 
First recorded activity by ExcelBanter: Aug 2015
Posts: 142
Default 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


  #7   Report Post  
Old February 8th 18, 10:32 PM posted to microsoft.public.excel.misc
external usenet poster
 
First recorded activity by ExcelBanter: Apr 2015
Posts: 958
Default 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
  #8   Report Post  
Old February 8th 18, 11:17 PM posted to microsoft.public.excel.misc
external usenet poster
 
First recorded activity by ExcelBanter: Apr 2015
Posts: 958
Default 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
  #9   Report Post  
Old February 9th 18, 01:30 AM posted to microsoft.public.excel.misc
external usenet poster
 
First recorded activity by ExcelBanter: Apr 2015
Posts: 958
Default 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
  #10   Report Post  
Old February 9th 18, 09:27 AM posted to microsoft.public.excel.misc
external usenet poster
 
First recorded activity by ExcelBanter: Aug 2015
Posts: 142
Default 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


Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
losing text from text box when pasting selection to word rallyworker Excel Discussion (Misc queries) 0 April 13th 07 08:44 AM
Cutting and pasting large variable text into a text box shape usin AJL Excel Programming 0 October 30th 06 07:54 PM
Pasting Text Lisa Excel Discussion (Misc queries) 1 May 10th 06 07:35 PM
Pasting text wnfisba Excel Discussion (Misc queries) 1 October 20th 05 07:23 PM
Pasting text from Excel to Word (macro) TT[_2_] Excel Programming 1 January 25th 05 02:23 AM


All times are GMT +1. The time now is 04:38 PM.

Powered by vBulletin® Copyright ©2000 - 2018, Jelsoft Enterprises Ltd.
Copyright 2004-2018 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Copyright © 2017