Help - Can't Print Large String (~500 Chars) to any Excel 95 Object
Martin Daoust
Try loadin your string into a textbox. You have to do it 255 charactera
at a time (record a macro doing it)
Martin Daoust
Carl 0rion Scarlett wrote:
>
> Help me, please help me, oh god help me.
>
> I've written a macro to concatenate the values of a selected range into
> a Excel VBA variable, and I want to write it out somewhere I can copy
> the result to the clipboard. I can't put it in a cell because it only
> stores the first 255 characters. I can't work out how to write it to a
> module sheet. I've tried writing it to a label in a dialog sheet, but
> it truncates at about 300 charaters for some reason.
>
> Is there anywhere I can write such a large string variable?
>
>
> Carl.
> ______________________________________________________________________
John Murray
I am assuming that you are writing this in a 32b
environment....below is a solution to put the data in the string
variable directly into the clipboard for you....
'Put the following lines into the declare section of the module
'this is at the top before any sub's or functions. Be sure
'and remove any new lines that are created
Declare Function GlobalUnlock Lib "kernel32" (ByVal hMem As Long) As
Long
Declare Function GlobalLock Lib "kernel32" (ByVal hMem As Long) As
Long
Declare Function GlobalAlloc Lib "kernel32" (ByVal wFlags As Long,
ByVal dwBytes As Long) As Long
Declare Function CloseClipboard Lib "User32" () As Long
Declare Function OpenClipboard Lib "User32" (ByVal hwnd As Long) As
Long
Declare Function EmptyClipboard Lib "User32" () As Long
Declare Function lstrcpy Lib "kernel32" (ByVal lpString1 As Any, ByVal
lpString2 As Any) As Long
Declare Function SetClipboardData Lib "User32" (ByVal wFormat As Long,
ByVal hMem As Long) As Long
Public Const GHND = &H42
Public Const CF_TEXT = 1
Public Const MAXSIZE = 4096
'now paste the following code into your module..this will create
'a new function
Function WriteToClipBoard(MyString As String)
Dim l_MemHndl As Long
Dim l_MemPtr As Long
Dim l_ClipHdl As Long
Dim x As Long
WriteToClipBoard = False
l_MemHndl = GlobalAlloc(GHND, Len(MyString) + 1)
l_MemPtr = GlobalLock(l_MemHndl)
l_MemPtr = lstrcpy(l_MemPtr, MyString)
GlobalUnlock (l_MemHndl)
If OpenClipboard(0&) = 0 Then
WriteToClipBoard = False
Exit Function
End If
x = EmptyClipboard()
l_ClipHdl = SetClipboardData(CF_TEXT, l_MemHndl)
x = CloseClipboard
If x Then WriteToClipBoard = True
End Function
Now you can call this function (passing it the variable that you want
to put into the clipboard) and whala...it will be there.
WriteToClipBoard(myresult)
One note of caution..using API's can cause problems if you arent
careful.I highly suggest that you at least declare the variable
myresult as a string..this code has been working fine for me for some
time; however, if you change variable types or the declarations it
could cause your system to become unstable...
Let me know if you have any questions...
John
Carl 0rion Scarlett <scar...@cosmos.curtin.edu.au> wrote:
>Help me, please help me, oh god help me.
>
>I've written a macro to concatenate the values of a selected range into
>a Excel VBA variable, and I want to write it out somewhere I can copy
>the result to the clipboard. I can't put it in a cell because it only
>stores the first 255 characters. I can't work out how to write it to a
>module sheet. I've tried writing it to a label in a dialog sheet, but
>it truncates at about 300 charaters for some reason.
>
>Is there anywhere I can write such a large string variable?
>
>Here is the code :
>
>
>''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
>
>
>'Macro to join the contents of a cell together and place the results
>somewhere
>'Carl Scarlett 18.03.97
>
>Sub Range_Join()
>
>'Find start and end of range
>
> 'Get range
> myrange = ActiveWindow.RangeSelection.Address(rowabsolute:=False)
>
> 'Work out start and end
> dee = InStr(myrange, ":")
> mystart = Left(myrange, dee - 1)
> myend = Mid(myrange, dee + 1, Len(myrange) - dee)
>
> rowstart = Range(mystart).Row
> rowend = Range(myend).Row
> colstart = Range(mystart).Column
> colend = Range(myend).Column
>
>'Join cells in range
> finished = False
> myresult = ""
>
> For myrow = rowstart To rowend
> For mycol = colstart To colend
>
> myresult = myresult + Columns(mycol).Rows(myrow).Value
>
> Next mycol
> Next myrow
>
>'Place result in first cell
> Columns(colstart).Rows(rowstart).Value = myresult
>
>End Sub
>
>
>''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
>
>
>Please email me as well as post a reply...my news server is very dicky,
>and I'm desperate!
>
>Carl.
> ______________________________________________________________________
>
> Carl 0rion Scarlett
> Finance Systems Analyst/Programmer, " May the road rise to
> Management Information Systems (MIS), meet you, may the
> Curtin University, Western Australia. wind be at your back. "
> -- Irish Blessing
> Phone : (09) 351 3987
> FAX : (09) 351 3982
> Email : scar...@cosmos.curtin.edu.au
> Web : http://www.curtin.edu.au/curtin/dept/mis/staff/extras/carl.htm
> ______________________________________________________________________