[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

VB code to Create Excel file from Outlook 2007

Posted on 2009-02-12
11
Medium Priority
?
351 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

0
Comment
Question by:0ffa
11 Comments
 
LVL 8

Expert Comment

by:JustWorking
ID: 23630146
Not knowing the entire code try (omitting As Object) so it shows:
Dim obj Excel
0
 
LVL 22

Expert Comment

by:danaseaman
ID: 23631043
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
 
LVL 59

Expert Comment

by:Chris Bottomley
ID: 23631298
>> 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
Veeam Disaster Recovery in Microsoft Azure

Veeam PN for Microsoft Azure is a FREE solution designed to simplify and automate the setup of a DR site in Microsoft Azure using lightweight software-defined networking. It reduces the complexity of VPN deployments and is designed for businesses of ALL sizes.

 

Author Comment

by:0ffa
ID: 23677512
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
 
LVL 59

Expert Comment

by:Chris Bottomley
ID: 23678273
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
 

Author Comment

by:0ffa
ID: 23689036
chris_bottomley,

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

Expert Comment

by:Chris Bottomley
ID: 23689146
What is it meant to represent ... in order to initialise it we need to know what/where it is

Chris
0
 

Author Comment

by:0ffa
ID: 23690891
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
 
LVL 59

Accepted Solution

by:
Chris Bottomley earned 2000 total points
ID: 23691098
Are you 100% that the exact filepath exists ... and if so that the file does not already exist there?

Chris
0
 

Author Comment

by:0ffa
ID: 23697938
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
 

Author Comment

by:0ffa
ID: 23697970
I posted part 2 question related to this one:

http://www.experts-exchange.com/Programming/Misc/Q_24163884.html
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Outlook for dependable use in a very small business   This article is about using the Outlook application (part of Microsoft Office) in a very small business, or for homeowners where dependability and reliability are critical requirements. This …
This article will help to fix the below errors for MS Exchange Server 2016 I. Certificate error "name on the security certificate is invalid or does not match the name of the site" II. Out of Office not working III. Make Internal URLs and Externa…
There are cases when e.g. an IT administrator wants to have full access and view into selected mailboxes on Exchange server, directly from his own email account in Outlook or Outlook Web Access. This proves useful when for example administrator want…
Whether it be Exchange Server Crash Issues, Dirty Shutdown Errors or Failed to mount error, Stellar Phoenix Mailbox Exchange Recovery has always got your back. With the help of its easy to understand user interface and 3 simple steps recovery proced…
Suggested Courses
Course of the Month17 days, 14 hours left to enroll

829 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question