VB code to Create Excel file from Outlook 2007

Not knowing the entire code try (omitting As Object) so it shows:
Dim obj Excel

This works here:

Sub CreateExcel()

    Dim objExcel As Object
    Dim sExcelFilePath As String
 
    Set objExcel = CreateObject("Excel.Application")
   
    sExcelFilePath = "e:\VBA\abc.xls"
   
    objExcel.Workbooks.Add
   
    objExcel.ActiveWorkbook.SaveAs sExcelFilePath

End Sub

>> I tried to run this code and got run time error '424' object required. What is wrong?

You have the line:
"sExcelFilePath = gProject.Path & "e:\VBA\abc.xls""

gproject is an object, (from which you want the path) but without being initialised in the routine VBA cannot use it so it pointing out that the object is required to be instantiated.

Chris

danaseaman,
I tried to run your code and got this error:

Run time error '1004':
Microsoft Office Excel cannot access the file 'e:\VBA\E02A752' There are several possible reasons:
- the file name or path does not exist
- the file is being used by another program
- the workbook you are trying to save has the same name as a currently open workbook

Which I checked all those reasons did not happen when I run the code. What else should I do?
I'm trying to learn how to code create excel file on the background without actually open the excel.
Thx for the help.

Offa, I repeat my post with the comment that it was not addressed in the code fom danseaman:

You have the line:
sExcelFilePath = gProject.Path & "e:\VBA\abc.xls"

gproject is an object, (from which you want the path) but without being initialised in the routine VBA cannot use it so it pointing out that the object is required to be instantiated.

Is th eobject defined with global scope elsewhere?

chris_bottomley,

sorry I forgot to reply on your posting, no the object is not defined elsewhere.
How do you initialize it?

What is it meant to represent ... in order to initialise it we need to know what/where it is

Chris

chris_bottomley,

I changed the code as danaseaman wrote and got those error '1004'. How do you fix it?

Sub CreateExcel()
 
   Dim objExcel As Object
   Dim sExcelFilePath As String
 
    Set objExcel = CreateObject("Excel.Application")
   
   sExcelFilePath = "e:\VBA\abc.xls"
   
   objExcel.Workbooks.Add
   
   objExcel.ActiveWorkbook.SaveAs sExcelFilePath
 
End Sub

Select allOpen in new window

chris_bottomley,

I got it now, the code doesn't create the folder for me. I thought it will automatically create one for me. Thank you to assist me this far. :)

I posted part 2 question related to this one:

https://www.experts-exchange.com/questions/24163884/VB-code-to-Create-Excel-file-from-Outlook-2007-part-2.html