Link to home
Start Free TrialLog in
Avatar of rogerdjr
rogerdjrFlag for United States of America

asked on

outlook vba code that processed an excel spreadsheet stopped working

User generated imageI have used this vba macro for a long time without issue - I ran it about 6 weeks ago and it worked fine

now when i try to run it i get an error message on "    objWorkBook1.Close savechanges:=True" that says Compile Error: Method or data member not found

Can any body point me to a solution?


 Sub a01_PST_BACKUP_listAllFolders()
    Dim ns As Outlook.NameSpace
    Dim exp As Explorer
    Dim ff As Outlook.Folder
    
    Dim objExcel As Object
    Dim objworkbook As Object
    Dim objWorkBook1 As Workbook
    
    Dim LastRow As Double

    Set objExcel = CreateObject("Excel.Application")
    objExcel.Visible = True
    Set objWorkBook1 = objExcel.Workbooks.Open("j:\Personal\OutlookData\Transfer Outlook Folders.xlsm")
    Set objWorksheet = objWorkBook1.Worksheets("New Folder List")
    
    objExcel.Sheets("New Folder List").Select
    
    objExcel.Range("a1").Select
    
    objExcel.Selection.End(xlDown).Select
    LastRow = objExcel.ActiveCell.Row
    objExcel.Selection.End(xlUp).Select

    objWorksheet.Columns("A:c").Select
    objWorksheet.Range("B459").Activate
    objExcel.Selection.ClearContents
    
    objWorksheet.Columns("e:j").Select
    objExcel.Selection.ClearContents

    objExcel.Cells(1, 5) = "SubFolder"
    objExcel.Cells(1, 6) = "OrigFolderCount"
    objExcel.Cells(1, 7) = "Status"
    objExcel.Cells(1, 8) = "DestFolder"
    objExcel.Cells(1, 9) = "DestFolderCount"
    objExcel.Cells(1, 10) = "FileDateTime"

    objExcel.Range("a1").Select
    
    RowNo = 1
    
    objWorksheet.Cells(RowNo, 1) = "PST FOLDER"
    objWorksheet.Cells(RowNo, 2) = "Dest File"
    objWorksheet.Cells(RowNo, 3) = "Number of Mail Items"
    
    Dim TextFile As Integer
    Dim FilePath As String
    
    'What is the file path and name for the new text file?
    FilePath = "J:\Personal\OutlookData\ListOfFolders" & Format(Now, "yyyy-mm-dd") & ".txt"
    
    'Determine the next file number available for use by the FileOpen function
    TextFile = FreeFile
    
    'Open the text file
    Open FilePath For Output As TextFile
    
    'Write some lines of text
    Print #TextFile, "Start"
      
    'Save & Close Text File
    Close TextFile
 
    Set ns = Application.GetNamespace("MAPI")
    Set exp = ActiveExplorer
    
    For Each ff In ns.Folders
        GetFolderDetails ff
    Next
 
    objExcel.Range("a1").Select
    
    objExcel.Selection.End(xlDown).Select
    LastRow = objExcel.ActiveCell.Row
    objExcel.Selection.End(xlUp).Select

    objWorksheet.Range("D2:S2").Select
    objExcel.Selection.Copy
    objWorksheet.Range("D2:D" & LastRow).Select
    objExcel.ActiveSheet.Paste

        
    objExcel.DisplayAlerts = False
    objWorkBook1.Close savechanges:=True
    
    objExcel.Quit
    Set objExcel = Nothing
 
    MsgBox "Macro Complete"
 
 End Sub

Open in new window

ASKER CERTIFIED SOLUTION
Avatar of Fabrice Lambert
Fabrice Lambert
Flag of France image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of rogerdjr

ASKER

Great solution

I wonder what changed to make the original configuration stop working
Maybe an MS Excel update, that's all I can guess.