excel to word?

[nancyo]

I am just learning about excel and macros. Is it possible to write a macro from an excel spreadsheet to copy the spreadsheet into a word document? This would need to be from a specific file (in a certain directory) to a specific word doc (in a specific directory).

The trick is that the excel file would be used over and over with different information each time, and this information would need to go to the corresponding new word doc each time.???

 

[Mark O'Brien]

Do you really need a macro?

Can you just insert the Excel spreadsheet into Word using:

Insert|Object "Create from file" then browsing to your spreadsheet and making sure that "link to file" is checked? This will update your Word document whenever the spreadsheet is updated.

HTH

 

[dk]

Hi,

This code copies the usedrange on Sheet1 from a specified file and pastes it into a specified Word document.

IMPORTANT: You need to go to Tools, References in the VB editor and make sure the Microsoft Word n.x Object Library is checked. n.x will depend on the version you're using - for Word 2000 it 9.0.

HTH,
Dan

Sub PasteToWord()
Dim wdApp As Word.Application, wdDoc As Word.Document

Dim strSourceFile As String, strDestFile As String


strSourceFile = "C:tempbook1.xls" 'Change to suit your needs

strDestFile = "C:tempyourdoc.doc" 'Ditto

'Open the Excel file
Workbooks.Open strSourceFile
ActiveWorkbook.Sheets("sheet1").UsedRange.Copy

'Open the Word document
Set wdApp = CreateObject("Word.Application")
wdApp.Documents.Open strDestFile
wdApp.Selection.Paste

End Sub

 

[nancyo]

Mark - this implies that each time the word doc is updated, it overwrites the current info. I need a separate file each time from excel to word. ?

Dan - where do I put this code? in the excel file as a module? I tried it that way, when i click run is does something, but does not create the file I need. Also, the source file and dest file will change with each set of data. Is this possible with the code as written? Please excuse my stupid questions, but this is my second week learning (all self taught) this stuff...

 

[lenze]

If you create a Word Template that has links to the Excel sheet, it will open as a NEW document each time and update with the current contents of the Excel sheet.

 

[Mark O'Brien]

On 2002-04-01 07:51, nancyo wrote:
Mark - this implies that each time the word doc is updated, it overwrites the current info. I need a separate file each time from excel to word. ?

Yup, you're correct. I missed the phrase "new Word" in your original post. I'd follow the VBA code or lenze's suggestions here.

 

[dk]

Nancy,

What area will of the Excel worksheet will you be copying? Will it be the same each time? Do you want the user to be able to select both source and destination files e.g. with a standard Open dialog box? Or do you want a new Word document created each time you run this macro?

Regards,
Dan

 

[nancyo]

Dan - the excel folder has several spreadsheets. Only one needs to be copied into word, and will be the same each time.

The excel file will be saved as a different filename with each new set of data. From there, I want to be able to just copy one of the spreadsheets into a word document, which will also have a new filename (either a template which keeps bening renamed, or?).

I do not know what an Open dialog box is (I am SO new to this). If the tech could select a source and destination, what about filenames? Would they need to be created in word ahead of time?

Ideally, the data is generated and saved into a specific spreadsheet in excel (to be resaved as another filename with each new set of data).

Once this is done, rather than copying and pasting by hand into a new word document, I would like to be able to have either a macro or some other way to do this automatically. There are way too many techs errors, and I want to eliminate this problem.

I apologize for my ignorance!!! I am leaving for the day...any resonses will be tomorrow. THANKS TO ALL!!!

 

[Jack in the UK]

Hi Nancy

Yuuuck Did i hear that WORD thingy...???

PK i hat word for no reason bar its not excel and i WONT allow it on any PC i go near...

But thats not the point.. check for an EXCEL file called Samples (the world will hit find files Samples.xls best i can remember thats what its called, suggest S*.xls search just in case)

I have sheets one has all the code to trans from A to B in Office Excel to Word is there for sure if Excel was loaded correctly.. this will get you started...

Now you have the text over.. the nasty stuff comes into plat WordBasic not VBA.. and ive no idea at that.. Excel can com and Word but?? Not got a cluse..

MY opinion is to didtroy word and do it all in Excel Shes muh nicer and better lloking, and can do all Word can withthe addition of Math.. and i fancy her!

If you need more hlep im sure this case be solved you producing letters or a report??? i guess..

 

[nancyo]

Dan - I have tried many variations on a theme with your code. Any suggestions?