Automation Error

[mburkett]

I am getting the following error when I run the macro below on line:
sh.Cells(1, 1) = "Path:"

Run-Time error '-2147221080(800401A8)':
Automation Error

Does anyone have an idea of what might be going on. This macro has ran
for 2 years without a problem. Thanks!


Sub getRMFlist()


Dim sh As Worksheet, lstAttr As Integer
Dim mypath As String, myName As String
Dim rw As Integer, fattr, strAttr As String

'goto worksheet
Sheets("RMF File List").Select

'set a variable to refer to the active sheet in this workbook
Set sh = ActiveSheet
'set key values
lstAttr = vbNormal + vbReadOnly + vbHidden

lstAttr = lstAttr + vbSystem + vbDirectory
lstAttr = lstAttr + vbArchive

Sheets("RMF File List").Select
Application.DisplayAlerts = False
Sheets("RMF File List").Delete
Sheets.Add.Name = "RMF File List"
Range("A1").Select


Sheets("Input").Select
Range("d10").Select
curdirloc = Selection.Value
Sheets("RMF File List").Select

' Set the directory to be analyzed
mypath = curdirloc
' Retrieve the first entry.
myName = Dir(mypath, lstAttr)
'put labels on the sheet at the top of the columns
sh.Cells(1, 1) = "Path:"

 

[Guest]

Hi,

I think you'll find the problem is due to the fact that you are deleting the
sheet that you have made reference to. Although there is still a sheet named
"RMF File List" the actually sheet that you used in the "Set sh = " line has
been deleted, so the reference to it later on "sh.Cells(1,1)" is no longer
valid.

You can either re reference it using "Set" again, or reference it directly
by name "Worksheets("RMF File List").Cells(1,1) = ".

In the portion of code you have shown, you only use the reference to the
sheet "sh" once anyway, the rest of the time you are using sheet names. If
you don't need the reference, and use sheet names throughout you can remove
the "Dim sh As Worksheet" and save yourself a little memory.

I hope this helps,

Sean.

 

[mburkett]

Hi,

I think you'll find the problem is due to the fact that you are deleting the
sheet that you have made reference to. Although there is still a sheet named
"RMF File List" the actually sheet that you used in the "Set sh = " line has
been deleted, so the reference to it later on "sh.Cells(1,1)" is no longer
valid.

You can either re reference it using "Set" again, or reference it directly
by name "Worksheets("RMF File List").Cells(1,1) = ".

In the portion of code you have shown, you only use the reference to the
sheet "sh" once anyway, the rest of the time you are using sheet names. If
you don't need the reference, and use sheet names throughout you can remove
the "Dim sh As Worksheet" and save yourself a little memory.

I hope this helps,

Sean.

--
(please remember to click yes if replies you receive are helpful to you)















- Show quoted text -

Thanks to all who replied. All were a big help. I tried directly
referencing the sheet directly and it was still blowing up. Instead I
moved the delete sheet line to the beginning of the routine and that
seemed to do the trick.

-Michael