Jump to content

Sky Slate Blueberry Blackcurrant Watermelon Strawberry Orange Banana Apple Emerald Chocolate
Photo

Remove linebreak after copying Excel


  • Please log in to reply
13 replies to this topic
TheDewd
  • Members
  • 835 posts
  • Last active: Yesterday, 06:13 PM
  • Joined: 28 Mar 2010
I'm trying to copy a cell from an excel spreadsheet to a variable by first copying it to the clipboard. When selecting the cell with the mouse, and then pressing CTRL+C, it copies the value but it has a line break afterward. How can I trim the break, or "enter"?

clipboard = ; Empty Clipboard
Send ^c
ClipWait ; Wait
colorid:=clipboard ; Set colorid variable

The result is as follows:

"Black
"

instead of

"Black"

Notice that I have put the ending quotation mark from the first example below the color to show the line break.

sinkfaze
  • Moderators
  • 6365 posts
  • Last active:
  • Joined: 18 Mar 2008
In terms of your example:

clipboard = ; Empty Clipboard 
Send ^c 
ClipWait ; Wait 
[color=red]clipboard=%clipboard%[/color] ; converts to plain text, see http://www.autohotkey.com/docs/misc/Clipboard.htm
colorid:=clipboard ; Set colorid variable

But what version of AHK are you running? There are slightly better ways to set/extract data from Excel than manual calling of the Clipboard.

TheDewd
  • Members
  • 835 posts
  • Last active: Yesterday, 06:13 PM
  • Joined: 28 Mar 2010
Your solution didn't solve my issue. I'm currently using AutoHotkey, file version 1.0.48.5

What alternate ways would you recommend to copy values from Excel cells?

sinkfaze
  • Moderators
  • 6365 posts
  • Last active:
  • Joined: 18 Mar 2008
You'll need to create a Lib folder in your AutoHotkey directory (see here for more information), then download the COM Standard Library and place the files from the zip in the Lib folder. Then you can run this when you have selection box around the cell you want:

^f::	; example hotkey
ControlGetFocus, out, ahk_class XLMAIN ; check to make sure a cell isn't being edited, will cause problems with COM
if	(out="EXCEL61") {	; if the cell edit control has focus
	MsgBox, 48, Warning, A cell is currently being edited, please exit the cell and try again.
	return
}
xl :=	COM_ActiveObject("Excel.Application")	; creates a reference to the active instance of Excel
colorid :=	COM_Invoke(xl,"Selection.Value")	; retrieves the value of the cell without the linefeeds
COM_Release(xl)	; releases the reference, must be done manually in this way in the older version of AHK
return

If the formatting from the cell is causing issues you may try an alternate method to retrieve the cell's contents:

colorid :=	COM_Invoke(xl,"Selection.[color=red]Formula[/color]")


a4u
  • Guests
  • Last active:
  • Joined: --
colorid:=RegExReplace(clipboard,"\R")


wooly_sammoth
  • Members
  • 644 posts
  • Last active: Jul 03 2013 08:08 PM
  • Joined: 12 May 2009
While I would agree with sinkfaze and a4u that COM is the best and most reliable way to interact with Excel, it was quite a long while before I could wrap my head around what COM even was let alone how to use it.

The simplest answer to your question is to use the StringReplace command to get rid of the Line Break
clipboard = ; Empty Clipboard
Send ^c
ClipWait ; Wait
StringReplace, ColorID, ClipBoard, `r`n, ,A ;you may need to experiment with different search text as i can't remember for certain what Excel adds to the cells
MsgBox, %ColorID%
Return

While this code will (with maybe some experimentation)give you a result, it will be worth your while to follow sinkfazes answers and implement some COM. (It revolutionised my main project when I finally felt up to tackling it.)

TheDewd
  • Members
  • 835 posts
  • Last active: Yesterday, 06:13 PM
  • Joined: 28 Mar 2010

You'll need to create a Lib folder in your AutoHotkey directory (see here for more information), then download the COM Standard Library and place the files from the zip in the Lib folder. Then you can run this when you have selection box around the cell you want:

^f::	; example hotkey
ControlGetFocus, out, ahk_class XLMAIN ; check to make sure a cell isn't being edited, will cause problems with COM
if	(out="EXCEL61") {	; if the cell edit control has focus
	MsgBox, 48, Warning, A cell is currently being edited, please exit the cell and try again.
	return
}
xl :=	COM_ActiveObject("Excel.Application")	; creates a reference to the active instance of Excel
colorid :=	COM_Invoke(xl,"Selection.Value")	; retrieves the value of the cell without the linefeeds
COM_Release(xl)	; releases the reference, must be done manually in this way in the older version of AHK
return

If the formatting from the cell is causing issues you may try an alternate method to retrieve the cell's contents:

