VBA works in XP and Office 2003 but not in Win / and Office 2010

[fionanmac]

I am not an expert user and have used this makro for many years but in Office 2010 it does not work, can anybody quickly look and solve. The makro compiles individual excel time sheets into one reporting sheet.

Sub get_sheets()
Dim basebook As Workbook
Dim mybook As Workbook
Set basebook = ThisWorkbook

Dim path As String
Dim excelfile As String
'path = basebook.Sheets(1).Cells(2, 1).Value

path = ""
With Application.FileDialog(msoFileDialogFolderPicker)
.InitialFileName = strDefDir
.Title = strTitle
If .Show = -1 Then GetFolder = .SelectedItems(1)
End With

Get_Timesheets.Show False
Application.ScreenUpdating = False

Dim i As Integer
Dim tmp As Integer
i = 2

excelfile = Dir(path & "*.xls")
Do While excelfile <> ""

Set mybook = Workbooks.Open(path & excelfile)

For j = 12 To 26
tmp = CBool(mybook.Sheets("Time Sheet").Cells(j, 13).Value)

If (tmp = 0) Then
End If

If (tmp <> 0) Then

For k = 1 To 14
If (k = 3) Then
basebook.Sheets(1).Cells(i, k + 1).Value _
= "'" & mybook.Sheets("Time Sheet").Cells(j, k).Value
Else
basebook.Sheets(1).Cells(i, k + 1).Value _
= mybook.Sheets("Time Sheet").Cells(j, k).Value
End If
Next k
basebook.Sheets(1).Cells(i, 1).Value = "" & mybook.Sheets("Time Sheet").Cells(8, 7).Value
basebook.Sheets(1).Cells(i, 17).Value = mybook.Sheets("Time Sheet").Cells(3, 6).Value
basebook.Sheets(1).Cells(i, 18).Value = mybook.Sheets("Time Sheet").Cells(4, 6).Value
basebook.Sheets(1).Cells(i, 16).Value = mybook.Sheets("Time Sheet").Cells(7, 6).Value
basebook.Sheets(1).Cells(i, 19).Value = excelfile
i = i + 1
End If
Next j
mybook.Close SaveChanges:=False
excelfile = Dir 'next one
Loop

Application.ScreenUpdating = True
Unload Get_Timesheets
End Sub

 

[fionanmac]

HI Again

Now its acting differently after I set the language and restarted with English as the default language,

What I have noticed which is different between Office 2003 and 2010 is that after selecting the directory with the File dialog, in the window it says "no items match your search"

This does not appear in 2003.

I tried changing the line
excelfile = Dir(Path & "*.xls") to Dir(Path & "*.x*") and to Dir(Path & "*.*")

but still no success. I believe the problem lies here but still cannot solve.

One step further but still not there

Thanks for helping

 

[Norie]

What's the purpose of the dialog box anyway?

You don't actually appear to do anything with it other than show it.

You do seem to put the selected path in a variable, GetFolder but you don't do anything with that.

 

[fionanmac]

SOLVED

In 2010 I just have to click the directory and not go into the directory

RELIEF

Thanks for all you time and answers

 

[Norie]

So are you using the path returned by the dialog?