How JustAnswer Works:

  • Ask an Expert
    Experts are full of valuable knowledge and are ready to help with any question. Credentials confirmed by a Fortune 500 verification firm.
  • Get a Professional Answer
    Via email, text message, or notification as you wait on our site.
    Ask follow up questions if you need to.
  • 100% Satisfaction Guarantee
    Rate the answer you receive.

Ask Yegarboy Your Own Question

Yegarboy
Yegarboy, Computer Enthusiast
Category: Microsoft Office
Satisfied Customers: 509
Experience:  Expert VBA Programmer.
18042535
Type Your Microsoft Office Question Here...
Yegarboy is online now
A new question is answered every 9 seconds

Hi there, i have a excel worksheet and need to export a spreadsheet

Customer Question

Hi there, i have a excel worksheet and need to export a spreadsheet and its contents to a new word doc.
I have at the moment exports to excel and PPt using VBA, these are all working fine.
So to sum it up i have a spreadsheet with textboxes and measure on a grid. and all this need to be exported to a new un-named word. which looking at the size might be 2-3 word sheets.
I get around VBA but i am no expert. Any ideas
Submitted: 2 years ago.
Category: Microsoft Office
Expert:  Yegarboy replied 2 years ago.

yegarboy :

Hi

yegarboy :

Please let me know before you Rate if you need further help. Only Rate when you are satisfied :)

yegarboy :

Open the Excel file. It contains the Macro and Instructions.

Customer:

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.

yegarboy :

That shouldnt be a problem.

yegarboy :

I will get back to you asap.

Customer:

thanks

Customer:

Also the format on the excel sheet need to be exactly the same. not sure if i can attach an example here.

yegarboy :

Yeah you can, click the Paperclip icon. Attach it as a Photo

Customer:

ok

yegarboy :

I fixed it

yegarboy :

Now it creates a new doc on its own

yegarboy :

Let me know how that works :)

Customer:

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.

yegarboy :

You sure can.

yegarboy :

If you tell me the sheet name and the range i can adapt it into the code for you :)

Customer:

This is the code for when it exports to excel from excel.

Customer:

Sub PATSheeENGExport()



Sheets("ENGPAT").Select


Range("B1").Select


Dim ws As Worksheet, wb As Workbook


Set ws = ActiveSheet


Set wb = Workbooks.Add


ws.Cells.Copy


ActiveSheet.Cells.PasteSpecial Paste:=xlValues


ActiveSheet.Cells.PasteSpecial Paste:=xlFormats


Columns("A:B").Select


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


Range("N1").Select



End Sub

yegarboy :

So its a new sheet gets copied to Word?

Customer:

its the tab in the excel called ENGPAT that goes to word

yegarboy :

All the data on ENGPAT goes to word?

Customer:

yes

yegarboy :

Change the range thats in the code now to..

yegarboy :

Sheets("ENGPAT").Cells.Copy

yegarboy :

Spell the sheetname exactly as it is in the tab or it wont work :)

Customer:

from c1 to q154 if that help

Customer:

ENGPAT

yegarboy :

Ok,...

Sheets("ENGPAT").Range("C1:Q154").Cells.Copy

yegarboy :

Sheets("ENGPAT").Range("C1:Q154").Copy

yegarboy :

Use the last one.

Customer:

Ok thats works, but i need to make it fit as the width Q extends it of word

yegarboy :

That will have to be done inside word.

yegarboy :

Let me look real quick

yegarboy :

It can be done within the code :)

yegarboy :

Do you mind gridlines?

Customer:

what on the word

yegarboy :

Yeah

Customer:

shouln`t be a problem, i hope

yegarboy :

Try this then...

yegarboy :

The very last line in the code is currently WD.Range.Pasteandformat 0 Change the 0 to a 2

Customer:

what does the 2 do

yegarboy :

Changes the way Word formats the Data

Customer:

no the format has all gone as well

yegarboy :

Ok, try a 1

Customer:

1 is the same as 0

yegarboy :

Ok

yegarboy :

Then you will need to resize it in word

Customer:

can you tell word to be landscape and small margin, that will do it

yegarboy :

I can try

Customer:

or when it goes into word be shrunk fit

yegarboy :

Ok, I got it all fixed :)

Customer:

no way

yegarboy :

I will paste only what needs added. Paste this code right under the last line WD.Range.Pasteandformat 0

yegarboy :







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

yegarboy :

Paste that in and your all set

yegarboy :

Not sure why it copied the top part too. Its not needed. The bottom is what we wanted. Use this...

yegarboy :

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

yegarboy :

Did this do what you wanted?

Customer:

no all the same as it was before, i need to take the _ and spaces out to make it work, but no

yegarboy :

I dont follow?

Customer:

hold i didn`t see the second lot you pasted

