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

 

[shg]

Welcome to the board.

What doesn't work, what happens?

 

[fionanmac]

it allows me to set the directory and then stops here

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

I am using Office 2010 with an english language pack.

Wish I stayed with 2003

Tx

 

[shg]

What happens when it stops? Do you get an error message? What is the error message? What is in that cell when the code stops?

 

[fionanmac]

When I debug I get the following

Runtime error 9
Subscript out of range

The funny thing is it works with no problem in 2003

Tx

 

[shg]

Sounds like the workbook you just opened doesn't contain a worksheet named "Time Sheet"

 

[fionanmac]

No, it contains a sheet called "Time Sheet", also it works with 2003. I have a feeling that its to do with a german version of excel with an english language pack.

Funnily 1 in 100 times it works if I step through the vba code

 

[Norie]

Are you 100% sure there is a worksheet called 'Time Sheet' in all the workbooks being opened?

How did you check?

 

[fionanmac]

Yes am sure, also test the files parallel in xp

Tx

 

[Norie]

There's nothing I know of that would be affected by using different versions of Excel that would cause this.

By the way, why do you never give path a value?

You've commented out the only line of code that would do that:

'path = basebook.Sheets(1).Cells(2, 1).Value

In fact the next line of code sets path to the empty string, "".