Solved

VB code to Create Excel file from Outlook 2007 part 3.1

Posted on 2009-03-30
7
268 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
  • 4
  • 2
7 Comments
 
LVL 50

Accepted Solution

by:
Dave Brett earned 500 total points
Comment Utility
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
Comment Utility
brettdj,

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

0
 
LVL 50

Expert Comment

by:Dave Brett
Comment Utility
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
Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

 

Author Closing Comment

by:0ffa
Comment Utility
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
Comment Utility
0
 

Author Comment

by:0ffa
Comment Utility
 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

How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

Join & Write a Comment

Resolve Outlook connectivity issues after moving mailbox to new Exchange 2016 server
Outlook Free & Paid Tools
This Experts Exchange video Micro Tutorial shows how to tell Microsoft Office that a word is NOT spelled correctly. Microsoft Office has a built-in, main dictionary that is shared by Office apps, including Excel, Outlook, PowerPoint, and Word. When …
To add imagery to an HTML email signature, you have two options available to you. You can either add a logo/image by embedding it directly into the signature or hosting it externally and linking to it. The vast majority of email clients display l…

771 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

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now