Posts by palmoura

Re: Copy/Paste Data Without Control Objects

Well I have a solution, i will copy tables to a new worksheet and then paste it in the wordDocument
btw Dave, Thanks for the support,

Kind Rewards,
Pedro

Re: Copy/Paste Data Without Control Objects

Quote from Dave Hawley

Shouldn't you be recording the pastespecial in Word?

I'm copuing from Excel and paste in word, but the code is working in Excel.
I try in Excel and I tried in word and the result was

Selection.PasteExcelTable False, False, False

Re: Copy/Paste Data Without Control Objects

Hy again Dave,
I've recorded a macro and the result was :

Range("A33:H38").Select
    Selection.Copy
    Sheets("Sheet1").Select
    Range("D5").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Range("D12").Select
    Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
        False, Transpose:=False

but when I try to apply in my code it gives me a run time error:

If wordDoc.Bookmarks.Exists("pts") = True Then
         wordDoc.Bookmarks("pts").Range.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
        False, Transpose:=False
       End If

btw ....
the runtime error is Run-time error '448':
Named argument not found

Re: Copy/Paste Data Without Control Objects

I try to explain it better, this is my code to select a range, and paste it in word Document

Sheet6.Activate
       Sheet6.Range("A12:N24").Select
       Selection.Copy
       If wordDoc.Bookmarks.Exists("pts") = True Then
         wordDoc.Bookmarks("pts").Range.PasteSpecial
       End If

I select the range and paste it in the document, but this range is a table that has some control buttons, but when is pasted in the document the buttons are pasted also.

Kind rewards,

Pedro

Re: Copy Data From To An Automation Object

Quote from Dave Hawley

Use PasteSpecial.

Hi Dave, thanks for the sugestion, but I'm using PasteSpecial, but it copied the buttons and comboBoxes, because when i'm selected the range it select the cells that has buttons.

Kind rewards,

*Pedro

Re: Insert range in a word document

Quote from Wigi

Please help others in the Ozgrid spirit and tell how you did it.

Sorry, but i didn't post my solution because I didn't checked if my solution was correct. Here it comes:

I have an automation Object, i put a bookmark in that object and I create a range in that bookmark, then I did this

Sheet6.Activate
       Sheet6.Range("A12:N24").Select
       Selection.Copy
       If wordDoc.Bookmarks.Exists("pts") = True Then
         wordDoc.Bookmarks("pts").Range.PasteSpecial
       End if

Hi fellows,

i'm working with excel and i have tables that gone to by copied to a document, but that tables has some controls like buttons, when i'm copying tables in VBA, Excel copy the buttons but i do'nt have to copy that, how can I do that?

Kind Rewards,
Pedro

Re: Insert range in a word document

problem is solved, thanks anyway

Hi fellows,

I am work in an aplication in Excel, and I have to crate a report with data from the excel document. I have text in the word document and I have to paste a range in the word document, i'm trying to do that but he create a new document but i have to put in the document that excel program create and paste it after the text that I have in document. Can I do that?

Kind rewards

Re: bookmarks

Quote from johnccc

Hi,

Surely the code

Code

.Bookmarks("local").Range.Text = ""

Where "local" can be replaced by temp from my code should get rid of the text but leave the bookmark in place??

HTH

John

Display More

Hi,
I did that on my code. I create a function

Function clean_content_of_Bookmarks()
  With wordDoc
      .Bookmarks(bookmarkname).Range.Text = ""
    ' do the same to all bookmarks in the document
  End With
End Function

but it didn't clear the content, and wrote the text after the content in the docuement.

Re: bookmarks

Quote from Andy Pope

When you say clean do you mean DELETE or CLEAR CONTENT?

John's code is removing all the bookmarks when appending, which will cause your code to fail.

sorry my explanation was not correct, when i say clean is to clear the content of the bookmark.
Huges

Re: bookmarks

thanks for the help Johnccc,

but i do'nt know wich tipe is the variable temp is an object? is a String?
i try this code, but when i append the bookmark i have an error

this is the function

Function cleanBookmarks()
  Dim i As Integer
  Dim temp
  With wordDoc
       On Error Resume Next
    For i = wordDoc.Bookmarks.count To 1 Step -1
        temp = wordDoc.Bookmarks.count
        wordDoc.Bookmarks(temp).Delete
    Next i


  End With
End Function

this is the code i try to put working

sub test()
 If FileOrDirExists(OutputFile) Then
    MsgBox "new"
    Set wordDoc = WordApp.Documents.Open(caminhoRel)
  Else
    MsgBox "append"
    Set wordDoc = WordApp.Documents.Open(dirStore)
    Call cleanBookmarks
  End If
  With wordDoc
    
    If verificaCelulasVazias("Dados Cliente", "localidade2") And verificaCelulasVazias("Dados Cliente", "gestorNome") Then
       .Bookmarks("cliente").Range.Text = ThisWorkbook.Sheets("Dados Cliente").Range("cliente").Text              'error here
       .Bookmarks("local").Range.Text = ThisWorkbook.Sheets("Dados Cliente").Range("localidade2").Text
       .Bookmarks("gestor").Range.Text = ThisWorkbook.Sheets("Dados Cliente").Range("gestorNome").Text
       .Bookmarks("mail").Range.Text = ThisWorkbook.Sheets("Dados Cliente").Range("mailGestor").Text
       .Bookmarks("telefone").Range.Text = ThisWorkbook.Sheets("Dados Cliente").Range("tlfGestor").Text
       .Bookmarks("telemovel").Range.Text = ThisWorkbook.Sheets("Dados Cliente").Range("tlmGestor").Text
       .Bookmarks("fax").Range.Text = ThisWorkbook.Sheets("Dados Cliente").Range("faxGestor").Text
       .Bookmarks("data").Range.Text = Date
    End If
end Sub

huges

hello fellows,

I have a question.... i have a document(generated with data from excel) and everytime I generate a report sometimes i have to create new file or append the existing file, i have bookmarks in file, but when i'm trying to append the file he don't clean the bookmarks, i want to know how to clean the bookmarks.

huges

Pedro Moura

Re: Fill a Cell with a color in VBA

Thank you Dave. You're Kind and nice :music:
huges

Pedro

Hi fellows,

I have a question, can I fill a cell with a color in VBA? If is that possible, how can I do it?

Kind Rewards.

Pedro

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

Problem solved

I solved my problem with this

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

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

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.

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