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

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.
monboisAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Rey Obrero (Capricorn1)Commented:
<But if I try to export it a 2nd time, I get Run-time Error 13: Type Mismatch.>

post the whole code, the error is getting triggered from somewhere else
0
monboisAuthor Commented:
There's a LOT of code involved in the formatting. This code is executed in 3 different spots in it, and all 3 locations crap out after the first run. There's nothing unusual going on with the rest of the VBA, al standard code I've used many times, though this is the first time I've done this kind of recordset routine in formatting an Excel file.

I noticed that after I reset the VBA window after the error occurs, the data from the form is missing but the code does work again, but just once. I wonder if there's a way of resetting the code without losing the form's data.

These are the 3 Excel variables declared in Access. xlApp is the one used in the line of code, but xlBook & xlSheet don't work either:

' declare Excel variables
' ===============================
  Dim xlApp As Excel.Application
  Dim xlBook As Excel.Workbook
  Dim xlSheet As Excel.Worksheet


This is the first recordset where the line of code is used:

' F O R M A T   D A T E   C O L U M N S
' =========================================================================
  Dim sLbl As String
' ---------------------------------------------------------------------------------------------------------------------------------
  sQry = _
    "SELECT DISTINCT " & _
      "Label, TabIndex, Rpt_Fld " & _
    "From " & _
      "Tracker_SKUs_Field_Widths " & _
    "WHERE " & _
      "(([Type]='Date') AND (Rpt_Fld=True)) " & _
    "ORDER BY " & _
      "TabIndex;"

' assign Query string to Recordset variable
' ==========================================
  Set rst = CurrentDb.OpenRecordset(sQry)

' cycle through all Records in Recordset
' ==========================================
  Do While Not rst.EOF

'   assign Label variable
'   ----------------------
    sLbl = (rst!Label)

'   select Header Row 1
'   ------------------------------------------------------------------------
    xlApp.Range("A1").Select
    xlApp.Range(xlApp.Selection, xlApp.Selection.End(xlToRight)).Select

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

'   format column as 'm/d/yy'
'   ------------------------------------------------------------------------
    xlApp.Columns(ActiveCell.Column).Select
    xlApp.Selection.NumberFormat = "m/d/yy;@"

    rst.MoveNext

'   exit loop if End of Recordset, else got to next field
'   ------------------------------------------------------
    If (rst.EOF = True) Then
      GoTo DateDone
    End If

  Loop
DateDone:
  rst.Close
0
monboisAuthor Commented:
Attached to this message is a text file with all the VBA used & referenced in the sub-routine. I've eliminated the 'sLbl' string variable because it's actually not necessary in the recordset. No change in the functionality, unfortunately: still works just once.

Thanks.
0
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

monboisAuthor Commented:
n/a
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
NorieAnalyst Assistant Commented:
ActiveCell means nothing if this code us being run from Access VBA.
0
monboisAuthor Commented:
n/a
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
VBA

From novice to tech pro — start learning today.