Solved

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

Posted on 2014-09-13
3
585 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

Courses: Start Training Online With Pros, Today

Brush up on the basics or master the advanced techniques required to earn essential industry certifications, with Courses. Enroll in a course and start learning today. Training topics range from Android App Dev to the Xen Virtualization Platform.

Question has a verified solution.

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

I originally created this report in Crystal Reports 2008 where there is an option to underlay sections. I initially came across the problem in Access Reports where I was unable to run my border lines down through the entire page as I was using the P…
Phishing attempts can come in all forms, shapes and sizes. No matter how familiar you think you are with them, always remember to take extra precaution when opening an email with attachments or links.
Familiarize people with the process of utilizing SQL Server views from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Access…
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …

785 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