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

Frank Sellers
Frank Sellers used Ask the Experts™
on
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.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Top Expert 2016

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
Frank SellersAnalyst/Developer

Author

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
Frank SellersAnalyst/Developer

Author

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.
Ensure you’re charging the right price for your IT

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

Analyst/Developer
Commented:
n/a
NorieAnalyst Assistant

Commented:
ActiveCell means nothing if this code us being run from Access VBA.
Frank SellersAnalyst/Developer

Author

Commented:
n/a

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial