Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

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

Posted on 2014-09-13
3
Medium Priority
?
701 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 36

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

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Explore the ways to Unlock VBA Project Password Excel 2010 & 2013 documents. Go through the article and perform the steps carefully to remove VBA Excel .xls file.
A Case Study of using the Windows API to provide RS232 communications capability in Access without the use of Active-X controls.
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.
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…

577 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