[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now

x
?
Solved

VB code to Create Excel file from Outlook 2007 part 3.1

Posted on 2009-03-30
7
Medium Priority
?
292 Views
Last Modified: 2012-05-06
When I changed the path c:\deleteme\test.xlsx to c:\deleteme\vba\test.xlsx my code asks "Do you want to save the changes you made to test.xlsx?", what happened?
Its seems like the code only capable to create 1 level folder olny.

But the code from part 2 of my question, the one with a function, using the same path its working fine for any levels. Could you please explain in more detail and what should I change in the code so it'll be able to create any level of folders without function?

Thx.

Note:
This question is related to these question

VB code to Create Excel file from Outlook 2007 part 3 - http://www.experts-exchange.com/Software/Office_Productivity/Groupware/Outlook/Q_24237135.html#a24017516

VB code to Create Excel file from Outlook 2007 part 2 - http://www.experts-exchange.com/Programming/Misc/Q_24163884.html#a23699562

VB code to Create Excel file from Outlook 2007 part 1 - http://www.experts-exchange.com/Programming/Languages/Visual_Basic/Q_24139919.html
Sub CreateExcel2()
    Dim objExcel As Object
    Dim sExcelFilePath As String
    Dim objFSO As Object
    Dim objFolder As Object
    
    Set objFSO = CreateObject("Scripting.FileSystemObject")
    sExcelFilePath = "c:\deleteme\test.xlsx"
    Set objExcel = CreateObject("excel.application")
    
    On Error Resume Next
    
    Set objFolder = objFSO.GetFolder(objFSO.GetParentFolderName(sExcelFilePath))
    
    On Error Resume Next
    
    If TypeName(objFolder) Then
        Set objFolder = objFSO.CreateFolder(objFSO.GetParentFolderName(sExcelFilePath))
    End If
    
    objExcel.Workbooks.Add
    objExcel.ActiveWorkbook.SaveAs sExcelFilePath
    objexcel.quit
    
    Set objExcel = Nothing
    Set objFSO = Nothing
    Set objFolder = Nothing
End Sub

Open in new window

0
Comment
Question by:0ffa
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 2
7 Comments
 
LVL 50

Accepted Solution

by:
Dave Brett earned 2000 total points
ID: 24025641
Something as below will create you path if it doesn't exist
And it will suppress the Alert on the SaveAs
Cheers
Dave

Option Explicit
 
 
Sub CreateExcel2()
    Dim objExcel As Object
    Dim sExcelFolderPath As String, sExcelFileName As String
    Dim objFSO As Object
    Dim objFolder As Object
    
    Set objFSO = CreateObject("Scripting.FileSystemObject")
    sExcelFolderPath = "c:\deleteme\newpath\"
    sExcelFileName = "test.xlsx"
    Set objExcel = CreateObject("excel.application")
    
    On Error Resume Next
    Set objFolder = objFSO.GetFolder(sExcelFolderPath)
    On Error GoTo 0
    If objFolder Is Nothing Then objFSO.createfolder (sExcelFolderPath)
 
    
    objExcel.Workbooks.Add
    objExcel.DisplayAlerts = False
    objExcel.ActiveWorkbook.SaveAs (sExcelFolderPath & sExcelFileName)
    objExcel.Quit
    objExcel.DisplayAlerts = True
    
    Set objExcel = Nothing
    Set objFSO = Nothing
    Set objFolder = Nothing
End Sub

Open in new window

0
 

Author Comment

by:0ffa
ID: 24025714
brettdj,

Why do you need to suppress the Alert on the SaveAs?

0
 
LVL 50

Expert Comment

by:Dave Brett
ID: 24025743
In case you are overwriting an existing file (ie if you rerun the code with the same paths)
If the file is being created from scratch then you will not get this message
This i suspect if why you had the "Do you want to save the changes you made to test.xlsx?" message
Cheers
Dave
0
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!

 

Author Closing Comment

by:0ffa
ID: 31564673
Thx brettdj, your codes working as I need.
I'll post another question related to this question, please assist me.
0
 

Author Comment

by:0ffa
ID: 24025868
 Continuing question in:

VB code to Create Excel file from Outlook 2007 part 4
http://www.experts-exchange.com/Software/Office_Productivity/Groupware/Outlook/Q_24279935.html
   

0

Featured Post

NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

Question has a verified solution.

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

This article describes how to use a set of graphical playing cards to create a Draw Poker game in Excel or VB6.
Are you looking for the options available for exporting EDB files to PST? You may be confused as they are different in different Exchange versions. Here, I will discuss some options available.
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…
This video shows how to remove a single email address from the Outlook 2010 Auto Suggestion memory. NOTE: For Outlook 2016 and 2013 perform the exact same steps. Open a new email: Click the New email button in Outlook. Start typing the address: …

649 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