Inserting Excel sheet into Word Document

[Wes Grant]

I put together a function that should create a basic Excel sheet and save it
to the user's C:\temp\ folder. It then can be inserted into the Word
document by the Word macro.

I used all of the help I could muster out of the online Word help files, but
I can't get this code to work. '.Range("A1:C1").Select' breaks. Should
there be another object stuffed in here somewhere? Thanks to any help that
may come!




Private Function Create_ws()
Dim wsObject As Object

Set wsObject = CreateObject("Excel.Sheet")

' ****Following code cut and paste from Excel Macro
With wsObject
.Range("A1:C1").Select
With .Selection
.HorizontalAlignment = excel.Constants.xlCenter
.VerticalAlignment = excel.Constants.xlBottom
.WrapText = False
.Orientation = 0
.ShrinkToFit = False
.MergeCells = False
End With
.Selection.Merge
.Range("A1:C1").Select
.Selection.Font.Bold = True
.ActiveCell.FormulaR1C1 = "Double Click to Edit"
.Range("B2").Select
.ActiveCell.FormulaR1C1 = "'---"
.Range("B3").Select
.ActiveCell.FormulaR1C1 = "'---"
.Range("B4").Select
.ActiveCell.FormulaR1C1 = "'---"
.Range("B2:B4").Select
With .Selection
.HorizontalAlignment = excel.Constants.xlCenter
.VerticalAlignment = excel.Constants.xlBottom
.WrapText = False
.Orientation = 0
.ShrinkToFit = False
.MergeCells = False
End With
.Range("A1:C1").Select
.Selection.Borders(excel.xlDiagonalDown).LineStyle =
excel.Constants.xlNone
.Selection.Borders(excel.xlDiagonalUp).LineStyle =
excel.Constants.xlNone
.Selection.Borders(excel.xlEdgeLeft).LineStyle =
excel.Constants.xlNone
.Selection.Borders(excel.xlEdgeTop).LineStyle =
excel.Constants.xlNone
With .Selection.Borders(excel.xlEdgeBottom)
.LineStyle = excel.xlContinuous
.Weight = excel.xlThin
.ColorIndex = excel.xlAutomatic
End With
.Selection.Borders(excel.xlEdgeRight).LineStyle = excel.xlNone
.Selection.Borders(excel.xlInsideVertical).LineStyle = excel.xlNone
.ActiveWorkbook.SaveAs FileName:="C:\temp\xl132.xls",
FileFormat:=excel.xlNormal, _
Password:="", WriteResPassword:="", ReadOnlyRecommended:=False,
_
CreateBackup:=False
End With

' ***End Cut and Pasted Excel Code.

wsObject.Application.Quit

Set wsObject = Nothing
End Function

 

[Cindy M -WordMVP-]

Hi Wes,

I put together a function that should create a basic Excel sheet and save it
to the user's C:\temp\ folder. It then can be inserted into the Word
document by the Word macro.

I used all of the help I could muster out of the online Word help files, but
I can't get this code to work. '.Range("A1:C1").Select' breaks. Should
there be another object stuffed in here somewhere? Thanks to any help that
may come!

I'm not quite clear on what you're trying to do. Your first paragraph sounds
as if you want to bring an existing sheet into the Word document. But your
code looks as if you're trying to create the sheet directly in Word? Which is
it?

Private Function Create_ws()
Dim wsObject As Object

Set wsObject = CreateObject("Excel.Sheet")

' ****Following code cut and paste from Excel Macro
With wsObject

Cindy Meister
INTER-Solutions, Switzerland
http://homepage.swissonline.ch/cindymeister (last update Jun 8 2004)
http://www.word.mvps.org

This reply is posted in the Newsgroup; please post any follow question or
reply in the newsgroup and not by e-mail

 

[Wes Grant]

Both, Actually.

When I distribute this, I want to distribute one file, so what I am having
the macro do is create the file if it doesn't already exist in the user's
TEMP directory so that it may be inserted into the document later. This bit
of code should create a new Excel sheet, populate / format it, and save it
to the user's temp directory.

When I try to create the document is where the code is failing. If I just
drop a copy into the temp folder, everything works fine.