Automation Error in VBA

Swoootie

Board Regular
Joined
Jun 28, 2012
Messages
66
Hi.

I have been attempting to perform a vlookup between 2 sheets and have just encountered the following error message:
Run-Time Error '-2147221080 (800401a8)':
Automation Error

What I am attempting to do is reference an eternal workbook where the information is trimmed and copied and pasted as values to match the destination cells and manually this works. When I try to hard code this using VBA, the error message pops up at the point at which the vlookup code is executed (so the trimming of the cells and copying across works fine).

A cut version of my code is below:

Option Explicit

Sub PORTMON()
Dim DT As Date
'INPUT BOX TAKES DATE FROM USER
DT = Application.InputBox("Enter a Date as MMM YYYY")
DT = Format(DT, "MMM YYYY")

Dim PATH As String
Dim WKBOOKATT2 As Workbook
Dim FILENAME2 As String

PATH = "C:\Checks\"

FILENAME6 = "ACC_" & Left(Format(DT, "MMM YYYY"), 3) & "_" & Right(Format(DT, "MMM YYYY"), 4)
Set WKBOOKATT2 = Workbooks.Open(PATH & "ACC_" & Left(Format(DT, "MMM YYYY"), 3) & "_" & Right(Format(DT, "MMM YYYY"), 4) & ".XLS")

Dim APATT1 As Variant

Workbooks.Open (SASPATH & "ACC_" & Left(Format(DT, "MMM YYYY"), 3) & "_" & Right(Format(DT, "MMM YYYY"), 4) & ".XLS")
Range("F2").Select
ActiveCell.FormulaR1C1 = "=TRIM(RC[-5])"
Range("F2").Select
Selection.AutoFill Destination:=Range("F2:F14")
Range("F2:F14").Select
Range("B2:B14").Select
Selection.Copy
Range("G2").Select
ActiveSheet.Paste
Range("F2:F14").Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False

Windows( _
"Acc Main.xls"). _
Activate
Sheets("Stability").Select
Range("D20").Select
'error occurs here
APATT1 = Application.VLOOKUP(Range("A20").VALUE, WKBOOKATT2.Sheets(FILENAME2).Range("F2:G15"), 2, False)
Range("D20").VALUE = APATT1

Has anyone come across anything like this before?

Please let me know if you require any further details from me.

Thanks
 

Excel Facts

Does the VLOOKUP table have to be sorted?
No! when you are using an exact match, the VLOOKUP table can be in any order. Best-selling items at the top is actually the best.
Apologies. As this is edited from a long bit of code I left this out of what I posted above.

It is:

FILENAME2 = "ACC" & Left(Format(DT, "MMM YYYY"), 3) & "_" & Right(Format(DT, "MMM YYYY"), 4)
 
Upvote 0
Have you checked all the values of the variables being used on the line of code that causes the error?
 
Upvote 0
Hi Norie. Yes I have. In fact it works perfectly when done manually and the values that have been assigned work well in other parts of the code (the automation project I am working on is quite big and I'm trying to learn how to code in VBA as I'm going along).
 
Upvote 0
Did you step through the code with F8 and check everything?

PS What do you mean it works 'manually'?
 
Upvote 0
Hi. Apologies for the delay in replying. Yes I have stepped through with F8 to check everything. In fact I have referenced the external workbook many times within the code and have vlookups from the external sheet that actually work.
What I meant by "manually" was that if I perform the vlookups without the use of VBA (ie through typing it in the actual cells), they work perfectly. So I know that the problem doesn't lie within the data itself or the relationship between the 2 sheets.
I hope this helps in clearing some stuff up, but if you require any further details, please let me know.
Does "automation error" relate to a specific known problem?
 
Upvote 0
Hi. Has anyone had a chance to look into this yet? I've looked everywhere that I can think of for the solution but I can't find anything at all. Any advice or help would be really appreciated.

Thanks
 
Upvote 0

Forum statistics

Threads
1,213,546
Messages
6,114,251
Members
448,556
Latest member
peterhess2002

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
Back
Top