Give this a try :) http://wikisend.com/download/549324/Export-To-Word.rar
Please let me know before you Rate if you need further help. Only Rate when you are satisfied :)
Open the Excel file. It contains the Macro and Instructions.
Instead of a selected word which has a path, is it possible so that it opens a New word as this will be used by a number of people around the business.
That shouldnt be a problem.
I will get back to you asap.
Also the format on the excel sheet need to be exactly the same. not sure if i can attach an example here.
Yeah you can, click the Paperclip icon. Attach it as a Photo
I fixed it
Now it creates a new doc on its own
Let me know how that works :)
now if the button is on a seperate sheet within the same excel, can you point it to select the data from differant sheet into word.
You sure can.
If you tell me the sheet name and the range i can adapt it into the code for you :)
This is the code for when it exports to excel from excel.
Dim ws As Worksheet, wb As Workbook
Set ws = ActiveSheet
Set wb = Workbooks.Add
Application.CutCopyMode = False
Selection.EntireColumn.Hidden = True
ActiveSheet.Shapes.AddTextbox(msoTextOrientationHorizontal, 3.75, 50.25, 610.75, 150#).Select '1st text box
ActiveSheet.Shapes.AddTextbox(msoTextOrientationHorizontal, 3.75, 800.25, 610.75, 700#).Select '2nd text box
So its a new sheet gets copied to Word?
its the tab in the excel called ENGPAT that goes to word
All the data on ENGPAT goes to word?
Change the range thats in the code now to..
Spell the sheetname exactly as it is in the tab or it wont work :)
from c1 to q154 if that help
Use the last one.
Ok thats works, but i need to make it fit as the width Q extends it of word
That will have to be done inside word.
Let me look real quick
It can be done within the code :)
Do you mind gridlines?
what on the word
shouln`t be a problem, i hope
Try this then...
The very last line in the code is currently WD.Range.Pasteandformat 0 Change the 0 to a 2
what does the 2 do
Changes the way Word formats the Data
no the format has all gone as well
Ok, try a 1
1 is the same as 0
Then you will need to resize it in word
can you tell word to be landscape and small margin, that will do it
I can try
or when it goes into word be shrunk fit
Ok, I got it all fixed :)
I will paste only what needs added. Paste this code right under the last line WD.Range.Pasteandformat 0
WordBasic.TogglePortrait Tab:=3, PaperSize:=0, TopMargin:="1", _ BottomMargin:="1", LeftMargin:="1", RightMargin:="1", Gutter:="0", _ PageWidth:="11", PageHeight:="8.5", Orientation:=1, FirstPage:=0, _ OtherPages:=0, VertAlign:=0, ApplyPropsTo:=0, FacingPages:=0, _ HeaderDistance:="0.5", FooterDistance:="0.5", SectionStart:=2, _ OddAndEvenPages:=0, DifferentFirstPage:=0, Endnotes:=0, LineNum:=0, _ StartingNum:=1, FromText:=wdAutoPosition, CountBy:=0, NumMode:=0, _ TwoOnOne:=0, GutterPosition:=0, LayoutMode:=0, CharsLine:=42, LinesPage:= _ 36, CharPitch:=220, LinePitch:=360, DocFontName:="+Body", DocFontSize:=11 _ , PageColumns:=1, TextFlow:=0, FirstPageOnLeft:=0, SectionType:=1, _ FolioPrint:=0, ReverseFolio:=0, FolioPages:=1 WordBasic.PageSetupMargins Tab:=0, PaperSize:=0, TopMargin:="0", _ BottomMargin:="0", LeftMargin:="0", RightMargin:="0", Gutter:="0", _ PageWidth:="11", PageHeight:="8.5", Orientation:=1, FirstPage:=0, _ OtherPages:=0, VertAlign:=0, ApplyPropsTo:=4, FacingPages:=0, _ HeaderDistance:="0.5", FooterDistance:="0.5", SectionStart:=2, _ OddAndEvenPages:=0, DifferentFirstPage:=0, Endnotes:=0, LineNum:=0, _ CountBy:=0, TwoOnOne:=0, GutterPosition:=0, LayoutMode:=0, DocFontName:= _ "", FirstPageOnLeft:=0, SectionType:=1, FolioPrint:=0, ReverseFolio:=0, _ FolioPages:=1
Paste that in and your all set
Not sure why it copied the top part too. Its not needed. The bottom is what we wanted. Use this...
WordBasic.PageSetupMargins Tab:=0, PaperSize:=0, TopMargin:="0", _ BottomMargin:="0", LeftMargin:="0", RightMargin:="0", Gutter:="0", _ PageWidth:="11", PageHeight:="8.5", Orientation:=1, FirstPage:=0, _ OtherPages:=0, VertAlign:=0, ApplyPropsTo:=4, FacingPages:=0, _ HeaderDistance:="0.5", FooterDistance:="0.5", SectionStart:=2, _ OddAndEvenPages:=0, DifferentFirstPage:=0, Endnotes:=0, LineNum:=0, _ CountBy:=0, TwoOnOne:=0, GutterPosition:=0, LayoutMode:=0, DocFontName:= _ "", FirstPageOnLeft:=0, SectionType:=1, FolioPrint:=0, ReverseFolio:=0, _ FolioPages:=1
Did this do what you wanted?
no all the same as it was before, i need to take the _ and spaces out to make it work, but no
I dont follow?
hold i didn`t see the second lot you pasted
It now sets the orientation to landscape and sets the margins to small just like you asked.
no it export to word just like it did before with the 0
the wordbasic line is highlighted with an error
I will need your workbook to fix it.
www.wikisend.com After upload please give me the file ID number :)
I cant wait any longer for you replies. The best way to get the Word doc to look how you want it is with the Macro Recorder.Click the Developer tab, then Record Macro.Set the sheet up the way you want it then Stop Recording. Copy the Recorded code to the Excel Code I gave you. Kindly Rate me with Excellent or Great service so I can receive credit for helping you. :)
file number 961870
sorry for delay the laptop crashed as it was saving, i think it just lost the last part of the vba code.
is called 121 KPI
it comes up with debug Object required
Let me check your file
sorry for delay earlier the laptop just froze and crashed
the Grey button with export to word on the ENG sheet it the expdrt button and the sheet to export is ENGPAT
Ok, this should work,... i will reupload the workbook back to you
ok, your a star
i took me long enough to get what it is now, this i hope is the final step
have you uploaded ?
Sorry for the delay I had to fix a bug
http://wikisend.com/download/169398/121_KPI vJul 26th v3.xlsm
Let me know how it works. Just press the button on the ENG page :)
I will be right back
Ok i`ll just download it
Ok, Im back
If it errors out we need to set the reference in the the VBA editor.
Ok it exports but its not showing full data, its cutting of the trend column
Ohh..ok let me take another look :)
your doing brilliantly to get this far, well done
also if i do it and then shut word and then click to export it wont work and goes into the dubug mode. If i then close this without changes it will work again.
ok the trend fit now, just closed it up on the eng pat sheet
can you fix the export bug when you click a second time
It will work again after word is closed. It takes like 10 seconds to be ready again.
Ok let me try again,
no it goes into debug on the second export
and quotes The remote server machine does not exist or is unavailable ??
That just means word isnt ready yet. Let me look into a work around. Mine just did it too.
No problem :)
it is to me i not the expert
From what I have read I added the correct Library to it. When It does that press "End" and then run it again
i am too
its like its not opening a word doc in time for the next export as such, or closing
when it debugs it highlights the toggle portrait script
should there be a command to say when word is closed or save to refresh, or something along them lines
Not always no..What version of office do you use?
I just saw it after i asked...sorry about that
I at a loss with this. I dont know why its bugging out on your end. It works perfectly on mine except for the server error
the server issue is all i`m getting, but you need to go into it twice to get it to work
I just ran mine 4 times in a row without error maybe i fixed it
what have you done
what have you added or changed ?
I changed a library reference.
explain a bit more
In the VBA editor there are hundreds of Library types that make VBA work right. I changed 2 of them and it seems to work
now you will need to explain where these are, i`m not the expert here i`m affraid
You wont have to change anything :) I will re-upload the workbook
so with the library changes this is within the workboook not on an individuals computer, so this will then work on other laptop computers etc
workbook i meant
http://wikisend.com/download/785946/121_KPI vJul 26th v3.xlsm
how long have you been working with microsoft etc ?
about 5 years
oh well, only two years at this level of building
Word is my weakest venue
no it did it once, and now it won`t load into the word doc properly
Same thing huh?
Im not sure what to do :(
Do you need it to Run more than once every time the book is open?
no it does not debug it open and export perfect and quick. but the alignment has gone of
yes maybe two to three
when you do the export your end does it open into the word fully or is of set
How do you mean?
its strange if i close the excel and open it and try again it works, if i have it open and try the second time it does not load into the word properly, its off set as if the margins are not recognised second time round
Try leaving Word open
ok so if i leave the first one open the second then follows the format of the first ????
I meant to see if it stopped erroring out.
thats looks like it needs a refresh in the script
as before no errors just if you shut the 1st word down then try again it wont load into word properly. But it does not come up with an error.
http://wikisend.com/download/600130/121_KPI vJul 26th v3.xlsm
Try this one.
You will need to set the trend column to fit again tho
no its the same, no if they need to keep the original 1st one open so the rest follow format they will need to go down this route
Thats all I know to do. I cant find an answer to this.
no i`m very happy, thank you very much for your time and assistance with this, i would never of got there
Im glad to of helped. If I come across a solution I will let you know.
please do, its a strange one. I`ll look on the old google again, i`ve been roaming round this for the fix for this. So yes please if you do have any ideas that would be great. Thanks again
your Welcome :)
i`ll do you rating in a mo