locked
Automation from Access to Excel RRS feed

  • Question

  • I'm trying to get a piece of code to run from Access to Excel, so that I don't have to write an excel macro. The code replaces the CrLf boxes left during an export.  The function works fine in Excel but when I try to re-work it to run in Access I keep getting an "Object Required" error number 424.  Below is the relevant part of the code. I left out a bunch of formatting which is working perfectly:

    Private Sub cmdMcrIAExport_Click()

        Dim i As Integer

        Dim j As Integer

        Dim strWorksheet As String

        Dim strWorksheetPath As String

        Dim appExcel As Excel.Application

        Dim sht As Excel.Worksheet

        Dim bk As Excel.Workbook

        Dim rng As Excel.Range

        Dim c As Range

        Dim StrTableName As String

        Dim StrRange As String

        Dim Selection As Range

       

       

        strWorksheetPath = "M:\Working Access Reports\"

        strWorksheet = "IA.xls"

        strWorksheetPath = strWorksheetPath & strWorksheet

        Debug.Print "Worksheet path: " & strWorksheetPath

       

        On Error Resume Next

           Kill strWorksheetPath

      

        On Error GoTo ErrorHandler

       

        DoCmd.TransferSpreadsheet acExport, 8, "qryAllExamsLongFinding", "M:\Working Access Reports\IA.xls", True, ""

         Set appExcel = CreateObject("Excel.Application")

        appExcel.Workbooks.Open (strWorksheetPath)

        Set sht = appExcel.ActiveSheet

        sht.Activate

                “A whole bunch of formatting that works perfectly until this line:

                ActiveSheet.UsedRange.Select

                            For each c In appExcel.Selection

                                        C = Replace(c, vbCrLf, vbLf)

                            Next

    The “For Each c In appExcel.Selection…generates an “object required error number 424”

    Any ideas?


    Dean J. Waring

    Monday, April 28, 2014 4:06 PM

Answers

  • There must be a simple explanation! Make a small test file, copy a few sample cells from your main file and save.

    Run the code from Access to open your test file but add a couple of extra lines

        For Each c In rng
            If InStr(1, c, vbCr) Then
                c = Replace(c, vbCrLf, vbLf)
    c.Interior.Color = vbYellow
    Debug.Print c.Address
            End If
        Next
    Addresses of processed cells should be written to the Immediate window (Ctrl-G) and when you reopen your file you should see yellow cells. Or better still add a break and step through with F8 and see what's going on.

    • Marked as answer by DeanJW2006 Wednesday, April 30, 2014 6:39 PM
    Wednesday, April 30, 2014 11:28 AM

All replies

  • That looks like the Excel macro I suggested as fix in your recent post about replacing unprintable characters. Your code will break on ActiveSheet because Access has no idea what ActiveSheet relates to. But you've already set a reference to it like this

    Set sht = appExcel.ActiveSheet

    However that relies on being sure that the sheet you want to process opens as the activesheet, if not sure -

    Dim wb As Excel.Workbook
    Dim ws As Excel.Worksheet
    Dim rng As Excel.Range
    Dim c As Excel.Range
    
        Set wb = appExcel.Workbooks.Open(strWorksheetPath)
        
        Set ws = bk.Worksheets("Sheet1")    ' or whatever name
        Set rng = sht.UsedRange
    
    ' or if sure the wb is active and your sheet opens as the
    ' activeSheet could do this
        
        'Set rng = appExcel.ActiveSheet.UsedRange
        
        For Each c In rng
            If InStr(1, c, vbCr) Then
                c = Replace(c, vbCrLf, vbLf)
            End If
        Next

    note the extra declarations for c and rng as Excel.Range

    If you know the particular area of the sheet to process change .UsedRange to .Range(myAddress)

    In passing suggest heading your module "Option Explicit" without quotes. Not sure what your j As Integer is for but to avoid problems with any value over 32k change the declaration to As Long


    Monday, April 28, 2014 4:49 PM
  • Thank you, again, for your help. Yes, this is what you helped me with before. I was able to replace the "boxy" characters in the excel spreadsheet with your suggestion. Now, I need to incorporate it into an Access 2003 function where I export the data to a spreadsheet and then format the spreadsheet through automation. I actually have a dozen of these applications, so I'm trying to get this one down as a model. 

    I worked with your code yesterday, but am unable to get it to replace the "boxy" characters from Access. I believe that it is just starting in column 1, which does not have any issues, and then ends when it is finished with column 1. I wanted to let you know that I received this and do appreciate your help. I'll keep trying to get it to work.


    Dean J. Waring

    Tuesday, April 29, 2014 12:57 PM
  • It should work the same from Access as it does in Excel. The only difference in Access is you need to fully or 'explicitly' qualify all references back to the reference to Excel. 

    Test everything in Excel first with same code and declarations except in Excel change

    Set appExcel = CreateObject("Excel.Application")

    to

    Set appExcel = Application

    Tuesday, April 29, 2014 2:16 PM
  • I made the change that you suggested, and it worked perfectly in a macro in excel. However, in access, it went through each cell but did not replace the "boxy" characters like it did in excel.

    Dean J. Waring

    Wednesday, April 30, 2014 11:14 AM
  • There must be a simple explanation! Make a small test file, copy a few sample cells from your main file and save.

    Run the code from Access to open your test file but add a couple of extra lines

        For Each c In rng
            If InStr(1, c, vbCr) Then
                c = Replace(c, vbCrLf, vbLf)
    c.Interior.Color = vbYellow
    Debug.Print c.Address
            End If
        Next
    Addresses of processed cells should be written to the Immediate window (Ctrl-G) and when you reopen your file you should see yellow cells. Or better still add a break and step through with F8 and see what's going on.

    • Marked as answer by DeanJW2006 Wednesday, April 30, 2014 6:39 PM
    Wednesday, April 30, 2014 11:28 AM
  • So, I guess that had Dim'd c as a variant which caused the problem. I added c.Interior.Color =vb yellow and the Debug.Print c.Address and it broke at the c.  I changed variant to excel.range and the code ran perfectly....

    I thank you so much for your patience and help. Like I said, I have about a dozen of these take a bunch of data from access, with lots of memo fields, and export it to excel with tons of formatting. This will serve as a basis for that. Again, thank you.


    Dean J. Waring

    Wednesday, April 30, 2014 6:39 PM
  • Can't think why c as Variant would have caused the problem (why didn't it break on the c =  line)  though indeed netter to declare As Excel (early binding with the ref to Excel Tools/refs) or As Object if not. Anyway, glad you've got it working!
    Wednesday, April 30, 2014 7:54 PM