Posts by alring

Re: Copy and paste to word header

Hi iwrk4dedpr

Maybe it's because I use Windows XP and Excel/Word 2003 and not 2000.

I don't know how to run a macro step by step. But it seems that Word is trying to do something. If I replace the last IF-statement so it look liik the code you gave me then I get a message saying:

"One of the values passed to this method or property is put of range."

So I need to have all the IF-Statement.

Alring

Re: Copy and paste to word header

Hi iwrk4dedpr
Maybe is should be childs play but it's not. For me. I have tried your code many times in different ways and all of them with the same result. Word is in normal view. Now the code look like this:

Sub excelToWord_click()
    'Open the word document
    aNameAndPath = Range("A4")
    Set wdApp = CreateObject("Word.Application")
    wdApp.documents.Open Filename:=aNameAndPath
    wdApp.Visible = True
    'copy cells
    Worksheets(1).Range("A1:D2").Select
    Selection.Copy
    Range("A3").Select
    'insert cells
    With wdApp.activedocument.Sections(1)
        .Headers(1).Range.inlineshapes(1).Activate
        Range("A1:D2").Select
        ActiveSheet.Paste
    End With
    If wdApp.ActiveWindow.View.SplitSpecial <> wdPaneNone Then
        wdApp.ActiveWindow.Panes(2).Close
    End If
    If wdApp.ActiveWindow.ActivePane.View.Type = wdNormalView Or wdApp.ActiveWindow. _
        ActivePane.View.Type = wdOutlineView Then
        wdApp.ActiveWindow.View.Type = wdPrintView
    End If
    Application.CutCopyMode = False
End Sub

Maybe I need to find my inner child to solve this. But right now Can't I find it.

Alring

Hi,

I have made a mscro in Excel that can copy cells and paste them into the header of a Word document.

After paste then the Word document is viewed in normal mode. I want it to be viewed in Print Layout View.

I know that it's Word that don't do as I want it to do. But since the macro is in Excel and Word is "controlled" by Excel then i do believe that I have to ask here.

I have attached the Excel file with the macro and the Word document in the same .zip file.

alring

Re: Copy and paste cell into word cell

Hi Roy

I'm sorry but I don't understand why you have moved my quistion.

The macro I have is in Excel and not in Word. The macro I have copy cells A1 and B1 into the same range in Word. In the word document I have inserted Excel cells. It's in there that A1 and B1 is pasted into. In Word the range is also A1 and B1. The macro works fine as long as the cells in Word is not inside the header. But I need to have this cells in the header of the word document.

In other words: this have nothing to do with Word VBA but only Excel VBA because it's Excel that "controls" the Word in this case.

Alring

Hi

I have a macro that can copy and paste Excel cells into Excel cells that is in a Word document. See the code below. My problem is to get the cells to be pasted into the Excel cells that i have in the header on the Word document.

Code:

Sub excelToWord_click()


    'Open the word document
    Set wdApp = CreateObject("Word.Application")
    wdApp.documents.Open "H:\test_01.doc"
    wdApp.Visible = True
    
    'copy cells
    Worksheets(1).Range("A1:B1").Select
    Selection.Copy
    'insert cells
    
    wdApp.Activedocument.InlineShapes(1).Activate
    Range("A1:B1").Select
    ActiveSheet.Paste


    Application.CutCopyMode = False
    
End Sub

I have tried to put in this code after "'insert cells" but it don't work.

With appWD.documents(1).Sections(1)
        .Headers(1)
    End With

Can someone please help me.

Alring

Re: Nested If

Hi Wigi

That's right.

Re: Nested If

Hi Wigi

As fare as I can see then is it then highest number that Wilna want to have. And If i understand you code too then is it also the highest number you get.

But let us see which one Wilna want to use.

Alring

Re: Nested If

Hi Wilna

I have changed B48.

Take a look and see if you can use this.

Alring

Hi There

I am working on a calender. Now I want to to put small pictures that shows the size of the moon. So how can I use VBA to input pictures in specified cells??
I also want to know how I can get VBA to delete the pictures when I change the year of the calander.

I do hope that it's possible to do these things.

Thank you in advance.

Re: Hyperlink

Hi Goppi

THANK YOU :thanx:

This is perfect. Just what I needed. I don't know how I can thank you enough. This was a big help for me. :cheers:

:thanx:

Alring

Re: Hyperlink

Hi Goppi

I am using Excel to control the project. In that excel document I have made some hyperlinks that can open some documents in pdf. These pdf documents sometimes change the name. But it's just the last part of the name that changes. As desribed before. The macro I have now look like this:

Sub Button1_Click()


    Dim fs, f, f1, fc, filename, filesearch, version, sdir
    sdir = Worksheets(1).Cells(1, 1)
    filesearch = Worksheets(1).Cells(2, 1) & _
    "-DOC-" & _
    Worksheets(1).Cells(3, 1)
    Set fs = CreateObject("Scripting.FileSystemObject")
    Set f = fs.GetFolder(sdir)
    Set fc = f.Files
    For Each filename In fc
    If Left(filename.Name, Len(filesearch)) = filesearch Then
    Worksheets(1).Cells(4, 1).Value = Mid(filename.Name, Len(filesearch) + 1, 2)
        Exit For
        End If
    Next
    aNameAndPath = Worksheets(1).Cells(6, 1)
    Set appWD = CreateObject("word.Application")
    appWD.Visible = True
    appWD.documents.Open filename:=aNameAndPath


End Sub

It's the part after "Next" that I quiestions to now. I know that this part can open a word document. But what do I need to write so it's Adobe Reader that opens.
Cells(6,1) is the path and file name.
Regards

Re: Hyperlink

Hi Goppi

As the macro is now then it's write the number for the document in cell A3. But is it also possible to have the macro to open the document? I know that if it's a word document then the VBA code is:

Word.Apllication

But what is it for a Pdf file?? if I just know that then I can use the macro to find the last version and to open the pdf file.

Alring

Re: Hyperlink

Hi Goppi

Thank you for the code. It's works almost perfect. I was hoping that the macro also could open the document. But it seems that i can't get it all.

:thanx:

Alring

Re: Hyperlink

The pdf files is in another directory and there is only one version of each pdf file.
regards

Re: Hyperlink

Hi Goppi

It don't sounds good in my ears right now. But if Excel can't do it in that way then I have to change my Excel document.

Now we just come to a nother problem. I have never tried to write a macro like that in VBA before. So can you please help me to make a wildcard in VBA that can solve my problem that way.

Regards

Re: Hyperlink

I want that A3 should be a wildcard so the hyperlink automatic takes the highest version-number in filename.

Regards

Re: Hyperlink

It's not the excel workbook the change number in the filename. It's the PDF file that change version-number in the filename if there is an update to the PDF-document. If there is one of these changes in version-number e.i. from 02 to 03 then 02 will 02 disapear from the filesystem. So it will aways be the highest number. Meaning where I put the XX and YY before.

I don't know if we can call it wildcard for the last part of the filename.

Regards

Re: Hyperlink

Maybe I didn't explain myself good enogh. The last part of the filename before the dot is version-number of the document.

I have the following:
6666-DOC-402-XX.pdf
and
7777-DOC-402-YY.pdf

In different cells I write the hole filename and then I put them together in the a hyperlink.

E.I
Cell A1=6666
Cell A2=-402-
Cell A3=XX
cell A4=.pdf
In another document is it the same but cell A1 will be 7777

But when I make the hyperlink then I have to write E.I. 02 in cell A3. But if change that document to version 03. Then the name will also be 03.

So what I want it the hyperlink automatic takes the highest version number.

Be the way. I don't wnat to use VBA for this. I mean I hope that I can do it without. If not then I have to use VBA.

Regards

Re: Problem with variables in userform

Hi Roy

OK. I will try to do that. But my english isn't the best here on this planet. :redface:

Alring

Re: Userform

Thank you so mush badger101. Now I can see what an idiot I have been. Maybe I should ask my boss to have a reading course. I think it will be on is't place to get one of these NOW.

Thank for you help. And sorry to all that I asked above this.

:thanx:
Alring