yegarboy :

It now sets the orientation to landscape and sets the margins to small just like you asked.

Customer:

no it export to word just like it did before with the 0

Customer:

the wordbasic line is highlighted with an error

yegarboy :

I will need your workbook to fix it.

yegarboy :

www.wikisend.com After upload please give me the file ID number :)

yegarboy :

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. :)

Customer:

file number 961870

Customer:

sorry for delay the laptop crashed as it was saving, i think it just lost the last part of the vba code.

Customer:

is called 121 KPI

Customer:

it comes up with debug Object required

yegarboy :

Let me check your file

Customer:

sorry for delay earlier the laptop just froze and crashed

yegarboy :

Thats ok

Customer:

the Grey button with export to word on the ENG sheet it the expdrt button and the sheet to export is ENGPAT

yegarboy :

Ok, this should work,... i will reupload the workbook back to you

Customer:

ok, your a star

Customer:

i took me long enough to get what it is now, this i hope is the final step

Customer:

have you uploaded ?

yegarboy :

Sorry for the delay I had to fix a bug

yegarboy :

Let me know how it works. Just press the button on the ENG page :)

yegarboy :

I will be right back

Customer:

Ok i`ll just download it

yegarboy :

Ok, Im back

yegarboy :

If it errors out we need to set the reference in the the VBA editor.

Customer:

Ok it exports but its not showing full data, its cutting of the trend column

yegarboy :

Ohh..ok let me take another look :)

Customer:

your doing brilliantly to get this far, well done

Customer:

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.

Customer:

ok the trend fit now, just closed it up on the eng pat sheet

Customer:

can you fix the export bug when you click a second time

yegarboy :

It will work again after word is closed. It takes like 10 seconds to be ready again.

Customer:

Ok let me try again,

Customer:

no it goes into debug on the second export

Customer:

and quotes The remote server machine does not exist or is unavailable ??

yegarboy :

That just means word isnt ready yet. Let me look into a work around. Mine just did it too.

Customer:

thanks

yegarboy :

No problem :)

Customer:

it is to me i not the expert

yegarboy :

:)

yegarboy :

From what I have read I added the correct Library to it. When It does that press "End" and then run it again

Customer:

just looking

yegarboy :

i am too

Customer:

its like its not opening a word doc in time for the next export as such, or closing

yegarboy :

hmm.

Customer:

when it debugs it highlights the toggle portrait script

yegarboy :

oh

Customer:

should there be a command to say when word is closed or save to refresh, or something along them lines

yegarboy :

Not always no..What version of office do you use?

Customer:

2010

yegarboy :

I just saw it after i asked...sorry about that

Customer:

:)

yegarboy :

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

Customer:

oh

Customer:

the server issue is all i`m getting, but you need to go into it twice to get it to work

yegarboy :

I just ran mine 4 times in a row without error maybe i fixed it

Customer:

what have you done

Customer:

what have you added or changed ?

yegarboy :

I changed a library reference.

Customer:

??

Customer:

explain a bit more

Customer:

please

yegarboy :

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

Customer:

now you will need to explain where these are, i`m not the expert here i`m affraid

yegarboy :

You wont have to change anything :) I will re-upload the workbook

Customer:

ok

Customer:

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

Customer:

woorbook

Customer:

workbook i meant

yegarboy :

Correct

Customer:

how long have you been working with microsoft etc ?

yegarboy :

about 5 years

Customer:

oh well, only two years at this level of building

yegarboy :

:)

yegarboy :

Word is my weakest venue

Customer:

no it did it once, and now it won`t load into the word doc properly

yegarboy :

Same thing huh?

yegarboy :

Im not sure what to do :(

yegarboy :

Do you need it to Run more than once every time the book is open?

Customer:

no it does not debug it open and export perfect and quick. but the alignment has gone of

Customer:

yes maybe two to three

yegarboy :

Ok,

Customer:

when you do the export your end does it open into the word fully or is of set

yegarboy :

How do you mean?

Customer:

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

yegarboy :

Try leaving Word open

Customer:

ok so if i leave the first one open the second then follows the format of the first ????

yegarboy :

I meant to see if it stopped erroring out.

Customer:

thats looks like it needs a refresh in the script

Customer:

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.

yegarboy :

Try this one.

yegarboy :

You will need to set the trend column to fit again tho

Customer:

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

yegarboy :

Thats all I know to do. I cant find an answer to this.

Customer:

no i`m very happy, thank you very much for your time and assistance with this, i would never of got there

yegarboy :

Im glad to of helped. If I come across a solution I will let you know.

Customer:

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

yegarboy :

your Welcome :)

Customer:

