Run-time error 445

francolandra

New Member
Joined
Aug 4, 2010
Messages
9
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
 

Excel Facts

What did Pito Salas invent?
Pito Salas, working for Lotus, popularized what would become to be pivot tables. It was released as Lotus Improv in 1989.

xld

Banned
Joined
Feb 8, 2003
Messages
5,378
FileSearch has been removed from Excel 2007.

Look up Dir in VBA help and use that.
 

francolandra

New Member
Joined
Aug 4, 2010
Messages
9
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

Banned
Joined
Feb 8, 2003
Messages
5,378
Replace the FileExsists function with this

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

francolandra

New Member
Joined
Aug 4, 2010
Messages
9

ADVERTISEMENT

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

New Member
Joined
Aug 4, 2010
Messages
9
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

Banned
Joined
Feb 8, 2003
Messages
5,378

ADVERTISEMENT

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

New Member
Joined
Aug 4, 2010
Messages
9
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) <> ""
 

francolandra

New Member
Joined
Aug 4, 2010
Messages
9
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) <> ""
 

Watch MrExcel Video

Forum statistics

Threads
1,132,818
Messages
5,655,465
Members
418,204
Latest member
ElizabethCorrin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top