'underlines last two words, count considers ".", and etc. StrArr(3) = ActiveSheet.Range("A28").ValueĬount = Doc.Sections(1).Headers(1). Wd.ActiveDocument.Sections(1).Headers(1). = 1įor someone in future that wants to do something similar, but without Table 'Procedure, to create header RangeObj.Tables(1).Cell(3, 1).Range.Paste Step-2: Select Header & Footer enables header and footer option on your excel file. ' And picture is getting pasted in the document not in header Table Step-1: Be on the data, Go to Insert >Header & Footer under text option. 'This line was causing Problem as Range.Application was referring to Word application Change to Your requirementĪctiveSheet.Shapes(1).CopyPicture xlScreen, xlBitmap 'shapes(4) modified to Shapes(1) for test. Set RangeObj = wd.ActiveDocument.Sections(1).Headers(1).Range referance to Word application added to ActiveDocument Set wd = CreateObject("Word.Application") Would paste it in the header table as shown belowĪlso instead of referring ActiveDocument directly in excel VBA (causing problem in some instances of run) it may be referred via Word Application. So changing the line to RangeObj.Tables(1).Cell(3, 1).Range.Paste The Picture is getting pasted in the document itself as it is being referred to Application object selection (normally it is not in the header table but in the main document).
#Excel copy picture in header code#
The main issue in the code is in the line RangeObj.Tables(1).Cell(3, 1). 'Shapes(4), because there are more then one object in sheetĪctiveSheet.Shapes(4).CopyPicture xlScreen, xlBitmap 'copy picture that is embedded in excel sheet RangeObj.Tables(1).Cell(2, 1).Range.Text = StrArr(2) This will add the picture that you just selected. Now, browse through all the available images, select the image that you wish to add and click the insert button as shown in the below image. After this navigate to ‘Format’ > ‘Sheet’ > ‘Background’. RangeObj.Tables(1).Cell(1, 1).Range.Text = StrArr(1) First of all, open the worksheet where you wish to add the background. Range:=RangeObj, NumRows:=4, NumColumns:=1 Set RangeObj = ActiveDocument.Sections(1).Headers(1).Range StrArr(2) = ActiveSheet.Range("A27").Value StrArr(1) = ActiveSheet.Range("A26").Value
#Excel copy picture in header how to#
Can't figure out how to get image in cell.įound that picture can be added from file, but I don't want to keep picture in separate file, because if I move my excel file I have to move picture file also. Picture is stored on sheet in excel file, that contains all data for report in word. I'm trying to do that with table that has 1 column and 4 rows. (Note that headers are visible only in Page Layout view, the Print Preview window in Backstage view ( File tab, Print), and the printed worksheet.I'm calling procedure from my main procedure to make header in word, that contains 2 lines of text, then image, then 1 line of text. To return to the Normal view–the way you typically work in Excel–click the Normal button on the status bar at the bottom right of your screen. When you’re viewing it, you’ll be in Page Layout view. You’ll see that your worksheet header contains the word “Picture.” To view the actual watermark, just click in any cell in your worksheet. PNG file on your computer and add it to the header. Find your copy of the DRAFT or CONFIDENTIAL. Excel switches to Page Layout view, and your cursor appears in the center header box at the top of the worksheet page (under Header).Ĭlick the Design tab, and in the Header & Footer elements group, click Picture. In your worksheet, click the Insert Tab and in the Text group, click Header & Footer. Specify the location where you want to save the picture and do so. Right-click on DRAFT or CONFIDENTIAL, and on the shortcut menu, click Save Target As. Just follow the steps below the graphic.įirst you’ll need to open and save the watermarks we’ve created. To get you started, we’ve created DRAFT and CONFIDENTIAL watermarks that you can access right from this post. PNG graphic file in the header of your Excel worksheet, and it displays as a watermark. Or sometimes a sensitive document needs to be kept secret, making it important to add a CONFIDENTIAL watermark.Įxcel doesn’t come with a built in way to add a watermark, but there’s a pretty painless way to do it: just place a pre-built. Sometimes that collaboration takes several iterations, so it’s useful to add a DRAFT watermark to a worksheet. If you’re a manager building a budget in Excel, you’ll likely need to solicit input from your staff. picture in two different picture types in one RTF file: one supported picture type to display, and one uncompressed WMF copy of the original picture to.