i`ll do you rating in a mo

yegarboy :

Ok :)

Yegarboy, Computer Enthusiast
Category: Microsoft Office
Satisfied Customers: 509
Experience: Expert VBA Programmer.
Yegarboy and 4 other Microsoft Office Specialists are ready to help you

JustAnswer in the News:

 
 
 
Ask-a-doc Web sites: If you've got a quick question, you can try to get an answer from sites that say they have various specialists on hand to give quick answers... Justanswer.com.
JustAnswer.com...has seen a spike since October in legal questions from readers about layoffs, unemployment and severance.
Web sites like justanswer.com/legal
...leave nothing to chance.
Traffic on JustAnswer rose 14 percent...and had nearly 400,000 page views in 30 days...inquiries related to stress, high blood pressure, drinking and heart pain jumped 33 percent.
Tory Johnson, GMA Workplace Contributor, discusses work-from-home jobs, such as JustAnswer in which verified Experts answer people’s questions.
I will tell you that...the things you have to go through to be an Expert are quite rigorous.
 
 
 

What Customers are Saying:

 
 
 
  • My Expert answered my question promptly and he resolved the issue totally. This is a great service. I am so glad I found it I will definitely use the service again if needed. One Happy Customer New York
< Last | Next >
  • My Expert answered my question promptly and he resolved the issue totally. This is a great service. I am so glad I found it I will definitely use the service again if needed. One Happy Customer New York
  • Wonderful service, prompt, efficient, and accurate. Couldn't have asked for more. I cannot thank you enough for your help. Mary C. Freshfield, Liverpool, UK
  • This expert is wonderful. They truly know what they are talking about, and they actually care about you. They really helped put my nerves at ease. Thank you so much!!!! Alex Los Angeles, CA
  • Thank you for all your help. It is nice to know that this service is here for people like myself, who need answers fast and are not sure who to consult. GP Hesperia, CA
  • I couldn't be more satisfied! This is the site I will always come to when I need a second opinion. Justin Kernersville, NC
  • Just let me say that this encounter has been entirely professional and most helpful. I liked that I could ask additional questions and get answered in a very short turn around. Esther Woodstock, NY
  • Thank you so much for taking your time and knowledge to support my concerns. Not only did you answer my questions, you even took it a step further with replying with more pertinent information I needed to know. Robin Elkton, Maryland
 
 
 

Meet The Experts:

 
 
 
  • jstinehelfer

    Information Systems Manager

    Satisfied Customers:

    36
    A+ Comptia Certified computer repair
< Last | Next >
  • http://ww2.justanswer.com/uploads/JS/jstinehelfer/2012-6-6_215146_Pictureofme1.64x64.png jstinehelfer's Avatar

    jstinehelfer

    Information Systems Manager

    Satisfied Customers:

    36
    A+ Comptia Certified computer repair
  • http://ww2.justanswer.com/uploads/JA/JasonJames122/2011-12-16_135647_jasonjamesheadshotweb.64x64.jpg JasonJames122's Avatar

    JasonJames122

    Computer Enthusiast

    Satisfied Customers:

    0
    I manage all information systems (network of Windows PC's and servers with Microsoft Office products) for a small business
  • http://ww2.justanswer.com/uploads/JE/jessmagz/2012-6-6_18129_jm.64x64.jpg Jess M.'s Avatar

    Jess M.

    Computer Support Specialist

    Satisfied Customers:

    301
    Computer Software Specialist for more than 10 years
  • http://ww2.justanswer.com/uploads/CH/chutz747/2011-11-6_14231_me.64x64.jpg The-PC-Guy's Avatar

    The-PC-Guy

    Computer Manager

    Satisfied Customers:

    274
    20 years experience providing remote computer support
  • http://ww2.justanswer.com/uploads/SP/spherrod/2012-6-6_174244_1000852.64x64.JPG Steve Herrod's Avatar

    Steve Herrod

    Computer Support Specialist

    Satisfied Customers:

    125
    Trained in Microsoft Office from 2000 through to latest version 2010.
  • http://ww2.justanswer.com/uploads/SE/Seashore2011/2011-12-6_33418_111205223015.64x64.jpg Chris L.'s Avatar

    Chris L.

    Support Specialist

    Satisfied Customers:

    124
    Certified Expert with over 10 years experience.
  • http://ww2.justanswer.com/uploads/KR/krkljatorm/2012-6-24_153417_ITMiro.64x64.jpg IT Miro's Avatar

    IT Miro

    Computer Scientist

    Satisfied Customers:

    97
    Bachelor's Degree in Information Technology, Microsoft Certified Professional
 
 
 

Related Microsoft Office Questions

Chat Now With A Microsoft Office Technician
Yegarboy
Yegarboy
Microsoft Office Technician
509 Satisfied Customers
Expert VBA Programmer.