Solved

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

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

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Question has a verified solution.

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

Whether you've completed a degree in computer sciences or you're a self-taught programmer, writing your first lines of code in the real world is always a challenge. Here are some of the most common pitfalls for new programmers.
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.
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.
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

808 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