Solved

VB code to Create Excel file from Outlook 2007 part 3.1

Posted on 2009-03-30
7
270 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
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
Control application downtime with dependency maps

Visualize the interdependencies between application components better with Applications Manager's automated application discovery and dependency mapping feature. Resolve performance issues faster by quickly isolating problematic components.

 

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: 24025860
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

Zoho SalesIQ

Hassle-free live chat software re-imagined for business growth. 2 users, always free.

Question has a verified solution.

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

If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.

911 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

24 Experts available now in Live!

Get 1:1 Help Now