Link to home
Start Free TrialLog in
Avatar of Frank Sellers
Frank SellersFlag 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.
Avatar of Rey Obrero (Capricorn1)
Rey Obrero (Capricorn1)
Flag of United States of America image

<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
Avatar of 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
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.
ASKER CERTIFIED SOLUTION
Avatar of Frank Sellers
Frank Sellers
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of Norie
Norie

ActiveCell means nothing if this code us being run from Access VBA.
n/a