Hello,
I am really newbie in excel vba...
I am using excel and word 2000. Thi is a really simple issue, but I can't solve.
I try to pass some values from Excel table to an especific position of a document word using bookmarks and it must be printed automaticaly by pressing a shortcut keyboard from excel.
The process must be all invisible.
I have done that by copying some vba code and pasting and it works fine.
The problem comes up when by the print out PART.
I can't do that it gets printed out automatically.
Only when I create an object
Set wdApp = CreateObject("Word.Application")
It prints out but something goes wrong, because word doesn't close properly and if I try to run this macro again... always appears a window saying that the document is being used by other user... but I can't close it...
This here below is my code... can you review it please... Thanks
****************************************************
Sub passdatatoword ()
On Error GoTo errorHandler
Dim wdApp As Word.Application
Dim Mydoc As Word.Document
'********************** variables Excel **************
Dim Usuario_Nombre As Excel.Range
Dim Usuario_Apell As Excel.Range
Set wdApp = New Word.Application
Set Mydoc = wdApp.Documents.Add(Template:=" \\Infofim74\serviciossociales\SERSOCIAL\Atencion_Publico\CORREO\Correo_vinculo_Sius\X2_Oficio_usuario_vinculado_siuss Pruebas.doc")
'*********** Giving variables from excel ************
Set Usuario_Nombre = Sheets("Correo").Range("H2")
Set Usuario_Apell = Sheets("Correo").Range("I2")
' **** giving variables too bookmarks of word document ****************
With Mydoc.Bookmarks
.Item("Usuario_Nombre").Range.InsertAfter Usuario_Nombre
.Item("Usuario_Apell").Range.InsertAfter Usuario_Apell
End With
'******************PRINT OUT PART ** Print document word ***********
'we have severals printers.
ActivePrinter = "HP Officejet K7100 Series"
'I need to print two pages. Page 1 one copy and page 2 three copies. Can you help me to do that?
with
.PrintOut Background:=False, Copies:=1
.Close False 'It doesn't close...
End With
'********* Close template and word application ************
'probabli here I have to close a file and use CLOSE AND QUIT.
'**************** Final ******************
errorHandler:
Set wdApp = Nothing
Set Mydoc = Nothing
End Sub
I am really newbie in excel vba...
I am using excel and word 2000. Thi is a really simple issue, but I can't solve.
I try to pass some values from Excel table to an especific position of a document word using bookmarks and it must be printed automaticaly by pressing a shortcut keyboard from excel.
The process must be all invisible.
I have done that by copying some vba code and pasting and it works fine.
The problem comes up when by the print out PART.
I can't do that it gets printed out automatically.
Only when I create an object
Set wdApp = CreateObject("Word.Application")
It prints out but something goes wrong, because word doesn't close properly and if I try to run this macro again... always appears a window saying that the document is being used by other user... but I can't close it...
This here below is my code... can you review it please... Thanks
****************************************************
Sub passdatatoword ()
On Error GoTo errorHandler
Dim wdApp As Word.Application
Dim Mydoc As Word.Document
'********************** variables Excel **************
Dim Usuario_Nombre As Excel.Range
Dim Usuario_Apell As Excel.Range
Set wdApp = New Word.Application
Set Mydoc = wdApp.Documents.Add(Template:=" \\Infofim74\serviciossociales\SERSOCIAL\Atencion_Publico\CORREO\Correo_vinculo_Sius\X2_Oficio_usuario_vinculado_siuss Pruebas.doc")
'*********** Giving variables from excel ************
Set Usuario_Nombre = Sheets("Correo").Range("H2")
Set Usuario_Apell = Sheets("Correo").Range("I2")
' **** giving variables too bookmarks of word document ****************
With Mydoc.Bookmarks
.Item("Usuario_Nombre").Range.InsertAfter Usuario_Nombre
.Item("Usuario_Apell").Range.InsertAfter Usuario_Apell
End With
'******************PRINT OUT PART ** Print document word ***********
'we have severals printers.
ActivePrinter = "HP Officejet K7100 Series"
'I need to print two pages. Page 1 one copy and page 2 three copies. Can you help me to do that?
with
.PrintOut Background:=False, Copies:=1
.Close False 'It doesn't close...
End With
'********* Close template and word application ************
'probabli here I have to close a file and use CLOSE AND QUIT.
'**************** Final ******************
errorHandler:
Set wdApp = Nothing
Set Mydoc = Nothing
End Sub
