VB code to Create Excel file from Outlook 2007 part 3

YOu have on error resume next active.

When you create the excel workbook you use objexcel which is undefined therefore there is no workbook to save.

Try as attached

Chris

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
    
    Set objExcel = Nothing
    Set objFSO = Nothing
    Set objFolder = Nothing
End Sub

Select allOpen in new window

0ffa,

Any update?

chris_bottomley

chris_bottomley,

I tried the code, when I ran it seems like the program stuck for a minute. I check on the path, its correctly created the file for me, but I can't open the file unless I do 'end task' the excel through Task Manager. So it seems the code doesn't exit or close the excel properly.

Sorry for replying this late, please bear with me coz I only can try this programming stuff on my spare time. Thx.

The code is working Chris :)

chris_bottomley,

I post a new related question in:
https://www.experts-exchange.com/questions/24279744/VB-code-to-Create-Excel-file-from-Outlook-2007-part-3-1.html