Solved

Late binding add a worksheet and upload recordset from ACCESS 2010 to Excel 2010

Posted on 2014-09-13
3
577 Views
Last Modified: 2014-09-13
Discovered not all the users of the database have references set to Excel library and need to use late binding.  But now I cannot get the code to recognizthe worksheet declaration.  

Set objExcel = CreateObject("Excel.Application")
Dim wks As objExcel.Worksheet  'DOES NOT WORK
  objExcel.Visible = True
Set wbk = objExcel.Workbooks.Add
'objExcel.Visible = True
objExcel.DisplayAlerts = True
Set wks = wbk.Sheets.Add
With wks
    .Name = "ProjectData"
    .Range("A1").Value = "WorkDate"
    .Range("B1").Value = "LastName"
    .Range("C1").Value = "FirstName"
    .Range("D1").Value = "ProjectName"
    .Range("E1").Value = "WorkTime"
    .Range("F1").Value = "PayRate"
    .Range("G1").Value = "PayTotal"
    .Range("H1").Value = "Notes"
End With

i = 2
With wks
0
Comment
Question by:ssmith94015
  • 2
3 Comments
 
LVL 33

Accepted Solution

by:
Norie earned 500 total points
ID: 40320984
objExcel.Worksheet wouldn't actually work if you were early binding, if that was the case you would just use Worksheet.

Since you are using late binding though you should change it, and any other Excel object declarations, to simply Object.
Dim wks As Object    'DOES NOT WORK

    Set objExcel = CreateObject("Excel.Application")
    objExcel.Visible = True
    objExcel.DisplayAlerts = True
    
    Set wbk = objExcel.Workbooks.Add
    Set wks = wbk.Sheets.Add
    
    With wks
        .Name = "ProjectData"
        .Range("A1").Value = "WorkDate"
        .Range("B1").Value = "LastName"
        .Range("C1").Value = "FirstName"
        .Range("D1").Value = "ProjectName"
        .Range("E1").Value = "WorkTime"
        .Range("F1").Value = "PayRate"
        .Range("G1").Value = "PayTotal"
        .Range("H1").Value = "Notes"
    End With

Open in new window

0
 

Author Comment

by:ssmith94015
ID: 40320991
Yes, let me try your suggestion.  One thing I missed was declaring the worksheet (wks) as an object.
0
 

Author Closing Comment

by:ssmith94015
ID: 40320993
Thank you for your help. this solved my problem.
0

Featured Post

U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Introduction When developing Access applications, often we need to know whether an object exists.  This article presents a quick and reliable routine to determine if an object exists without that object being opened. If you wanted to inspect/ite…
Entering time in Microsoft Access can be difficult. An input mask often bothers users more than helping them and won't catch all typing errors. This article shows how to create a textbox for 24-hour time input with full validation politely catching …
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…

920 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now