Run-time error 445

[francolandra]

Dear all

I moved from Excel 2003 to Excel 2010 and now I cannot run macros that I ran using the previous Excel 2003 version.

The message I received when ticking the Macro box in Excel is:

Microsoft Visual Basic
Run-time error '445':
Object doesn't support this action

When I tick the Debug button I receive the next message:

"Sub ImportFXRates()
'
' ImportFXRates Macro
' Macro recorded 22/08/2008 by Andy
'
Dim oldWB As Workbook
Dim newWB As Workbook
Dim ws As Worksheet
Dim cur_Year As String
Dim cur_mon As String
Dim c As Object
Dim i As Integer
Dim flName As String
Dim MonthRef As Integer
Set newWB = ActiveWorkbook
Set ws = ActiveSheet

Application.EnableEvents = False
Application.Calculation = xlCalculationManual
Application.ScreenUpdating = False

With ThisWorkbook
cur_Year = .Names!FYREF.RefersToRange.Text
cur_mon = .Names!MONREF.RefersToRange.Text
flName = .Names!FXDir.RefersToRange.Value & "\ER" & Right(cur_Year, 2) & .Names!MONREF.RefersToRange.Text & ".xls"

If FileExists(flName) Then
Application.StatusBar = "Importing " & flName
Workbooks.Open FileName:=flName, UpdateLinks:=0
Set oldWB = ActiveWorkbook

ActiveSheet.Range("A1:D39").Select
Selection.Copy

newWB.Activate


.Activate
.Names("FX").RefersToRange.Select
Selection.PasteSpecial Paste:=xlPasteValues
Application.CutCopyMode = False
Else
'her error message
End If


oldWB.Close (False)


End With

ws.Activate
Application.StatusBar = ""
Application.EnableEvents = True
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True

MsgBox ("Import Complete")
End Sub
Public Function FileExists(FileName As String) As Boolean
'-------------------------------------------------------------------------
' Author : Philipp Schaefer
' Created : 10-05-2008
' Purpose : Checks if the provided File exists
' Modified :
'-------------------------------------------------------------------------

Dim Pos As Integer

Pos = InStrRev(FileName, "\")

With Application.FileSearch
.NewSearch
.LookIn = Left(FileName, Pos - 1)
.FileName = Mid(FileName, Pos + 1)
.Execute
End With
If Application.FileSearch.FoundFiles.Count = 0 Then
FileExists = False
Else
FileExists = True


End If
End Function"

Could someone help me to fix this error please?

Thank you very much

Franco

 

[xld]

FileSearch has been removed from Excel 2007.

Look up Dir in VBA help and use that.

 

[francolandra]

Hi,

Thank you for your answer. I am not an IT person so could you please expand your answer a little bit? This will help me to understand it.

Thanks

Franco

 

[xld]

Replace the FileExsists function with this

Public Function FileExists(FileName As String) As Boolean
    FileExists = Dir(FileName) <> ""
End Function

 

[francolandra]

Thanks again. Is this the correct formula in your opinion? There are 2 lines where I could replace FileExists (either True or False). Thanks again. I really appreciate your advice.

Dim Pos As Integer

Pos = InStrRev(FileName, "\")

With Application.FileSearch
.NewSearch
.LookIn = Left(FileName, Pos - 1)
.FileName = Mid(FileName, Pos + 1)
.Execute
End With
If Application.FileSearch.FoundFiles.Count = 0 Then
FileExists = Dir(FileName) <> ""
Else
FileExists = Dir(FileName) <> ""

 

[francolandra]

One last question. I have Macros in several spreadsheets. Should I go over each one and change the visual basic code or there is a way to make the change just one time for all the spreadsheets?

Thanks

 

[xld]

If you have the co9de in several, you will haveto change several I am afraid.

You could make an addin, but that will prob ably be even more work for you.

 

[francolandra]

Thanks so the code below is correct for you?

Dim Pos As Integer

Pos = InStrRev(FileName, "\")

With Application.FileSearch
.NewSearch
.LookIn = Left(FileName, Pos - 1)
.FileName = Mid(FileName, Pos + 1)
.Execute
End With
If Application.FileSearch.FoundFiles.Count = 0 Then
FileExists = Dir(FileName) <> ""
Else
FileExists = Dir(FileName) <> ""

 

[xld]

Thanks so the code below is correct for you?

What do you mean?

 

[francolandra]

Hi, what I meant is if the formula (Code- sorry I dont know the correct terminology) is correct for you. I replaced FileExists=True for FileExists = Dir(FileName) <> "" and I replaced FileExists=False for FileExists = Dir(FileName) <> ""

Is the correct for you? I am copying all the formula below

Microsoft Visual Basic
Run-time error '445':
Object doesn't support this action

When I tick the Debug button I receive the next message:

"Sub ImportFXRates()
'
' ImportFXRates Macro
' Macro recorded 22/08/2008 by Andy
'
Dim oldWB As Workbook
Dim newWB As Workbook
Dim ws As Worksheet
Dim cur_Year As String
Dim cur_mon As String
Dim c As Object
Dim i As Integer
Dim flName As String
Dim MonthRef As Integer
Set newWB = ActiveWorkbook
Set ws = ActiveSheet

Application.EnableEvents = False
Application.Calculation = xlCalculationManual
Application.ScreenUpdating = False

With ThisWorkbook
cur_Year = .Names!FYREF.RefersToRange.Text
cur_mon = .Names!MONREF.RefersToRange.Text
flName = .Names!FXDir.RefersToRange.Value & "\ER" & Right(cur_Year, 2) & .Names!MONREF.RefersToRange.Text & ".xls"

If FileExists(flName) Then
Application.StatusBar = "Importing " & flName
Workbooks.Open FileName:=flName, UpdateLinks:=0
Set oldWB = ActiveWorkbook

ActiveSheet.Range("A1:D39").Select
Selection.Copy

newWB.Activate


.Activate
.Names("FX").RefersToRange.Select
Selection.PasteSpecial Paste:=xlPasteValues
Application.CutCopyMode = False
Else
'her error message
End If


oldWB.Close (False)


End With

ws.Activate
Application.StatusBar = ""
Application.EnableEvents = True
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True

MsgBox ("Import Complete")
End Sub
Public Function FileExists(FileName As String) As Boolean
'-------------------------------------------------------------------------
' Author : Philipp Schaefer
' Created : 10-05-2008
' Purpose : Checks if the provided File exists
' Modified :
'-------------------------------------------------------------------------

Dim Pos As Integer

Pos = InStrRev(FileName, "\")

With Application.FileSearch
.NewSearch
.LookIn = Left(FileName, Pos - 1)
.FileName = Mid(FileName, Pos + 1)
.Execute
End With
If Application.FileSearch.FoundFiles.Count = 0 Then
FileExists = Dir(FileName) <> ""
Else
FileExists = Dir(FileName) <> ""