String vs LONG format

[Ruth]

Hey guys,

Any idea about how to solve this?

I want to copy some cells in EXCEL to a form in WORD. The problem is when
the cells in EXCEL contain some long paragraphs of text. I have done it in
the following
way:

If wdFF.Name = "Text3" Then
wdFF.Result = Range("T" & fila)
End If

Text3 is the field in the form where the info should be inserted. If I copy
and paste manually the info to the field in the form it works fine but with
the code above it does not work because it seems the object wdFF is expecting
to see only a string and the info received is much longer. Is there any way
to modify the format of the object wdFF from string to long?? or should I cut
and put together later on?

Thanks!!

 

[Nigel]

The result of the evaluation of Range("T" & fila) presumably returns a
string? What do you mean when you say "the info received is much longer",
a string is a string! Long vars refer to a numerical value not a string.

You could convert the value to a string using Cstr(Range("T" & fila)) but
you probably need some error checking to avoid error values, nulls etc.

 

[Ruth]

Thanks for your answer. In case I didn't explain it clear before I will try
to reformulate the problem...

- Cells T1...500 in EXCEL contains a paragraph of text of whatever lenght
(it is not known beforehand!) that has to be copied in a form in word.

- The field Text3 is the field in the WORD form where I want to copy the
text from the previous cell (there is one field for each cell).

Depending on the length of the content of the text in the cells T1...500,
the follwowing routine works or not ...

If the text in the EXCEL cell is long enough, the previous routine fails in
execution because wdFF.Result is expecting to see a String and seems the text
in the cell is too long for a string.
Any idea of how to avoid this problem?

 

[Nigel]

I am no expert on WORD, this appears to be an issue in that application not
Excel. If the text is already stored in an Excel cell then you have not
exceeded any limits, and string var will handle that length of string. So
it must be a limit in Word.

Have you tried the word programming newsgroup ?

Sorry I do not have a specific solution

--

Regards,
Nigel
(e-mail address removed)

 

[Andrew Taylor]

Hello again Ruth

It seems that the fact you;re doing it from Excel is irrelevant here -
you can't directly set a text formfield to more than 255 characters,
though you can do it "manually" within Word.

See this MS article, which gives a rather klunky workaround:
http://support.microsoft.com/default.aspx?scid=kb;en-us;Q163192
(using search and replace in the field)

See also
http://groups.google.co.uk/group/mi...read/thread/4b09b858aabe3412/695eaf7882ecba1c
which has a different method using copy & paste.


Andrew

 

[Ruth]

It seems to be a problem in EXCEL, and most likely the problem is that I
haven't defined something properly but I don't know exactly what.

 

[Nigel]

Looks like Andrew Taylor has put his finger on it. Its a WORD problem

--

Regards,
Nigel
(e-mail address removed)

 

[Ruth]

Hi Andrew

Thanks a lot for your help! I had a look to the links and I decided to go
for the second one but still struggling with some problems.

Case "Text3"
If Len(Range("T" & fila)) > 255 Then
wdFF.Result = Left$(Range("T" & fila), 250)
MyDataObj.SetText ""
MyDataObj.PutInClipboard
MyDataObj.SetText Mid$(Range("T" & fila), 256)
MyDataObj.PutInClipboard
Set rngx = wdFF.Range
rngx.GoTo
rngx.Select
With wordFile.Selection
.Collapse wdCollapseEnd
.Paste
End With
Else
wdFF.Result = Range("T" & fila)
End If


The situation now is that I get the 255 initial characters, it even clear
properly the clipboard and copy there the rest of the text but then I get an
error because it says that "Set rngx = wdFF.Range" use non compatible formats
(rngx is defined as a range). Any idea of what I coud do? Thanks!!

 

[Per Jessen]

Hi Andrew

Thanks a lot for your help! I had a look to the links and I decided to go
for the second one but still struggling with some problems.

