Results 1 to 11 of 11
  1. #1
    RayMilhon is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    Aug 2011
    Location
    Southern California
    Posts
    1,065

    Export To Excel Error

    I have the following code in an Access Database. The issue we're trying to solve is we have an Excel Template that contains 45 Worksheets. The data for each worksheet comes from a SQL Server Query. The current process is to run each query and copy the data to the appropriate worksheet. I've redone the queries so they are now SQL Server Views. I have an Access 2010 that links to the SQL Server Views. Now what I'm trying to do is set up subroutine that can be called from the autoexec Macro to run VBA Code to grab each view and export to the appropriate worksheet in the template and save the template as an Excel Workbook. I have a table in Access that links each view to the appropriate Worksheet. Where I'm having a problem is in the line xlApp.Workbooks(WBName).Sheets(rs!worksheet).Activ ate I'm getting a runtime error 13 Type Mismatch. rs!Worksheet is the name of the worksheet I want to edit. WBName is the name of the workbook. What am I missing?




    Code:
    Public Sub export_to_Excel()
    
    Dim rs As New ADODB.Recordset, lpctr As Long, WS As New ADODB.Recordset
    Dim introw As Long, strsql As String, irow As Long, rsout As New ADODB.Recordset, strfld As String, lastrow As Long, lastcol As Long
    Dim WBName As String
    
    
    xlApp.Workbooks.Open "\\192.168.114.17\hvvhomefolders\rmilhon\unitedhealthcare_Remediation_2018\TEMPLATE - HVVMG - Oversight Monitoring Reports (SR_Part 1 of 2)_Updated_01282019_.xlsx"
    xlApp.Workbooks(1).SaveAs "Oversight Monitoring Reports (SR_Part 1 of 2)_" & Format(Now(), "mmddyyyy") & ".xlsx"
    WBName = Workbooks(1).Name
    strsql = "SELECT * FROM tbl_worksheetformats;"
    
    rs.Open strsql, CurrentProject.Connection, adOpenForwardOnly, adLockReadOnly
    
    Do Until rs.EOF
        irow = rs!startrow
        xlApp.Workbooks(1).Sheets(rs!Worksheet).Activate
        strsql = "Select * From " & rs!Table
        rsout.Open strsql, CurrentProject.Connection, adOpenForwardOnly, adLockReadOnly
           Do Until rsout.EOF
                strsql = "Select * From tbl_worksheetformats Where Worksheet = """ & rs!Worksheet & """"
                WS.Open strsql, CurrentProject.Connection, adOpenForwardOnly, adLockReadOnly
                Do Until WS.EOF
                    Set cell = xlApp.Workbooks(1).ActiveSheet.Cells(irow, WS!Column)
                    strfld = WS!Field
                    cell.Value = rsout.Fields(strfld)
                    WS.MoveNext
                Loop
                
                WS.Close
                irow = irow + 1
                rsout.MoveNext
            Loop
            Set cell = xlApp.Workbooks(1).ActiveSheet.Cells(irow + 1, rs!endCol)
            cell.Value = irow
            rsout.Close
        rs.MoveNext
    Loop
    rs.Close
    xlApp.Workbooks(1).Close
    End Sub

  2. #2
    ssanfu is offline Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    You must not have the line
    Code:
    Option Explicit
    as the 2nd line in EVERY code module. In the code above, "xlApp" and "cell" have NOT been declared.


    Also, in this line
    Code:
    WBName = Workbooks(1).Name
    workbooks must be fully qualified because you are using automation.
    It has bee a while since I've used automation to control Excel, but maybe try
    Code:
    WBName = xlApp.Workbooks(1).Name

    Ken Snell's site has code examples for Exporting to Excel from Access

  3. #3
    RayMilhon is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    Aug 2011
    Location
    Southern California
    Posts
    1,065
    This is what's at the top of the module. Should have included it earlier but didn't. I automatically put the Option Explicit in every module I create.

    Code:
    Option Compare Database
    Option Explicit
    Public xlApp As New Excel.Application
    Public cell As Object

  4. #4
    CJ_London is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    I don't see

    xlApp.Workbooks(WBName).Sheets(rs!worksheet).Activ ate

    in your code

    I see

    xlApp.Workbooks(1).Sheets(rs!Worksheet).Activate

  5. #5
    accesstos's Avatar
    accesstos is offline Expert
    Windows XP Access 2007
    Join Date
    Dec 2018
    Location
    Greece
    Posts
    551
    It don't need activate a sheet to work with it.
    So, you can say:
    Code:
    [...]
    Do Until rs.EOF
        With xlApp.Workbooks(WBName).Sheets(rs!Worksheet)
        irow = rs!startrow
        strsql = "Select * From " & rs!Table
        rsout.Open strsql, CurrentProject.Connection, adOpenForwardOnly, adLockReadOnly
           Do Until rsout.EOF
                strsql = "Select * From tbl_worksheetformats Where Worksheet = """ & rs!Worksheet & """"
                WS.Open strsql, CurrentProject.Connection, adOpenForwardOnly, adLockReadOnly
                Do Until WS.EOF
                    .Cells(irow, WS!Column) = rsout.Fields(WS!Field)
                    WS.MoveNext
                Loop
                WS.Close
                irow = irow + 1
                rsout.MoveNext
            Loop
            .Cells(irow + 1, rs!endCol) = irow
            rsout.Close
        rs.MoveNext
        End With
    Loop
    [...]

  6. #6
    CJ_London is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    you might also find a significant performance benefit to use your loop to build your select query, then use copyfromrecordset to update Excel

  7. #7
    RayMilhon is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    Aug 2011
    Location
    Southern California
    Posts
    1,065
    Quote Originally Posted by Ajax View Post
    I don't see

    xlApp.Workbooks(WBName).Sheets(rs!worksheet).Activ ate

    in your code

    I see

    xlApp.Workbooks(1).Sheets(rs!Worksheet).Activate
    I've done it both ways same result

  8. #8
    RayMilhon is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    Aug 2011
    Location
    Southern California
    Posts
    1,065
    Quote Originally Posted by accesstos View Post
    It don't need activate a sheet to work with it.
    So, you can say:
    Code:
    [...]
    Do Until rs.EOF
        With xlApp.Workbooks(WBName).Sheets(rs!Worksheet)
        irow = rs!startrow
        strsql = "Select * From " & rs!Table
        rsout.Open strsql, CurrentProject.Connection, adOpenForwardOnly, adLockReadOnly
           Do Until rsout.EOF
                strsql = "Select * From tbl_worksheetformats Where Worksheet = """ & rs!Worksheet & """"
                WS.Open strsql, CurrentProject.Connection, adOpenForwardOnly, adLockReadOnly
                Do Until WS.EOF
                    .Cells(irow, WS!Column) = rsout.Fields(WS!Field)
                    WS.MoveNext
                Loop
                WS.Close
                irow = irow + 1
                rsout.MoveNext
            Loop
            .Cells(irow + 1, rs!endCol) = irow
            rsout.Close
        rs.MoveNext
        End With
    Loop
    [...]
    Maybe you didn't see my original post but there are 48 tables that have to be plugged into 48 worksheets.

  9. #9
    RayMilhon is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    Aug 2011
    Location
    Southern California
    Posts
    1,065
    Quote Originally Posted by Ajax View Post
    you might also find a significant performance benefit to use your loop to build your select query, then use copyfromrecordset to update Excel
    I'll check into that. Never used copyfromrecordset.

  10. #10
    ssanfu is offline Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    Ken Snell's site also has code example for the Excel command "copyfromrecordset"
    http://www.accessmvp.com/KDSnell/EXC...ExpCopyFromRst


    48 tables!!??


    Good luck.....

  11. #11
    RayMilhon is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    Aug 2011
    Location
    Southern California
    Posts
    1,065
    Finally figured out the problem. My Code was fine the template that Corporate provided had the Worksheet names with periods and spaces and doing the process in VBA it didn't like it.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Date out of range error on Excel Export
    By RCG in forum Import/Export Data
    Replies: 1
    Last Post: 05-09-2016, 05:05 PM
  2. Replies: 3
    Last Post: 11-11-2013, 04:50 PM
  3. Export to Excel Error Handling
    By TimMoffy in forum Programming
    Replies: 1
    Last Post: 06-06-2012, 05:40 AM
  4. Error when trying to export from table to Excel
    By tobinjames in forum Import/Export Data
    Replies: 3
    Last Post: 12-15-2011, 02:55 PM
  5. Macro export to excel error
    By Andy_d in forum Import/Export Data
    Replies: 7
    Last Post: 04-15-2011, 09:54 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums