Avatar of Frank Sellers
Frank Sellers
Flag for United States of America asked on

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.
VBAMicrosoft ExcelMicrosoft AccessMicrosoft ApplicationsMicrosoft Development

Avatar of undefined
Last Comment
Frank Sellers

8/22/2022 - Mon
Rey Obrero (Capricorn1)

<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
Frank Sellers

ASKER
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
Frank Sellers

ASKER
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.
This is the best money I have ever spent. I cannot not tell you how many times these folks have saved my bacon. I learn so much from the contributors.
rwheeler23
ASKER CERTIFIED SOLUTION
Frank Sellers

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
Norie

ActiveCell means nothing if this code us being run from Access VBA.
Frank Sellers

ASKER
n/a