Case "Text3"
                 If Len(Range("T" & fila)) > 255 Then
                  wdFF.Result = Left$(Range("T" & fila), 250)
                  MyDataObj.SetText ""
                  MyDataObj.PutInClipboard
                  MyDataObj.SetText Mid$(Range("T" & fila), 256)
                  MyDataObj.PutInClipboard
                  Set rngx = wdFF.Range
                  rngx.GoTo
                  rngx.Select
                  With wordFile.Selection
                    .Collapse wdCollapseEnd
                    .Paste
                  End With
                Else
                   wdFF.Result = Range("T" & fila)
                End If

The situation now is that I get the 255 initial characters, it even clear
properly the clipboard and copy there the rest of the text but then I get an
error because it says that "Set rngx = wdFF.Range" use non compatible formats
(rngx is defined as a range). Any idea of what I coud do?  Thanks!!








- Vis tekst i anførselstegn -

Hi again

Just a long shot, maybe this will work ?


Case "Text3"
If Len(Range("T" & fila)) > 255 Then
wdFF.Result = Left$(Range("T" & fila), 250) ' Shoulden´t it be
255?
MyDataObj.SetText ""
MyDataObj.PutInClipboard
DataString = Mid$(Range("T" & fila), 256)
With wdFF
.InsertAfter DataString
.Collapse wdCollapseEnd
End With
Else
wdFF.Result = Range("T" & fila)
End If

Regards,

Per

 

[Ruth]

Hi Per

Thanks for your answer!!
Still has the same problem. I get a runtime error 438, "OBject doesn't
support this property or method" when the execution goes through the sentence
With wdFF .Insert DataString. Just to remind that wdFF is defined as an
Object In wordFile.FormFields. Any idea of how to solve this and the reason
of the error?

 

[Per Jessen]

Hi Per

Thanks for your answer!!
Still has the same problem. I get a runtime error 438, "OBject doesn't
support this property or method" when the execution goes through the sentence
With wdFF  .Insert DataString. Just to remind that wdFF is defined as an
Object In wordFile.FormFields. Any idea of how to solve this and the reason
of the error?








- Vis tekst i anførselstegn -

Hi Ruth

Thanks for your reply. Unfortunately I can't find any solution for
your problem :-(

I don't think that wdFF as Object is the problem, rather we are not
using the right syntax to do what we want with the object.

Regards,

Per

 

[Per Jessen]

HiRuth

Thanks for your reply. Unfortunately I can't find any solution for
your problem :-(

I don't think that wdFF as Object is the problem, rather we are not
using the right syntax to do what we want with the object.

Regards,

Per- Skjul tekst i anførselstegn -

- Vis tekst i anførselstegn -

Hi Ruth

I have found a workaround, that will work if the text passed to your
Form won't be manipulated.

Insert at new Text Form Field next to the field named "Text1" and name
it "Text1_1", and use this code:

Case "Text1"

If Len(Range("T" & fila)) > 255 Then
wdFF.Result = Left$(Range("T" & fila), 250) ' Shoulden´t it be
255
DataString = Mid$(Range("T" & fila), 256)
wordFile.FormFields("Text1_1").Result = DataString
Else
wdFF.Result = Range("T" & fila)
End If

Regards,

Per

 

[Andrew Taylor]

You say "rngx is defined as a range" - does this mean
you have "Dim rngx as Range" in your Excel code?
That is not the same as a Range object in Word - you
should probably make "Dim rngx as Word.Range"

Andrew

 

[Ruth]

Hi Andrew,

Yes I did and it is not the same. Thanks for the help! Now seems to be
completly working.

Per, the problem with the approach that you suggest is that it is more than
510 characters then you have to include a third field and so on, ...

This is the code I finally used and it is working ....

If Len(Range("T" & fila)) > 255 Then
wdFF.Result = Left$(Range("T" & fila), 255)
MyDataObj.SetText ""
MyDataObj.PutInClipboard
MyDataObj.SetText Mid$(Range("T" & fila), 1)
MyDataObj.PutInClipboard
wordFile.Unprotect
Set rngx = wdFF.Range
rngx.GoTo
rngx.Select
rngx.Paste
wordFile.Protect Type:=2, NoReset:=1
Else
wdFF.Result = Range("T" & fila)
End If

Of course rngx not defined as an excel range!!

Well, thanks to all you guys for your help!!