Automation Error in VBA

[Swoootie]

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

 

[Swoootie]

Hi Norie.

I am basically trying to lookup 1 value so for example if the value in cell A1 in the main spreadsheet is X, I am looking for the corresponding value from another worksheet to be populated next to the cell.

It's just looking up 1 value from a range and the main reason I am having to do this multiple times is because there are gaps in the spreadsheet (as I am populating a table which has some fixed values and some variables values that require a vlookup.

Does this make sense?

I've tried practically everything and the only reason I know it can be done is that if I physically open up the 2 spreadsheets and perform a vlookup by typing in the cells it works; however, this needs to happen automatically.

The only way that I've got a vlookup to work before in this spreadsheet is where I've had a list of values (say B1:B12) to be populated via a vlookup that looks across the range e.g. A:C.

Please let me know if you require any further details.

 

[Swoootie]

Hi,

Basically, the problem doesn't appear to be the vlookup but rather the code has an issue with executing the previous bit of code followed by the vlookup in sucession. A way to get rid of the automation error is to save and close the eternal referenced file after trimming the values and then reopening and referencing the file. Whereas this works in most cases, in some of the cases only a couple of the values are returned, but there is no error message.

Any ideas what could be causing this?

Thanks

 

[Norie]

Did you try my suggestion of opening workbooks only when they are needed and closing them immediately after you've finished with them.

' open workbook with data
Set wbSrc = Workbooks.Open("C:\Data.xlsx")

' set reference to Prices worksheet with
Set wsData = wbSrc.Worksheets("Prices")

' set reference to worksheet for results
Set wsDst = ThisWorkbook.Worksheets("Results")

' set reference to range on results worksheet with value to lookup
Set rngLookUp = wsDst.Range("A1").Value 

' lookup on value from Results sheet on Prices worksheet and put value in cell to right
rngLookUp.Offset(, 1).Value = Application.Vlookup(rngLookUp.Value, wsData.Range("A:C"), 3, 0)

' close data workbook now it's finished with
wbSrc.Close SaveChanges:=True

 

[Swoootie]

Hi Norie,
Tried it and it worked thanks. I closed down the workbook and reopened it. The problem didn't appear to be the vlookup itself (which was surprising).
Thanks again for all your help.