rogerdjr
asked on
outlook vba code that processed an excel spreadsheet stopped working
I 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?
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Maybe an MS Excel update, that's all I can guess.
ASKER
I wonder what changed to make the original configuration stop working