troubleshooting Question

MS access VBA Formatting MS Excel File: Run-Time Error '13' (Type Mismatch) on Second Run

Avatar of Frank Sellers
Frank SellersFlag for United States of America asked on
Microsoft OfficeMicrosoft AccessMicrosoft ExcelMicrosoft ApplicationsMicrosoft Development
6 Comments1 Solution330 ViewsLast Modified:
I am exporting an Excel file from MS Access. As part of the formatting from the Access VBA to the Excel file is to find specific text in a range of cells and then activate the cell with that text. The code is executed from a command button on a form. The first time I export the report it works fine. But if I try to export it a 2nd time, I get Run-time Error 13: Type Mismatch.

This is the line of code:

'   go to Date Cell
'   ------------------------------------------------------------------------
    xlApp.Cells.Find(What:=sLbl, After:=ActiveCell, LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=True, SearchFormat:=False).Activate

sLbl is a string variable, but the error occurs even when the value is hard-coded between quotes, e.g., "Ship Date". The variable is populated in a recordset that obtains the text from a database table.

I've tried refreshing and repainting the form, but that doesn't work. The only thing that seems to work is closing/re-opening the form, which is an inconvenient and sloppy "solution."

Thanks.
ASKER CERTIFIED SOLUTION
Frank Sellers
Analyst/Developer

Our community of experts have been thoroughly vetted for their expertise and industry experience.

Join our community to see this answer!
Unlock 1 Answer and 6 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 6 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros