akiles99
asked on
VB issues in excel 2007
Hi,
This works fine with excel 2003, but now I´m moving to excel 2007 and don´t know how to change "filesearch" code.
Task the macro does:
loking "c:\consultas\" folder and subfolders
File type: excel file (xls, xlsx, etc) ONLY EXCEL FILES
pt.PivotCache.MissingItems Limit = xlMissingItemsNone (change a property)
ActiveWorkbook.RefreshAll => VERY IMPORTANT, I THINK THIS IS WORKING FINE, REFRESH PIVOT TABLE
ActiveWorkbook.Save
ActiveWorkbook.Close
I THINK THE ONLY THING THAT IS NOT WORKING IS "APPLICATION.FILESEARCH
Thanks,
akiles
This works fine with excel 2003, but now I´m moving to excel 2007 and don´t know how to change "filesearch" code.
Task the macro does:
loking "c:\consultas\" folder and subfolders
File type: excel file (xls, xlsx, etc) ONLY EXCEL FILES
pt.PivotCache.MissingItems
ActiveWorkbook.RefreshAll => VERY IMPORTANT, I THINK THIS IS WORKING FINE, REFRESH PIVOT TABLE
ActiveWorkbook.Save
ActiveWorkbook.Close
I THINK THE ONLY THING THAT IS NOT WORKING IS "APPLICATION.FILESEARCH
Thanks,
akiles
Private Sub Workbook_Open()
Application.DisplayAlerts = False
On Error Resume Next
Dim basebook As Workbook
Dim mybook As Workbook
Dim pt As PivotTable
Dim ws As Worksheet
With Application.FileSearch **** here is the problem
.NewSearch
.LookIn = "C:\Consultas\"
.SearchSubFolders = True
.FileType = msoFileTypeExcelWorkbooks
If .Execute() > 0 Then
For i = 1 To .FoundFiles.Count
Set mybook = Workbooks.Open(.FoundFiles(i), UpdateLinks:=0, IgnoreReadOnlyRecommended:=True, corruptload:=0)
For Each ws In ActiveWorkbook.Worksheets
For Each pt In ws.PivotTables
pt.PivotCache.MissingItemsLimit = xlMissingItemsNone
Next pt
Next ws
ActiveWorkbook.RefreshAll
ActiveWorkbook.Save
ActiveWorkbook.Close
Next i
End If
End With
Application.Quit
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.
ASKER
Thanks
ASKER