help convert this macro from 2003 to 2007

[smiler44]

I have a macro created in Excel 2003 that does not work properly in excel 2007. Although the code below is not complete is is complete all the way to where it fails so I hope someone can guide me.

I am operating the code from c\...\my documents\dashboard\finanace\dashboard.xls
the other workbooks are in c\....\my documents\dashboard\finanace\files

in 2003 wkb is correctly the name of a wokbook in the files directory
in 2007 it is incorrectly dashboard.xls which is the name of the workbook the code is running from.

I do not think that any of the workbooks have yet been saved as 2007 workbooks. I have a mental block about doing this.

I think the line of code that is failing is .filetype = or the if statement
I wonder what else of my 2003 stuff will not work in 2007.

Dim wkb As String 'variable Workbook name
 
        Sub openall()
        Dim lCount As Long
        Dim wbResults As Workbook
        Dim wbCodeBook As Workbook
        ''''''''''''''
        Dim dirpath As String ' HR directory path
        Dim subdir As String ' sub directory of HR
        Dim pathsub As String  'complete path
 
        Application.DisplayAlerts = False
        dirpath = ActiveWorkbook.path
        subdir = "\Files"
        pathsub = dirpath + subdir
 
        '''''''''''''''''''''''
         On Error Resume Next
        Set wbCodeBook = ThisWorkbook
        With Application.FileSearch
        .NewSearch
        .LookIn = pathsub
        .FileType = msoFileTypeExcelWorkbooks
        If .Execute > 0 Then 'Workbooks in folder
            For lCount = 1 To .FoundFiles.Count 'Loop through all.
               'Open Workbook x and Set a Workbook variable to it
               Set wbResults = Workbooks.Open(Filename:=.FoundFiles(lCount), UpdateLinks:=0)
 
               wkb = ActiveWorkbook.Name

any advice?

thank you in advance
smiler44

 

[HaHoBe]

Hi, smiler44,

FileSearch isn´t available in the Application Model of 2007, you could use Dir instead. For an example please see No Application.Filesearch in 2007.

Ciao,
Holger

 

[smiler44]

Thank you. That was helpful. A further search of the internet gave me the method to open a workbook and a modification gave me what I wanted. I could not find the correct method completley to close a workbook but what I found coupled with what I "know" has given me the close method.

sfil being the path and file name I have
workbooks.open sfil and workbooks(sfil).close.

Now to bring all the bits of code together to create a new macro and see if it works.

smiler44

 

[HaHoBe]

Hi, smiler44,

I find it more useful to use an oject for doing so and relying on the object instead of Windows(xxx) or Workbooks(xyz), this is the idea how to go for it:

Sub smiler44()
Dim wkbToOpen As Workbook
Dim strPath As String
Dim strFile As String

strFile = "smiler44.xls"
strPath = "C:\Temp\"

If Not Right(strPath, 1) = "\" Then strPath = strPath & "\"

Set wkbToOpen = Workbooks.Open(strPath & strFile)
wkbToOpen.Sheets(2).Activate

'more code

wkbToOpen.Close savechanges:=False

Set wkbToOpen = Nothing

End Sub

Ciao,
Holger