Create a report in a word document using data from a workbook

palmoura · Aug 17th 2006

Hi fellows,

I'm newer here. I have a work to do, but I can't solve this problem. I have a workbook with some worksheets, and a I have to generate a document in Word format with a some data that become from the worksheets and I don't know how to do it, would you give me some help?

Thanks
I regard your answer

Dave Hawley · Aug 17th 2006

Re: Create a report in a word document using data from a workbook

Moved to correct question forum

palmoura · Aug 17th 2006

Re: Create a report in a word document using data from a workbook

thanks for the change, i didn't know what was the correct section
well I will give U my workbook to see. I have to collect some information from sheets and make a report in a word document. If you cand i need help, it's urgent please.

johnccc · Aug 17th 2006

Re: Create a report in a word document using data from a workbook

Her's some code which worked for me

Code

Sub BFormPrintPlus()
Dim objMyField As Field
Dim intCount As Integer
Dim Enclosed As String
Dim ToFollow As String
Dim Required As String
Dim position, count
Dim TextString As String
On Error GoTo Error_Handling
Set wordApp = GetObject(, "Word.Application")
NextStep:
On Error Resume Next


'THIS PRINTS OUT THE WORD DOC  (Use your own Path)
Set wordDoc = wordApp.Documents.Open(FileName:="\\LEEDS-RES-02\TLIBLEEDS$\BrokerFormMemo.dot")
'BrokerForm is a Form in Excel, The Word doc contains fields
If BrokerForm.TextBox14.Value = "" Then
    wordDoc.Fields(1).Result.Text = BrokerForm.TextBox8.Value 'Insurer
Else
    wordDoc.Fields(1).Result.Text = BrokerForm.TextBox14.Value & " - " & BrokerForm.TextBox8.Value 'insurer & contact
End If
wordDoc.Fields(3).Result.Text = BrokerForm.TextBox13.Value 
wordDoc.Fields(4).Result.Text = BrokerForm.TextBox11.Value
wordDoc.Fields(5).Result.Text = BrokerForm.TextBox3.Value 
wordDoc.Fields(6).Result.Text = BrokerForm.TextBox6.Value 


WordDoc.PrintOut
ExitHere:
wordApp.DisplayAlerts = False
wordDoc.Close (False)
wordApp.DisplayAlerts = True
Set wordDoc = Nothing
Exit Sub
Error_Handling:
Set wordApp = CreateObject("Word.Application")
Resume NextStep
End Sub

Display More

This will not work by itself, it needs a word document in the right plkace and it prints from a form. It should get you started though and changing the form references to cell ones shouldn't be too difficult.

John

johnccc · Aug 17th 2006

Re: Create a report in a word document using data from a workbook

Sorry, forgot to make it clear that this is all controlled by a macro in Excel.

Similar code should be able to be run this from Word, I just didn't have any to hand.

John

palmoura · Aug 17th 2006

Re: Create a report in a word document using data from a workbook

Quote from johnccc

Sorry, forgot to make it clear that this is all controlled by a macro in Excel.

Similar code should be able to be run this from Word, I just didn't have any to hand.

John

No probies John, U give me a big help. Thanks for the reply

Kind rewards

palmoura · Aug 21st 2006

Problem solved

I solved my problem with this

Code

Private Sub CommandButton1_Click()


  DataCriacao = Date ' guarda a data actual do sistema
  Dim caminhoRel As String ' caminho onde se encontra o modelo
  Dim OutputFile As String ' nome do ficheiro que vai ser gravado e contem o relorio
  Dim outputPath As String ' caminho onde vai ser guardado o relatorio gerado
  Dim dirStore As String ' contem a pasta e o ficheiro a ser gerado
  Dim WordDoc As Object ' referencia ao ficheiro Word
  Dim WordApp As Object ' referencia à aplicação Word
  Dim clt As String
  ' Abre o ficheiro
  If verificaCelulasVazias("Dados Cliente", "D12") Then
    OutputFile = ThisWorkbook.Sheets("Dados Cliente").Range("D12").Text & DataCriacao & ".doc"
    outputPath = ThisWorkbook.Path & "\FolhasGeradas\" & ThisWorkbook.Sheets("Dados Cliente").Range("D12").Text
  Else
    Worksheets("Dados Cliente").Activate
pergunta:
    clt = InputBox("Qual o nome do cliente ?") ' se nao for preenchido o nome do cliente entao pergunta pelo nome do cliente
    If clt = "" Then
      GoTo pergunta
    End If
    ThisWorkbook.Sheets("Dados Cliente").Range("D12").Value = clt
    OutputFile = ThisWorkbook.Sheets("Dados Cliente").Range("D12").Text & DataCriacao & ".doc"
    outputPath = ThisWorkbook.Path & "\FolhasGeradas\" & ThisWorkbook.Sheets("Dados Cliente").Range("D12").Text
  End If
  dirStore = outputPath & "\" & OutputFile
  Set WordApp = CreateObject("Word.Application")
  WordApp.Visible = True
  caminhoRel = ThisWorkbook.Path & resources & nomeRelFLN
  Set WordDoc = WordApp.Documents.Open(caminhoRel)
  ' Brincar com o ficheiro
  If verificaComboBoxVazia("ServicosManutencao", cbTipoPT1) Then
   With WordDoc
    .Bookmarks("clienteTitulo").Range.Text = ThisWorkbook.Sheets("Dados Cliente").Range("D12").Text
    
    'bookmarks restantes
  If FileOrDirExists(outputPath) Then
    MkDir outputPath
  Else
    ChDir outputPath
  End If
  If FileOrDirExists(OutputFile) Then
    .SaveAs dirStore
  Else
    'apenas gravar o ficheiro existente e alterar o nome
  End If
  .Close ' close the document
 End With
End If
    WordApp.Quit ' close the Word application
    Set WordDoc = Nothing
    Set WordApp = Nothing
End Sub

Display More

Dave Hawley · Aug 22nd 2006

Re: Problem solved

Thanks for sharing palmoura in the spirit of ozgrid.

palmoura · Aug 22nd 2006

Re: Problem solved

Quote from Dave Hawley

Thanks for sharing palmoura in the spirit of ozgrid.

No probies Dave, i'm here to be helped and to help if I can :p
By the way, if I have other problems I can count on You.

Huges

Create a report in a word document using data from a workbook

Participate now!

Share