colorid :=	COM_Invoke(xl,"Selection.[color=red]Formula[/color]")


I'm receiving an error. "Error: call to nonexistent function." "Specifically: COM_ActiveObject ("Excel.Application")"

Line: xl := COM_ActiveObject("Excel.Application")

a4u
  • Guests
  • Last active:
  • Joined: --
sinkfaze - your AHK Basic COM is getting rusty :wink:
xl := COM_[color=red]Get[/color]ActiveObject("Excel.Application")


TheDewd
  • Members
  • 835 posts
  • Last active: Yesterday, 06:13 PM
  • Joined: 28 Mar 2010
I'm really sorry to keep bothering you guys with this. I am receiving the error below:

COM Error Notifcation

Function Name: "Selection"
ERROR: The COM Object may not be a valid Dispatch Object!
First Ensure that COM Library has been initialized through COM_Init().
()

Will Continue
YES / NO

I made the adjustment pointed out by a4u.

Code:
#NoEnv  ; Recommended for performance and compatibility with future AutoHotkey releases.
SendMode Input  ; Recommended for new scripts due to its superior speed and reliability.
#SingleInstance Force ; Allow only one running instance of script.
#Persistent ; Keep script running until closed.
#Include C:\Program Files\AutoHotkey\Lib\COM.ahk

^f::
ControlGetFocus, out, ahk_class XLMAIN
if   (out="EXCEL61") {
   MsgBox, 48, Warning, A cell is currently being edited, please exit the cell and try again.
   return
}
xl := COM_GetActiveObject("Excel.Application")
colorid :=   COM_Invoke(xl,"Selection.Value")
COM_Release(xl)
return

Also, I'm not sure if it matters, but I'm using Microsoft Excel 2007.

sinkfaze
  • Moderators
  • 6365 posts
  • Last active:
  • Joined: 18 Mar 2008

sinkfaze - your AHK Basic COM is getting rusty



I think we both bit it on this trip:

COM Error Notifcation

Function Name: "Selection"
ERROR: The COM Object may not be a valid Dispatch Object!
[color=red]First Ensure that COM Library has been initialized through COM_Init().[/color]

Will Continue
YES / NO


Try this:
#NoEnv  ; Recommended for performance and compatibility with future AutoHotkey releases.
SendMode Input  ; Recommended for new scripts due to its superior speed and reliability.
#SingleInstance Force ; Allow only one running instance of script.
#Persistent ; Keep script running until closed.
#Include C:\Program Files\AutoHotkey\Lib\COM.ahk

^f::
ControlGetFocus, out, ahk_class XLMAIN
if   (out="EXCEL61") {
   MsgBox, 48, Warning, A cell is currently being edited, please exit the cell and try again.
   return
}
[color=red]COM_CoInitialize()[/color]
xl := COM_GetActiveObject("Excel.Application")
colorid :=   COM_Invoke(xl,"Selection.Value")
COM_Release(xl), [color=red]COM_CoUninitialize()[/color]
MsgBox % colorid
return


TheDewd
  • Members
  • 835 posts
  • Last active: Yesterday, 06:13 PM
  • Joined: 28 Mar 2010
The code is now working and does exactly what I need. This problem has been solved. Thanks to all of you, and sinkfaze.

sinkfaze
  • Moderators
  • 6365 posts
  • Last active:
  • Joined: 18 Mar 2008
Just keep in mind that if/when you decide to move to AHK_L the COM call is even more simplified since COM does not need to be "turned on or off" via COM_CoInitialize()/CoUnInitialize(), you can in fact reduce the whole thing to one line:

^f:: 
ControlGetFocus, out, ahk_class XLMAIN 
if   (out="EXCEL61") { 
   MsgBox, 48, Warning, A cell is currently being edited, please exit the cell and try again. 
   return 
} 
[color=red]colorid := ComObjActive("Excel.Application").Selection.Value[/color]
MsgBox % colorid
return

The syntax is more natural in that it is more in line with the Visual Basic for Applications (VBA) code that it is utilizing to make it work. You can also have a look at the MS Office Automation Functions thread if you find you're in need of additional functionality in Excel or most other Microsoft Office applications as well.

RJ1
  • Members
  • 1 posts
  • Last active: Sep 03 2013 03:48 AM
  • Joined: 27 Aug 2013

If you are just looking to trim the line feed off the clipboard content (like what you get after copying excel)

StringTrimRight, MyVar1, clipboard, 2

should store your string to MyVar1



Daniel Bonnell
  • Members
  • 37 posts
  • Last active: Mar 26 2014 12:23 PM
  • Joined: 14 Nov 2012

Thank you RJ11 - that is the simplest method to do what needs to be done. I deal with that problem often with the clipboard contents and never thought of simply trimming the last 2 characters (and they are always a LF CR).