From Excel to word. Number miles problem

[mikel1369]

I am trying to develop a small personal project related to Excel.
I have developed a macro that will create a word file, with the following code.

Select Code copy to clipboard
Dim datos(0 To 1, 0 To 600) As String '(columna,fila)

Application.Calculation = xlCalculationManual
Application.EnableEvents = False
ActiveSheet.DisplayPageBreaks = False

patharch = ThisWorkbook.Path & "\plantilla.dotx"
Set objWord = CreateObject("Word.Application")
objWord.Visible = True
objWord.Documents.Add Template:=patharch, NewTemplate:=False, DocumentType:=0

Count = 19
aux = 0
aux1 = 0
For j = 0 To 1
For k = 0 To 1200

If Hoja5.Cells(k + 1, Count) <> "" Then
datos(aux, aux1) = Hoja5.Cells(k + 1, Count)
aux1 = aux1 + 1
End If

Next k
Count = Count - 1
k = 0
aux = aux + 1
aux1 = 0
Next j

For I = 0 To UBound(datos, 2)

textobuscar = datos(0, I)
objWord.Selection.Move 6, -1
objWord.Selection.Find.Execute FindText:=textobuscar

If IsNumeric(textobuscar) = False Then

While objWord.Selection.Find.found = True
objWord.Selection.Text = datos(1, I) 'texto a reemplazar
objWord.Selection.Move 6, -1
objWord.Selection.Find.Execute FindText:=textobuscar
Wend
End If
Next I
The data that will be in datos are the following. The date and the numbers will be in the first column and pag_fecha_inicio in the second.

miércoles, 02 de marzo de 2016 [pag_fecha_inicio]
martes, 15 de marzo de 2016 [pag_fecha_fin]
395.500 [pag_promedio_diario_]
555.200 [pag_promedio_diario_mobile]
1.045.400 [pag_promedio_diario_entreseman]
734.700 [pag_promedio_diario_findesemana]
13.304.600 [pag_totales ]
5.537.100 [pag_totales_pc]
7.767.500 [pag_totales_mobile]


The problem comes when I export all the data to word since I want to export not just the numbers but also the point between the numbers.

I have the following formula { MERGEFIELD [pag_promedio_diario_] \# "##0,00"} to try to achieve that goal, however the result is the following «395500». Not the number 395.500. In addition, it appears the << >>.

Could someone help me?

I can't find a solution.

Thanks!!!
Miguel.

[macropod]

There is nothing about your process to indicate why you're using mergefields at all. Furthermore, without the context of your document, it's difficult to follow what you're doing. There is also rarely any reason to use Selection to populate a document.

PS: When posting code, please use the code tags, indicated by the # button on the posting menu. Without them, your code loses much of whatever structure it had.

[mikel1369]

Hi Paul,

First of all, thank you very much for your fast responde.
Please, find attached a example of the data that Iam using (image1). In the S column is the label that I will search in the word document and in the R column, the one that will remplace the label.

Attachment 451946

In the second one, there is a example of the template that I am using. There, it is where I want the numbers be like 1.500.000

Attachment 451947

The code is the one above. I have added some comments to clarify what I am doing.

Please Login or Register  to view this content.

Thank you very much!

[macropod]

As I said in my previous reply:

There is nothing about your process to indicate why you're using mergefields at all. Furthermore, without the context of your document, it's difficult to follow what you're doing. There is also rarely any reason to use Selection to populate a document.

Your code appears to be building a two-dimension array, then using that for a series of Find/Replace operations in Word. Nothing about the Find/Replace process would affect any data formatting. I suspect your data problem is that you're populating the array with the cell values rather than their text. In any event, your process seems unnecessarily convoluted - you should be able to work with the worksheet content directly rather than going through the circumlocution of first building an array, then using that. Your Find/Replace process could also be made much more efficient.

Clicking on your attachments generates an 'Invalid Attachment specified' message in both cases, so they're of no use. Even if the links weren't invalid, being images your attachments would be far less meaningful than the actual document.

At a guess, having not seen you document or workbook to provide any context, you might try:

Please Login or Register  to view this content.

[mikel1369]

OMG!!

It works perfect! I changed just two things from your code and now it works!

Just one question more, if I want to paste graphs from excel, Iam using the following code:

'valor = "evolucion_grafico1"
'objWord.Selection.Goto _
' What:=-1, _
' Name:=valor
' Hoja5.ChartObjects(valor).Activate
' ActiveChart.ChartArea.Select
'
' Selection.Width = 2000
' Selection.Height = 1000
'
'
' Selection.Copy
' objWord.Selection.Paste

Is there any more afficient way of doing it?

I repeat: Thank you, thank you, thank you!!

[macropod]

You could reduce that to:

Please Login or Register  to view this content.

[mikel1369]

Hi Again,

Using this I have a 438.error.

Please Login or Register  to view this content.

What could the problem be? I have already create the evolucion_grafico1 marker in the word document

Thanks!
Miguel.

[macropod]

On which line do you get the error?

[mikel1369]

With Sheets("aux").ChartObjects(valor).ChartArea 'Aux is the name of the excel sheet

[macropod]

Try:

Please Login or Register  to view this content.

[mikel1369]

Hi,

It gives me the 438 error again with:

Please Login or Register  to view this content.

[macropod]

The code I posted was derived from your own, where you had:

Please Login or Register  to view this content.

Your code purports to activate then select the ChartArea, then resize and copy it. I suspect the issue is that, despite what your code purports to do, you can't change the size of or copy the ChartArea. So perhaps you should just be using:

Please Login or Register  to view this content.

[mikel1369]

Yep! Now it Works!

Thank you!!