Issue in the VBA macro of MS word.

[priby]

Greetings to all,

I am new to VB. In my VBA macro I am trying to access a data source(a text file) using the below code snippet.



Dim TESTING As String
..
..

TESTING = "M_1111" 'Accesing the value of M_1111 from data source
Selection.TypeText Text:=Chr(13)
Selection.TypeText Text:="TESTING"
Selection.TypeText Text:=Chr(13)

Selection.TypeText Text:=.DataFields(TESTING)
Selection.TypeText Text:=Chr(13)
Selection.TypeText Text:="END OF TESTING"
Selection.TypeText Text:=Chr(13)

This works fine, if the value of M_1111 contains less number of characters. But if the value of M_1111 contains more number of characters, the value gets truncated upon accessing.

I don't know why this truncation is happening. Is there any maximum for the number of characters that can be accessed?

Any help appreciated.

Thanks
priby

[fumei]

"This works fine, if the value of M_1111 contains less number of characters. But if the value of M_1111 contains more number of characters, the value gets truncated upon accessing."

Huh? Not following. "less"? "more"? Than what?

BTW: without going into why you should not use Selection if possible, you can put your text as one instruction, rather than seven.

SEVEN instructions:
Selection.TypeText Text:=Chr(13)
Selection.TypeText Text:="TESTING"
Selection.TypeText Text:=Chr(13)
Selection.TypeText Text:=.DataFields(TESTING)
Selection.TypeText Text:=Chr(13)
Selection.TypeText Text:="END OF TESTING"
Selection.TypeText Text:=Chr(13)

ONE instruction:
Selection.TypeText Text:=Chr(13) & "TESTING" & _
Chr(13) & .DataFields(TESTING) & Chr(13) & _
"END OF TESTING" & Chr(13)

You may want to use vbCrLf rather than Chr(13) - although there may be a reason why you use Chr(13).

I also have to wonder about the .DataFields(TESTING). Is this part of a larger WITH statement?

[priby]

Hi Fumei,

Sorry if the explanation which gave was not clear.

When I run the code, I am able to get the value of M_1111 from the data source(which is a text file) . But the problem is that, if the value of M_1111 has more number of characters(more than around 200 characters), the value gets truncated.

Thanks
priby

[fumei]

Still not following. Please be very specific. Why are you typing - which is what Selection.TypeText is - the string from the text file? Let me clear. You are not getting the value, you are typing the value.

Put the value into a string, then insert the string.

Dim yadda As String
yadda = .DataFields(TESTING)

Now do something with the string variable yadda.

Still do not know what .DataFields is, but..shrug...there you go.

[gwkenny]

Is there any maximum for the number of characters that can be accessed?

From the VBA help files regarding String Data Type: A variable-length string can contain up to approximately 2 billion (2^31) characters.

Given the fact that the code you cited does not concern accessing the data, no one can help you. :(

[fumei]

In other words, the number of characters is NOT the issue. You are doing something else incorrectly, and since you have not posted fully...we have no idea what that may be.

[priby]

Hi,

I am not able to attach the word document(with macro) and the data source that I used becuase their size exceed the forum's limit.

The following is the macro that I used. (Complete):



Public Function SpecialProc() As String
Dim TESTING As String
Dim Str1 As String
On Error GoTo ErrorHandler
Selection.HomeKey unit:=wdStory
With ActiveDocument.MailMerge.DataSource


TESTING = "M_1111"
Str1 = .DataFields(TESTING)
Selection.TypeText Text:=Chr(13) & "TESTING" & _
Chr(13) & .DataFields(TESTING) & Chr(13) & _
"END OF TESTING" & Chr(13)
Selection.TypeText Text:=Chr(13) & "TESTING" & _
Chr(13) & Str1 & Chr(13) & _
"END OF TESTING" & Chr(13)


End With
SpecialProc = "OK"
Exit Function
ErrorHandler:
SpecialProc = "Error " & Err.Number & ":" & Err.Description
End Function





The following is the data source(a simple text file with the following contents) that I used:





M_1111,
"Visual Basic (VB) is a programming environment from Microsoft in which a programmer uses a graphical user interface to choose and modify preselected sections of code written in the BASIC programming language. Visual Basic is also widely used to write working programs. Microsoft says that there are at least 3 million developers using Visual Basic.",


The problem is that when the value from the data source is more than 255 characters, the value gets truncated.

For example: the value of M_1111 in the data source exceeds 255 characters and it is getting truncated.

Thanks
priby

[gwkenny]

Sorry, spent 20 minutes understanding what you are doing, recreating it, and I experience no text cropping.

Everything is fine.

If I change your function to a subroutine, it works. If I call your function from a one line procedure, it works.

Word 2003, sp2

[priby]

Are u getting the entire value of M_1111 without any cropping?

Can u please tell me how you had run the macro?

(I am using word2003. I just attached the data source to word document and inside the VBA editor for macro, I pressed F5 for running .)

[priby]

Hi gwkenny,
I checked the word document you attached. I see that the value of M_1111 is cropped.

The actual value of M_1111 in datasource is
Visual Basic (VB) is a programming environment from Microsoft in which a programmer uses a graphical user interface to choose and modify preselected sections of code written in the BASIC programming language. Visual Basic is also widely used to write working programs. Microsoft says that there are at least 3 million developers using Visual Basic.

But after running the macro, it is displayed as
Visual Basic (VB) is a programming environment from Microsoft in which a programmer uses a graphical user interface to choose and modify preselected sections of code written in the BASIC programming language. Visual Basic is also widely used to write work

That is the value of M_1111 is getting truncated

[fumei]

PLEASE, use the underscore character to break up code lines!

Thanks.

[fumei]

And, BTW: it is truncated for me as well.

[gwkenny]

Sorry, just a quick note to say I'm an idiot and I can't see the forest for the trees.

Yes, I have the same problem too. If I just LOOKED I would have seen it.

I tried a quick search of the MS Knowledgebase and came up blank. I've got to run, but hopefully we can collectively think of something but it doesn't look good :(

A plain mail merge brings the entire text, accessing through VB with mailmerge datasource seems to truncate it automatically to 255. I'll try and test with different data formats as well.

[Nelviticus]

I think that rather than trying to come up with a way to get around the 'VBA truncating mail merge fields' issue, you need to take a step back and decide what you're trying to achieve in the first place. Maybe a completely different method would be easier.

[fumei]

Da-da!

[gwkenny]

I suspect the best solution is Nelviticus's. There's multiple ways to read in a text file, and since it's in a data format, it should be relatively easy to parse out the information needed.

But this bugs me. There should be no need to code around these 'quirks' in Microsoft Products. And they are not that infrequent.

Yesterday I could read a range of cells in Excel directly into a variant array. I do some calculations and put my results in a variant results array. The results array can NOT be copied back into a range of cells, though my original variant array can. If I use redim preserve on my variant array, Excel just goes BLAH. I could read each individual element. Values are all fine, but when I copy it into a range of cells (with the correct range size) I just get 40k cells of the first element!!!!. So I got to do it the hard way and loop through all 40k elements and put them into cells one by one :(

Way to go Microsoft!!!!

[Tinbendr]

If your data source in in Excel, there seems to be a known problem.

http://homepage.swissonline.ch/cindymeister/mergfaq.htm#XLData
Link2 (http://groups.google.com/group/microsoft.public.word.mailmerge.fields/browse_thread/thread/ea8156cbaa8be9aa/f595db95b4e35c37?hl=en&lnk=gst&q=DataFields+limitation#f595db95b4e35c37)