Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
losing text from text box when pasting selection to word | Excel Discussion (Misc queries) | |||
Cutting and pasting large variable text into a text box shape usin | Excel Programming | |||
Pasting Text | Excel Discussion (Misc queries) | |||
Pasting text | Excel Discussion (Misc queries) | |||
Pasting text from Excel to Word (macro) | Excel Programming |