I’m using this code to copy a range of cells from an Excel sheet to a template table in Word.
When I try to copy the data from a sheet cell to a Word table cell, appears error 91
Any help ?
Thanks, everbody,
Marta
This is my code...
Code
Sub Carga()
Dim num As Integer
Dim x As Integer
Dim EtiqCliente As Integer
Dim ColX As Integer
Dim FilaX As Integer
Dim ColY As Integer
Dim FilaY As Integer
Dim Inicio As Integer
Dim TotEtiq As Integer
Dim SumTotEtiq As Integer
Dim TotColum As Integer
Dim EtiquetasHoja As Integer
Dim NumEtiq As Integer
Dim Membrete As String
Dim NomPlantilla As String
Dim appWord As Object
'--------------------------------------------------------------
Dim wDoc As Object
Dim rnValue As Range
With ActiveSheet
Set rnValue = .Range("HojaEtiquetas")
End With
Set appWord = CreateObject("Word.Application")
'Set appWord = New Word.Application
'Set wDoc = appWord.Documents.Add()
'--------------------------------------------------------------
NumCol = hDatos.Range("NumCol").Value
NumFil = hDatos.Range("NumFil").Value
NomPlantilla = "Etiquetas_" & Trim(Str(NumCol)) & "x" & Trim(Str(NumFil)) & ".dot"
'--- Total columns
TotColum = (NumCol * 2) + 1
'--- Total number of labels in the sheet.
EtiquetasHoja = NumCol * NumFil
'---- Cell of the first label (2,2)
FilaY = 2
ColY = 3
appWord.Visible = True
'--- Here we open the template table.
appWord.ChangeFileOpenDirectory "C:\Etiquetas\"
appWord.Documents.Open Filename:=NomPlantilla ', ConfirmConversions:=False, _
ReadOnly:=False, AddToRecentFiles:=False, PasswordDocument:="", _
PasswordTemplate:="", Revert:=False, WritePasswordDocument:="", _
WritePasswordTemplate:="", Format:=wdOpenFormatAuto, XMLTransform:=""
'----------------------------------------------------------------------------------------
'appWord.Selection.HomeKey Unit:=wdStory
NumEtiq = 0
For x = 3 To 1000 'DATOS
'-- Number of labels to print for each record.
num = hDatos.Cells(x, 2)
If Str(num) = "" Then
hEtiquetas.Range("A1").Select
Exit Sub
End If
For EtiqCliente = 1 To num 'LABELS
'------------ LABEL TO CELL --------------------------------------------------------------------------------------
If hDatos.Cells(x, 7) = "" Then
Membrete = hDatos.Cells(x, 3).Value & Chr(10)
Else
Membrete = "Att. " & hDatos.Cells(x, 7) & Chr(10) & Chr(10) & hDatos.Cells(x, 3).Value & Chr(10)
End If
NumEtiq = NumEtiq + 1
Membrete = Str(NumEtiq) & Chr(10) & Membrete & hDatos.Cells(x, 4).Value & Chr(10) _
& hDatos.Cells(x, 5) & " - " _
& hDatos.Cells(x, 6)
hEtiquetas.Cells(FilaY, ColY).Value = Membrete
'-------------------------------------------------------------------------------------------------------------------------
TotEtiq = TotEtiq + 1 'Total of labels in each sheet.
'---- We copy the data in the cell of the template Word.
'----- HERE APPEARS ERROR 91 ----
wDoc.appWord.Selection.TypeText Text:=Membrete
' appWord.Selection.TypeText Membrete
wDoc.appWord.Selection.MoveRight Unit:=appWord.wdCell, Count:=1, Extend:=appWord.wdMove
If TotEtiq = EtiquetasHoja Then 'Ya hemos llenado una hoja
TotEtiq = 0
FilaY = 1
Call Borrar 'A sheet of labels is full, so we clean the sheet.
End If
ColY = ColY + 2
If ColY > TotColum Then
ColY = 3
FilaY = FilaY + 1
End If
Next EtiqCliente
Next x
End Sub
:confused:
Display More