Advertisement

06.08.2008 at 02:07PM PDT, ID: 23467622
[x]
Attachment Details
[x]
The Solution Rating System

With so many solutions, how can you tell which solutions are most likely to help you and which ones are not? To provide you with a tool to use, we rate our solutions based on various elements that most accurately determine if a solution is a quality solution. To explain what factors affect the solution rating, here are the elements we take into consideration when formulating our solution rating.

  • The Grade of the Solution
  • The Zone Rank of the Expert Providing the Solution
  • The Number of Author and Expert Comments
  • The Number of Experts Contributing
  • The Feedback of the Community

Your Input Matters
Because of the way the system is set up, the most important variable in this equation is you. As a member of Experts Exchange, you are able to cast your vote on the quality of the solutions in regard to how complete, accurate, helpful and easy to understand each solution is. When you provide your feedback, each rating is adjusted accordingly. So, if you see a solution that has a poor rating that you think is a good solution, let us know by rating it. As you do, the rating will be adjusted and will become more accurate for other members of our site.

If you have any suggestions that you would like to make for our rating system, please ask a question in the Suggestions Zone of Community Support.

Thank you!
8.9

Accessing Spreadsheet Object Created From Form In VBA (Access 2003)

Asked by auracon in VB Objects, Visual Basic Programming, Access Coding/Macros

Tags: , , ,

Hello Everyone,

I created an Office Web Components 11.0 Spreadsheet ActiveX object on my form. I can make it export into an Excel file fine, but I can't access the worksheets in it from the VBA side. The spreadsheet was created from form, not within the VBA code.

objXLContractYears is the OWC 11 control created on the form.

The exporting works, but referring to the first worksheet in the object objXLContactYears generates an error saying that "Object doesn't support this property or method", with the line referring to the worksheets(1) highlighted.

All I need to know is what I am forgetting to do in order to access the OWC control that i put on the form. I am confused because when I make an OWC object from the VBA-side, I can access its worksheets fine, but I can't do the same with OWC object (objXLContractYears) that was created from the form directly. (Referring to the Form-created OWC Spreadsheet object's activesheet, activeworkbook, etc gives me an error)

Could anyone help? I think I have all necessary libraries referenced.
Visual Basic for Applications
Microsoft Access 11.0 Object Library
OLE Automation
Microsoft ActiveX Data Objects 2.1 Library
Microsoft DAO 3.6 Object Library
Microsoft Office 12.0 Object Library
Microsoft Office XP Web Components
Microsoft Office Web Components 11.0
Microsoft Windows Common Controls 6.0 (SP6)
Microsoft Excel 11.0 Object Library

Thanks guys!Start Free Trial 
1:
2:
3:
4:
5:
6:
7:
8:
9:
10:
11:
12:
13:
14:
15:
16:
17:
18:
19:
20:
21:
22:
23:
24:
25:
26:
27:
28:
29:
30:
31:
32:
33:
34:
35:
36:
37:
38:
39:
40:
41:
42:
43:
44:
45:
46:
47:
48:
49:
50:
51:
52:
53:
54:
55:
56:
57:
58:
59:
60:
61:
62:
63:
64:
65:
66:
67:
68:
69:
70:

Private Sub lblExportToExcel_Click()
'Exports the Contract Details table to an Excel file.
'Microsoft Excel x.x Object Library is required for this method.
'If errors occur, please check References package via Tools->References.
 
    Dim strExcelFile As Variant     'Excel file name specified by user via Save As dialouge
    Dim xlApp As Object             'Excel application instance
    Dim varTemp As Variant          'Dummy variable to dump data
    
    'See if an instance of Excel is already running.
    'If not, create an Excel application instance
    On Error Resume Next
    Set xlApp = GetObject(, "Excel.Application")
    If Err <> 0 Then
        Err.Clear
        Set xlApp = CreateObject("Excel.Application")
    End If
    
    On Error GoTo ErrorHandler
    
RetrySave:
    'Asks the user to select the file name and location.
    strExcelFile = xlApp.GetSaveAsFilename("Export.xls", _
                                            fileFilter:="Microsoft Excel Workbook (*.xls), *.xls")
    
    'Check to see if an incorrect filename was specified by user, and proceed only when no error is found.
    If strExcelFile <> False Then
        'User enters a correct filename.
        If Dir(strExcelFile) <> "" Then     'File already exists
            'Overwrite?
            If MsgBox("Another file with the same name already exists in the seleted location. Overwrite?", _
                vbQuestion + vbYesNo, "File Already Exists") = vbNo Then
                GoTo RetrySave
            End If
        End If
 
        'Export into an XLS file. (This is working)
        varTemp = Me.objXLContractYears.Export(strExcelFile, 0)
 
        'Test if I can access OWC spreadsheet created from the form. (no other specific purpose.. this is just to see whether i can access it or not)
        varTemp = Me.objXLContractYears.Worksheets(1)
 
        If MsgBox("Contract details have been exported into an Excel file successfully." & vbNewLine & vbNewLine _
            & "Saved as: " & vbNewLine & strExcelFile & vbNewLine & vbNewLine & _
            "Would you like to open this file in Excel?", vbYesNo + vbInformation, "Excel Export Successful") _
            = vbYes Then
            xlApp.Visible = True
            xlApp.Workbooks.Open FileName:=strExcelFile
        End If
    Else
        If MsgBox("An incorrect filename was entered, or File Save was cancelled." & vbNewLine & _
                    "Please try again, or click Cancel to return to the original window.", _
                    vbRetryCancel + vbCritical, "Excel Export Error") = vbRetry Then
            GoTo RetrySave
        Else
            End
        End If
    End If
    
    If xlApp.Visible = False Then
        xlApp.Quit
        Set xlApp = Nothing
    End If
    
    Exit Sub
 
ErrorHandler:
    MsgBox "An error has occured in this application. Please review the message below." & vbNewLine & vbNewLine & _
            "Error: [" & Err.Number & "] " & Err.Description, Buttons:=vbCritical, Title:="Error"
End Sub
[+][-]06.09.2008 at 11:03AM PDT, ID: 21745164

View this solution now by starting your 7-day free trial. Setting up your free trial is quick, easy, and secure. We will return you to this solution, unlocked, when you're done.

 

About this solution

Zones: VB Objects, Visual Basic Programming, Access Coding/Macros
Tags: Microsoft, Access, 2003, Visual Basic for Applications
Sign Up Now!
Solution Provided By: jmundsack
Participating Experts: 1
Solution Grade: A
 
 
 
Loading Advertisement...
20080716-EE-VQP-32 / EE_QW_2_20070628