?
Solved

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

Posted on 2014-09-13
3
Medium Priority
?
648 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
3 Comments
 
LVL 34

Accepted Solution

by:
Norie earned 2000 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

Want to be a Web Developer? Get Certified Today!

Enroll in the Certified Web Development Professional course package to learn HTML, Javascript, and PHP. Build a solid foundation to work toward your dream job!

Question has a verified solution.

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

It’s the first day of March, the weather is starting to warm up and the excitement of the upcoming St. Patrick’s Day holiday can be felt throughout the world.
Access custom database properties are useful for storing miscellaneous bits of information in a format that persists through database closing and reopening.  This article shows how to create and use them.
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.
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…

777 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