[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Transferring Data from Access to Excel in VBA...Receiving error 430 - class does not support automation

Posted on 2008-11-04
2
Medium Priority
?
347 Views
Last Modified: 2013-11-27
Hi, one of my user's is getting an error - my program copies data from a recordset in Access to Excel using BA code and I receive an error #430 class does not support automation.  I don't have this problem and no other users have reported this issue - I think it may just be this individuals pc.  Any thoughts?

See code snippet attached

Thanks,
Marilyn
Private Sub cmdApproval_Click()
Dim xlApp As Excel.Application
Dim xlwb As Excel.Workbook
Dim xlws As Excel.Worksheet
Dim xlrng As Excel.Range
Dim DB As DAO.Database
Dim strSQL As String
Dim strRecipient As String
Dim strBodyText As String
Dim rstQuote As DAO.Recordset
Dim vaTmp() As String
Dim vaNew As Variant
Dim strExportFile As String
Dim strVersion As String
 
On Error GoTo cmdApproval_Err
DoCmd.Save
Set DB = CurrentDb()
DoCmd.SetWarnings False
 
Me.Contract_Admin.SetFocus
Me.tabOpportunity.SetFocus
 
'MsgBox ("This quote is to be emailed to only your Manager for approval purposes.")
Set xlApp = New Excel.Application
Set xlwb = xlApp.Workbooks.Open("C:\Quote Templates\Application Files\Quote Template Export.xls")
'xlApp.Visible = "True"
 
'Delete out all data on Equipment Worksheet in template and copy in new data
strSQL = "Select * from tbl_Quote_Equipment where QuoteNo = '" & Me.txtQuoteNo & "';"
Set rstQuote = DB.OpenRecordset(strSQL, dbOpenDynaset)
   
Set xlws = xlwb.Sheets("Equipment")
xlws.Rows("2:65536").ClearContents
Set xlrng = xlws.Range("A2")
xlrng.CopyFromRecordset rstQuote

Open in new window

0
Comment
Question by:Marilync1266
  • 2
2 Comments
 
LVL 93

Accepted Solution

by:
Patrick Matthews earned 2000 total points
ID: 22882013
Hello Marilyn,

That code looks OK to me.

Have your user check references in the VB Editor, and see if any are listed as missing.

Regards,

Patrick
0
 
LVL 93

Expert Comment

by:Patrick Matthews
ID: 22882032
You could also have this person remove the reference to Excel, and modify the code to:



Private Sub cmdApproval_Click()
Dim xlApp As Object 'Excel.Application
Dim xlwb As Object 'Excel.Workbook
Dim xlws As Object 'Excel.Worksheet
Dim xlrng As Object 'Excel.Range
Dim DB As DAO.Database
Dim strSQL As String
Dim strRecipient As String
Dim strBodyText As String
Dim rstQuote As DAO.Recordset
Dim vaTmp() As String
Dim vaNew As Variant
Dim strExportFile As String
Dim strVersion As String
 
On Error GoTo cmdApproval_Err
DoCmd.Save
Set DB = CurrentDb()
DoCmd.SetWarnings False
 
Me.Contract_Admin.SetFocus
Me.tabOpportunity.SetFocus
 
'MsgBox ("This quote is to be emailed to only your Manager for approval purposes.")
Set xlApp = CreateObject("Excel.Application")
Set xlwb = xlApp.Workbooks.Open("C:\Quote Templates\Application Files\Quote Template Export.xls")
'xlApp.Visible = "True"
 
'Delete out all data on Equipment Worksheet in template and copy in new data
strSQL = "Select * from tbl_Quote_Equipment where QuoteNo = '" & Me.txtQuoteNo & "';"
Set rstQuote = DB.OpenRecordset(strSQL, dbOpenDynaset)
   
Set xlws = xlwb.Sheets("Equipment")
xlws.Rows("2:65536").ClearContents
Set xlrng = xlws.Range("A2")
xlrng.CopyFromRecordset rstQuote
0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

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

This article describes a serious pitfall that can happen when deleting shapes using VBA.
After seeing numerous questions for Dynamic Data Validation I notice that most have used Visual Basic to solve the problem. This suggestion is purely formula based and can be used in multiple rows.
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…
Look below the covers at a subform control , and the form that is inside it. Explore properties and see how easy it is to aggregate, get statistics, and synchronize results for your data. A Microsoft Access subform is used to show relevant calcul…

834 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