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."

Frank Sellers

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