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
 

Some videos you may like

Excel Facts

Fastest way to copy a worksheet?
Hold down the Ctrl key while dragging tab for Sheet1 to the right. Excel will make a copy of the worksheet.

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
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
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) <> ""
 

Subscribe on YouTube

Watch MrExcel Video

Forum statistics

Threads
1,106,001
Messages
5,508,713
Members
408,690
Latest member
Lip Renan

This Week's Hot Topics

  • Turn fraction around
    Hello I need to turn a fraction around, for example I have 1/3 but I need to present as 3/1
  • TIme Clock record reformatting to ???
    Hello All, I'd like some help formatting this (Tbl-A)(Loaded via Power Query) [ATTACH type="full" width="511px" alt="PQdata.png"]22252[/ATTACH]...
  • TextBox Match
    hi, I am having a few issues with my code below, what I need it to do is when they enter a value in textbox8 (QTY) either 1,2 or 3 the 3 textboxes...
  • Using Large function based on Multiple Criteria
    Hello, I can't seem to get a Large formula to work based on two criteria's. I can easily get a oldest value based one value, but I'm struggling...
  • Can you check my code please
    Hi, Im going round in circles with a Compil Error End With Without With Here is the code [CODE=rich] Private Sub...
  • Combining 2 pivot tables into 1 chart
    Hello everyone, My question sounds simple but I do not know the answer. I have 2 pivot tables and 2 charts that go with this. However I want to...
Top