Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 192
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  
Junior Member
 
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  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 1,182
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  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 192
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  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 192
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  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 192
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  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 1,182
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  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 1,182
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  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 1,182
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  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 192
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


  #11   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 1,182
Default Pasting text into VBA macro?

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

The purpose of my version is for efficiency:

1. It has no external dependancies;
-doesn't use the clipboard and so doesn't require a ref to Forms2.0
-it doesn't require using Macro Express

2. It inserts the picture wherever you decide before running the macro;
-gives you greater flexibility over using a hard-coded target

3. It allows you to specify the full path to the pic;
-encapsulates the process within a single app

4. The code is reusable in other projects;
-except for the selection of row27 (could be done manually
-allows you to do similar process in other projects

So unless this is a dedicated task project, generic code that's reusable is
always a better asset to have in you repertoire!

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.


Your PERSONAL.xls probably has a ref to the Forms2.0 lib by way of
Tools::References or it contains a Userform.

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion
  #12   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 192
Default Pasting text into VBA macro?

GS wrote:

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

The purpose of my version is for efficiency:

1. It has no external dependancies;
-doesn't use the clipboard and so doesn't require a ref to Forms2.0
-it doesn't require using Macro Express

2. It inserts the picture wherever you decide before running the macro;
-gives you greater flexibility over using a hard-coded target

3. It allows you to specify the full path to the pic;
-encapsulates the process within a single app

4. The code is reusable in other projects;
-except for the selection of row27 (could be done manually
-allows you to do similar process in other projects

So unless this is a dedicated task project, generic code that's reusable is
always a better asset to have in you repertoire!

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.


Your PERSONAL.xls probably has a ref to the Forms2.0 lib by way of
Tools::References or it contains a Userform.


Thanks, yes, that proved to be the reason.

I've archived your version filed for future use, but this is a very
specific project as you may have gathered from my illustrations.

Terry, East Grinstead, UK
  #13   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 1,182
Default Pasting text into VBA macro?

I've archived your version filed for future use, but this is a very
specific project as you may have gathered from my illustrations.


Yes.., I suspected that. I got another suspicion, though, that you might do
similar process[es] in other projects and so why the generic reusable code.

I'm also not a fan of using another program to do something Excel can do so my
projects are stand-alone and therefore self-sufficient. Nor am I a fan of
making projects that require unnecessary refs to external libs if not needed.

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion
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 09:56 AM.

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

About Us

"It's about Microsoft Excel"