VB code to Create Excel file from Outlook 2007

I tried to run this code and got run time error '424' object required. What is wrong?
Sub CreateExcel()
    Dim objExcel As Object
    Dim sExcelFilePath As String
 
    Set objExcel = CreateObject("Excel.Application")
    
    sExcelFilePath = gProject.Path & "e:\VBA\abc.xls"
    
    objExcel.Workbooks.Add
    
    objExcel.Workbooks.Item(0).SaveAs sExcelFilePath
End Sub

Open in new window

0ffaAsked:
Who is Participating?
 
Chris BottomleySoftware Quality Lead EngineerCommented:
Are you 100% that the exact filepath exists ... and if so that the file does not already exist there?

Chris
0
 
JustWorkingCommented:
Not knowing the entire code try (omitting As Object) so it shows:
Dim obj Excel
0
 
danaseamanCommented:
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
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
Chris BottomleySoftware Quality Lead EngineerCommented:
>> 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
0
 
0ffaAuthor Commented:
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.
0
 
Chris BottomleySoftware Quality Lead EngineerCommented:
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?
0
 
0ffaAuthor Commented:
chris_bottomley,

sorry I forgot to reply on your posting, no the object is not defined elsewhere.
How do you initialize it?
0
 
Chris BottomleySoftware Quality Lead EngineerCommented:
What is it meant to represent ... in order to initialise it we need to know what/where it is

Chris
0
 
0ffaAuthor Commented:
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

Open in new window

0
 
0ffaAuthor Commented:
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. :)


0
 
0ffaAuthor Commented:
I posted part 2 question related to this one:

http://www.experts-exchange.com/Programming/Misc/Q_24163884.html
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.