We help IT Professionals succeed at work.

We've partnered with Certified Experts, Carl Webster and Richard Faulkner, to bring you a podcast all about Citrix Workspace, moving to the cloud, and analytics & intelligence. Episode 2 coming soon!Listen Now

x

VB code to Create Excel file from Outlook 2007

Medium Priority
382 Views
Last Modified: 2012-05-06
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

Comment
Watch Question

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

Commented:
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
CERTIFIED EXPERT
Top Expert 2011

Commented:
>> 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
Foffaf FoffafCommercial Manager

Author

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.
CERTIFIED EXPERT
Top Expert 2011

Commented:
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?
Foffaf FoffafCommercial Manager

Author

Commented:
chris_bottomley,

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

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

Chris
Foffaf FoffafCommercial Manager

Author

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

CERTIFIED EXPERT
Top Expert 2011
Commented:
Are you 100% that the exact filepath exists ... and if so that the file does not already exist there?

Chris

Not the solution you were looking for? Getting a personalized solution is easy.

Ask the Experts
Foffaf FoffafCommercial Manager

Author

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. :)


Foffaf FoffafCommercial Manager

Author

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

http://www.experts-exchange.com/Programming/Misc/Q_24163884.html
Access more of Experts Exchange with a free account
Thanks for using Experts Exchange.

Create a free account to continue.

Limited access with a free account allows you to:

  • View three pieces of content (articles, solutions, posts, and videos)
  • Ask the experts questions (counted toward content limit)
  • Customize your dashboard and profile

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.