CODEWITHSUNDEEP
vbams-word
Andrew
Andrew

Reputation: 67

Creating and saving Excel document From VBA in Word

I want to be able to run a macro in a Word document that will create an Excel document and then save that spreadsheet in a shared folder.

This is the code I have so far:

Public Sub Monthly_Commission_Extract()

Dim objExcel
Dim objDoc
Dim objSelection
Dim SaveAs1 As String
SaveAs1 = ("\\stnlinasshd01\P403759\Month End\Monthly Commission Extract\1st Save")

Set objExcel = CreateObject("Excel.Application")
Set objDoc = objExcel.Workbooks.Add

objExcel.Visible = True

Set objSelection = objExcel.Selection

ActiveWorkbook.SaveAs FileName:=SaveAs1, FileFormat:=-4158, CreateBackup:=False
Application.DisplayAlerts = True

End Sub

The code is giving me an error:

Run-time error '424': Object required

At the following piece of code:

ActiveWorkbook.SaveAs FileName:=SaveAs1, FileFormat:=xlText, CreateBackup:=False

Please advise how I get around this.

Upvotes: 4

Views: 11657

Answers (3)

Lobito
Lobito

Reputation: 147

Oh captain, yes my captain, the errormessage says clearly that a object is missed, I think this line SaveAs1 = ("\stnlinasshd01\P403759\Month End\Monthly Commission Extract\1st Save") is the problem You should a SET-Statement out of this, by starting this line with "SET ", then you have the object required.

Upvotes: 0

Kallol Bosu Roy Choudhuri
Kallol Bosu Roy Choudhuri

Reputation: 771

Below is a simple and straight forward code that creates a new excel workbook and saves it into a path on your local disk. This VBA code has ben tested from inside MS Word.

Private Sub CreateExcel()

Dim myExcel As Object
Dim myWb As Object

Set myExcel = CreateObject("Excel.Application")
Set myWb = myExcel.Workbooks.Add

Application.DisplayAlerts = False

myWb.SaveAs FileName:="D:\test\dump.xls"

Application.DisplayAlerts = True

myWb.Close False
Set myWb = Nothing
myExcel.Quit
Set myExcel = Nothing

End Sub

In your case, the following would have worked because 'objDoc' is derived from the excel object reference.

objDoc.SaveAs FileName:=SaveAs1, FileFormat:=-4158, CreateBackup:=False

Upvotes: 0

Tim Williams
Tim Williams

Reputation: 166790

objExcel.ActiveWorkbook.SaveAs

not just 

ActiveWorkbook.SaveAs

Anything which "belongs" to Excel must be prefixed with your objExcel application reference.

Upvotes: 1

Related Questions