Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 300
  • Last Modified:

VB code to Create Excel file from Outlook 2007 part 3.1

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
0ffa
Asked:
0ffa
  • 4
  • 2
1 Solution
 
DaveCommented:
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
 
0ffaAuthor Commented:
brettdj,

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

0
 
DaveCommented:
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
Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
0ffaAuthor Commented:
Thx brettdj, your codes working as I need.
I'll post another question related to this question, please assist me.
0
 
0ffaAuthor Commented:
 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
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.

Join & Write a Comment

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

  • 4
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now