Solved

VB issues in excel 2007

Posted on 2008-10-22
3
192 Views
Last Modified: 2012-05-05
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.MissingItemsLimit = 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
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

Open in new window

0
Comment
Question by:akiles99
  • 2
3 Comments
 
LVL 81

Accepted Solution

by:
zorvek (Kevin Jones) earned 500 total points
Comment Utility
The FileSearch object was removed in Excel 2007.

Here is a discussion of alternative solutions:

Title: Application.FileSearch is gone in Excel 2007 -- Alternatives
Link: http://www.mrexcel.com/forum/showthread.php?t=268046

Kevin
0
 

Author Comment

by:akiles99
Comment Utility
I read it but i can't find how to include that snippet into mine...
0
 

Author Closing Comment

by:akiles99
Comment Utility
Thanks
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
Query Missing Money orders... 6 53
Visual Studio 2005 text editor 10 18
VBA in SharePoint 3 16
Add macros on Open 8 22
Have you ever wanted to restrict the users input in a textbox to numbers, and while doing that make sure that they can't 'cheat' by pasting in non-numeric text? Of course you can do that with code you write yourself but it's tedious and error-prone …
Article by: Martin
Here are a few simple, working, games that you can use as-is or as the basis for your own games. Tic-Tac-Toe This is one of the simplest of all games.   The game allows for a choice of who goes first and keeps track of the number of wins for…
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.

771 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

14 Experts available now in Live!

Get 1:1 Help Now