UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Update Data In Existing Excel File..creating Error 430, Access 2016    
 
   
TheDuke
post May 6 2019, 02:31 AM
Post#1



Posts: 3
Joined: 15-December 18



Hello,
I'm using W10 and access 2016 and now I'm getting the fabulous message "Run-time error '430': Class does not support....
It was running well on another computer with the same OS and access 2016.

this is my code:

Private Sub XL_Update_TBP_SEW_C_Drive_Click()

' Update TBP PH1 PH2 SEW XL C Drive

Dim xl As Excel.Application

' SEW Part
DoCmd.SetWarnings False
DoCmd.Hourglass True

Dim vbSEW_TBP_CALC As Workbook
Dim qdfBase As QueryDef
Dim rsBase As DAO.Recordset


DoCmd.Hourglass True


'SEW_PH1

' Set up reference to the query to export
Set qdfBase = CurrentDb.QueryDefs("Q_SEW_TBP_PH1")

' Debug.Print qdfBase.SQL

' set up the parameter
' Execute the query
Set rsBase = qdfBase.OpenRecordset()

' programmatically reference Excel
Set xl = CreateObject("Excel.application")

' Set reference to the export workbook
Set vbSEW_TBP_CALC = xl.Workbooks.Open("c:\Didier\AAPO_BI_ANALYSIS\SEW\SEW_TBP_CALC.xlsx")

' clear excel sheet
vbSEW_TBP_CALC.Worksheets("PH1").Cells.Range("A2:Z65000").ClearContents


' Use paste from recordset to put in excel sheet
vbSEW_TBP_CALC.Worksheets("PH1").Cells(2, 1).CopyFromRecordset rsBase



' save workbook
vbSEW_TBP_CALC.Save
vbSEW_TBP_CALC.Close

Set vbSEW_AAPO_TBP_CALC = Nothing
Set xl = Nothing

Set qdfBase = Nothing
DoCmd.Hourglass False


'SEW_PH2

' Set up reference to the query to export
Set qdfBase = CurrentDb.QueryDefs("Q_SEW_TBP_PH2")

' Debug.Print qdfBase.SQL

' set up the parameter
' Execute the query
Set rsBase = qdfBase.OpenRecordset()

' programmatically reference Excel
Set xl = CreateObject("Excel.application")

' Set reference to the export workbook
Set vbSEW_TBP_CALC = xl.Workbooks.Open("c:\Didier\AAPO_BI_ANALYSIS\SEW\SEW_TBP_CALC.xlsx")

' clear excel sheet
vbSEW_TBP_CALC.Worksheets("PH2").Cells.Range("A2:Z65000").ClearContents

' Use paste from recordset to put in excel sheet
vbSEW_TBP_CALC.Worksheets("PH2").Cells(2, 1).CopyFromRecordset rsBase

' save workbook
vbSEW_TBP_CALC.Save
vbSEW_TBP_CALC.Close

Set vbSEW_AAPO_TBP_CALC = Nothing
Set xl = Nothing

Set qdfBase = Nothing
DoCmd.Hourglass False

DoCmd.Close
DoCmd.OpenForm "F_Tables_Management"

End Sub


If somebody could help me on this...thanks already

theduke3006@gmail.com

Go to the top of the page
 
cheekybuddha
post May 6 2019, 03:02 AM
Post#2


UtterAccess VIP
Posts: 11,463
Joined: 6-December 03
From: Telegraph Hill


welcome2UA.gif

At which line does the error occur?

The main thing that jumps out from your code (though probably unrelated to you issue) is that you turn off Warnings but don't appear to turn them back on again.

At a guess, I'll suspecting you have s missing excel reference

--------------------


Regards,

David Marten
Go to the top of the page
 
TheDuke
post May 6 2019, 03:12 AM
Post#3



Posts: 3
Joined: 15-December 18



Hello,

The error is on the line:

' Use paste from recordset to put in excel sheet
vbSEW_TBP_CALC.Worksheets("PH1").Cells(2, 1).CopyFromRecordset rsBase

concerning the references I've added a screenshot in attachment. smile.gif

Attached File(s)
Attached File  References.png ( 19.14K )Number of downloads: 0
 
Go to the top of the page
 
cheekybuddha
post May 6 2019, 04:19 AM
Post#4


UtterAccess VIP
Posts: 11,463
Joined: 6-December 03
From: Telegraph Hill


Hi,

First add Option Explicit to the top of every code module. Then, in the VBA editor menus, click Debug -> Compile.

This will highlight some basic errors like undeclared variables like vbSEW_AAPO_TBP_CALC. Correct all these errors/

You appear to have copy and pasted the same code to try and repeat a similar operation 2 times. Some bits do not need to be repeated. Eg. you have already created the instance of Excel once - no need to keep doing it on each pass.

Since you are not doing anything to the stored queries, your use of a QueryDef object here is superfluous.

Try your code like this:
CODE
Private Sub XL_Update_TBP_SEW_C_Drive_Click()

' Update TBP PH1 PH2 SEW XL C Drive

  Dim xl As Excel.Application
  Dim vbSEW_TBP_CALC As Workbook
  Dim db As DAO.Database
  Dim rsBase As DAO.Recordset

' SEW Part
  DoCmd.SetWarnings False
  DoCmd.Hourglass True

' programmatically reference Excel
  Set xl = CreateObject("Excel.Application")
' Set reference to the export workbook
  Set vbSEW_TBP_CALC = xl.Workbooks.Open("c:\Didier\AAPO_BI_ANALYSIS\SEW\SEW_TBP_CALC.xlsx")

' Set database object
  Set db = CurrentDb()

' SEW_PH1
' Set up reference to the query to export
  Set rsBase = db.OpenRecordset("Q_SEW_TBP_PH1")
' clear excel sheet
  vbSEW_TBP_CALC.Worksheets("PH1").Cells.Range("A2:Z65000").ClearContents
' Use paste from recordset to put in excel sheet
  vbSEW_TBP_CALC.Worksheets("PH1").Cells(2, 1).CopyFromRecordset rsBase
' save workbook
  vbSEW_TBP_CALC.Save
' close recordset
  rsBase.Close
' release object
  Set rsBase = Nothing

' SEW_PH2
' Set up reference to the query to export
  Set rsBase = db.OpenRecordset("Q_SEW_TBP_PH2")
' clear excel sheet
  vbSEW_TBP_CALC.Worksheets("PH2").Cells.Range("A2:Z65000").ClearContents
' Use paste from recordset to put in excel sheet
  vbSEW_TBP_CALC.Worksheets("PH2").Cells(2, 1).CopyFromRecordset rsBase
' save workbook
  vbSEW_TBP_CALC.Save
  vbSEW_TBP_CALC.Close
' Release workbook object
  Set vbSEW_TBP_CALC = Nothing
' Release Excel object
  Set xl = Nothing

' close recordset
  rsBase.Close
' release object
  Set rsBase = Nothing
' release database object
  Set db = Nothing

  DoCmd.Hourglass False
  DoCmd.SetWarnings True     ' <-- Very important! Turn your warning back on!!!

  DoCmd.OpenForm "F_Tables_Management"
  DoCmd.Close Me.Name

End Sub


hth,

d

--------------------


Regards,

David Marten
Go to the top of the page
 
TheDuke
post May 10 2019, 10:57 AM
Post#5



Posts: 3
Joined: 15-December 18



Hello David,

I've used your solution but it still give me the same error.

Thanks.

Didier
Go to the top of the page
 


Custom Search


RSSSearch   Top   Lo-Fi    21st August 2019 - 03:51 PM