Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Automation Error
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:" |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Automation Error
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) "mburkett" wrote: 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:" |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Automation Error
On Oct 27, 2:10 pm, SeanC UK
wrote: 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) "mburkett" wrote: 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:"- Hide quoted text - - 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Run-Time Error'-2147221080(800401a8)': Automation Error | Excel Programming | |||
Compile error automation error | Excel Programming | |||
compile error automation error | Excel Programming | |||
Automation Error, Unknown Error. Error value - 440 | Excel Programming | |||
Unknown where is the problem on the Runtime error - Automation error | Excel Programming |