Exporting Error between Access -> Excel

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I am using the CopyFromRecordSet method in Excel to take the data from my
Access database into Excel. The module is in Access.

As i was testing and debugging, the CopyfromRecordset seemed to be okay, but
now for some reason, I am getting run-time error 430 error: Class does not
support Automation or does not support expected interface.

I know it should be working as Access is opening up a new Excel workbook and
dropping my field headers on row 1. it pops this error when it gets to:

xlrng.Offset(1, 0).CopyFromRecordset rs

See below for code:

Private Sub sqlcreator()
Dim r As String
Dim col As Integer
Dim sqlstring As String
Dim rs As DAO.Recordset
Dim db As DAO.Database
Dim xlwkb As Excel.Workbook
Dim xlwks As Excel.Worksheet
Dim xlrng As Excel.Range
Dim xl As Excel.Application


r = InputBox("What billing period (yyyymmdd)", "Billing Period")

If r <> "" Then
sqlstring = "SELECT A.organization AS Org, A.billing_date AS
Billing_Date, A.billing_number, A.billing_customer_account_number,
A.description, round(Sum([pre_tax_amount]+[pst]),2) AS Subtotal,
IIf(InStr(1,A.[Description],'SERVICEFINDER')>0,'769004','761201') &
Right([ORGANIZATION],5) AS GL FROM ER_30987_" & r & " AS A"
sqlstring = sqlstring & " GROUP BY A.organization, A.Billing_date,
A.Billing_Number, A.billing_customer_account_Number, A.description,
IIf(InStr(1,[Description],'SERVICEFINDER')>0,'769004','761201') &
Right(A.[ORGANIZATION],5)"
sqlstring = sqlstring & " HAVING (Sum(nz([pre_tax_amount]) +
nz([pst]))<> 0 )"

sqlstring = sqlstring & " UNION SELECT B.organization,
B.billing_date, B.billing_number, B.billing_customer_account_number,
B.description, Round(Sum([pre_tax_amount]+[PST]),2) AS SubTotal,
IIf(InStr(1,[Description],'SERVICEFINDER')>0,'769004','761201') &
Right([ORGANIZATION],5) AS GL"
sqlstring = sqlstring & " FROM OCC_30987_" & r & " AS B GROUP BY
B.organization, B.billing_date, B.billing_number,
B.billing_customer_account_number, B.description,
IIf(InStr(1,[Description],'SERVICEFINDER')>0,'769004','761201') &
Right([ORGANIZATION],5)"
sqlstring = sqlstring & " HAVING ((B.description Not In
('PST','GST')) AND (Sum([pre_tax_amount]+[pst])<>0))"

sqlstring = sqlstring & " UNION SELECT C.organization,
C.billing_date, C.billing_number, C.billing_customer_account_number,
C.toll_plan_description, Round(Sum([pre_tax_amount]+[pst]),2) AS Subtotal,
'769004' & Right([ORGANIZATION],5) AS GL FROM Toll_30987_" & r & " AS C"
sqlstring = sqlstring & " GROUP BY C.organization, C.billing_date,
C.billing_number, C.billing_customer_account_number, C.toll_plan_description,
'769004' & Right([ORGANIZATION],5)"
sqlstring = sqlstring & " HAVING (Sum([pre_tax_amount]+[pst])<>0)"

sqlstring = sqlstring & " UNION SELECT D.organization,
D.billing_date, D.billing_number, D.billing_customer_account_number,
D.toll_plan_description, Round(Sum([pre_tax_amount]+[pst]),2) AS Subtotal,
'769004' & Right([ORGANIZATION],5) AS GL FROM Tollfree_30987_" & r & " AS D"
sqlstring = sqlstring & " GROUP BY D.organization, D.billing_date,
D.billing_number, D.billing_customer_account_number, D.toll_plan_description,
'769004' & Right([ORGANIZATION],5)"
sqlstring = sqlstring & " HAVING (Sum([pre_tax_amount]+[pst])<>0);"

Set db = CurrentDb

Set rs = db.OpenRecordset(sqlstring)

If rs.RecordCount <> 0 Then
Set xl = Excel.Application
Set xlwkb = xl.Workbooks.Add
Set xlwks = xlwkb.Worksheets(1)
xl.Visible = True

Set xlrng = xlwks.Range("A1")
For col = 0 To rs.Fields.Count - 1
xlrng.Offset(0, col).Value = rs.Fields(col).Name
Next

xlrng.Offset(1, 0).CopyFromRecordset rs

End If
Set rs = Nothing
rs.Close
db.Close
Set db = Nothing

End If

End Sub

I must be naming something wrong here. References to Excel 12.0 object
library have been selected. Can someone tell me why this class error would
be occuring.
 
Steven said:
I am using the CopyFromRecordSet method in Excel to take the data from my
Access database into Excel. The module is in Access.

As i was testing and debugging, the CopyfromRecordset seemed to be okay, but
now for some reason, I am getting run-time error 430 error: Class does not
support Automation or does not support expected interface.

I know it should be working as Access is opening up a new Excel workbook and
dropping my field headers on row 1. it pops this error when it gets to:

xlrng.Offset(1, 0).CopyFromRecordset rs

See below for code:

Private Sub sqlcreator()
Dim r As String
Dim col As Integer
Dim sqlstring As String
Dim rs As DAO.Recordset
Dim db As DAO.Database
Dim xlwkb As Excel.Workbook
Dim xlwks As Excel.Worksheet
Dim xlrng As Excel.Range
Dim xl As Excel.Application


r = InputBox("What billing period (yyyymmdd)", "Billing Period")

If r <> "" Then
sqlstring = "SELECT A.organization AS Org, A.billing_date AS
Billing_Date, A.billing_number, A.billing_customer_account_number,
A.description, round(Sum([pre_tax_amount]+[pst]),2) AS Subtotal,
IIf(InStr(1,A.[Description],'SERVICEFINDER')>0,'769004','761201') &
Right([ORGANIZATION],5) AS GL FROM ER_30987_" & r & " AS A"
sqlstring = sqlstring & " GROUP BY A.organization, A.Billing_date,
A.Billing_Number, A.billing_customer_account_Number, A.description,
IIf(InStr(1,[Description],'SERVICEFINDER')>0,'769004','761201') &
Right(A.[ORGANIZATION],5)"
sqlstring = sqlstring & " HAVING (Sum(nz([pre_tax_amount]) +
nz([pst]))<> 0 )"

sqlstring = sqlstring & " UNION SELECT B.organization,
B.billing_date, B.billing_number, B.billing_customer_account_number,
B.description, Round(Sum([pre_tax_amount]+[PST]),2) AS SubTotal,
IIf(InStr(1,[Description],'SERVICEFINDER')>0,'769004','761201') &
Right([ORGANIZATION],5) AS GL"
sqlstring = sqlstring & " FROM OCC_30987_" & r & " AS B GROUP BY
B.organization, B.billing_date, B.billing_number,
B.billing_customer_account_number, B.description,
IIf(InStr(1,[Description],'SERVICEFINDER')>0,'769004','761201') &
Right([ORGANIZATION],5)"
sqlstring = sqlstring & " HAVING ((B.description Not In
('PST','GST')) AND (Sum([pre_tax_amount]+[pst])<>0))"

sqlstring = sqlstring & " UNION SELECT C.organization,
C.billing_date, C.billing_number, C.billing_customer_account_number,
C.toll_plan_description, Round(Sum([pre_tax_amount]+[pst]),2) AS Subtotal,
'769004' & Right([ORGANIZATION],5) AS GL FROM Toll_30987_" & r & " AS C"
sqlstring = sqlstring & " GROUP BY C.organization, C.billing_date,
C.billing_number, C.billing_customer_account_number, C.toll_plan_description,
'769004' & Right([ORGANIZATION],5)"
sqlstring = sqlstring & " HAVING (Sum([pre_tax_amount]+[pst])<>0)"

sqlstring = sqlstring & " UNION SELECT D.organization,
D.billing_date, D.billing_number, D.billing_customer_account_number,
D.toll_plan_description, Round(Sum([pre_tax_amount]+[pst]),2) AS Subtotal,
'769004' & Right([ORGANIZATION],5) AS GL FROM Tollfree_30987_" & r & " AS D"
sqlstring = sqlstring & " GROUP BY D.organization, D.billing_date,
D.billing_number, D.billing_customer_account_number, D.toll_plan_description,
'769004' & Right([ORGANIZATION],5)"
sqlstring = sqlstring & " HAVING (Sum([pre_tax_amount]+[pst])<>0);"

Set db = CurrentDb

Set rs = db.OpenRecordset(sqlstring)

If rs.RecordCount <> 0 Then
Set xl = Excel.Application
Set xlwkb = xl.Workbooks.Add
Set xlwks = xlwkb.Worksheets(1)
xl.Visible = True

Set xlrng = xlwks.Range("A1")
For col = 0 To rs.Fields.Count - 1
xlrng.Offset(0, col).Value = rs.Fields(col).Name
Next

xlrng.Offset(1, 0).CopyFromRecordset rs

End If
Set rs = Nothing
rs.Close
db.Close
Set db = Nothing

End If

End Sub

I must be naming something wrong here. References to Excel 12.0 object
library have been selected. Can someone tell me why this class error would
be occuring.
Click to expand...

The only thing I can see without testing, is that there's something with
your instantiation of the Excel application.

try

Set xl = New Excel.Application

in stead of

Set xl = Excel.Application

It also seems you're not releasing the Excel objects, which you should
do, in the proper order, regardless of whether you close them or not.

Would you happen to have remaining instances of Excel in memory after
this (check Task Manager)?
 
Multiple versions of Access or Excel?


Steven Cheng said:
I am using the CopyFromRecordSet method in Excel to take the data from my
Access database into Excel. The module is in Access.

As i was testing and debugging, the CopyfromRecordset seemed to be okay, but
now for some reason, I am getting run-time error 430 error: Class does not
support Automation or does not support expected interface.

I know it should be working as Access is opening up a new Excel workbook and
dropping my field headers on row 1. it pops this error when it gets to:

xlrng.Offset(1, 0).CopyFromRecordset rs

See below for code:

Private Sub sqlcreator()
Dim r As String
Dim col As Integer
Dim sqlstring As String
Dim rs As DAO.Recordset
Dim db As DAO.Database
Dim xlwkb As Excel.Workbook
Dim xlwks As Excel.Worksheet
Dim xlrng As Excel.Range
Dim xl As Excel.Application


r = InputBox("What billing period (yyyymmdd)", "Billing Period")

If r <> "" Then
sqlstring = "SELECT A.organization AS Org, A.billing_date AS
Billing_Date, A.billing_number, A.billing_customer_account_number,
A.description, round(Sum([pre_tax_amount]+[pst]),2) AS Subtotal,
IIf(InStr(1,A.[Description],'SERVICEFINDER')>0,'769004','761201') &
Right([ORGANIZATION],5) AS GL FROM ER_30987_" & r & " AS A"
sqlstring = sqlstring & " GROUP BY A.organization, A.Billing_date,
A.Billing_Number, A.billing_customer_account_Number, A.description,
IIf(InStr(1,[Description],'SERVICEFINDER')>0,'769004','761201') &
Right(A.[ORGANIZATION],5)"
sqlstring = sqlstring & " HAVING (Sum(nz([pre_tax_amount]) +
nz([pst]))<> 0 )"

sqlstring = sqlstring & " UNION SELECT B.organization,
B.billing_date, B.billing_number, B.billing_customer_account_number,
B.description, Round(Sum([pre_tax_amount]+[PST]),2) AS SubTotal,
IIf(InStr(1,[Description],'SERVICEFINDER')>0,'769004','761201') &
Right([ORGANIZATION],5) AS GL"
sqlstring = sqlstring & " FROM OCC_30987_" & r & " AS B GROUP BY
B.organization, B.billing_date, B.billing_number,
B.billing_customer_account_number, B.description,
IIf(InStr(1,[Description],'SERVICEFINDER')>0,'769004','761201') &
Right([ORGANIZATION],5)"
sqlstring = sqlstring & " HAVING ((B.description Not In
('PST','GST')) AND (Sum([pre_tax_amount]+[pst])<>0))"

sqlstring = sqlstring & " UNION SELECT C.organization,
C.billing_date, C.billing_number, C.billing_customer_account_number,
C.toll_plan_description, Round(Sum([pre_tax_amount]+[pst]),2) AS Subtotal,
'769004' & Right([ORGANIZATION],5) AS GL FROM Toll_30987_" & r & " AS C"
sqlstring = sqlstring & " GROUP BY C.organization, C.billing_date,
C.billing_number, C.billing_customer_account_number, C.toll_plan_description,
'769004' & Right([ORGANIZATION],5)"
sqlstring = sqlstring & " HAVING (Sum([pre_tax_amount]+[pst])<>0)"

sqlstring = sqlstring & " UNION SELECT D.organization,
D.billing_date, D.billing_number, D.billing_customer_account_number,
D.toll_plan_description, Round(Sum([pre_tax_amount]+[pst]),2) AS Subtotal,
'769004' & Right([ORGANIZATION],5) AS GL FROM Tollfree_30987_" & r & " AS D"
sqlstring = sqlstring & " GROUP BY D.organization, D.billing_date,
D.billing_number, D.billing_customer_account_number, D.toll_plan_description,
'769004' & Right([ORGANIZATION],5)"
sqlstring = sqlstring & " HAVING
Click to expand...
(Sum([pre_tax_amount]+[pst]) said:
Set db = CurrentDb

Set rs = db.OpenRecordset(sqlstring)

If rs.RecordCount <> 0 Then
Set xl = Excel.Application
Set xlwkb = xl.Workbooks.Add
Set xlwks = xlwkb.Worksheets(1)
xl.Visible = True

Set xlrng = xlwks.Range("A1")
For col = 0 To rs.Fields.Count - 1
xlrng.Offset(0, col).Value = rs.Fields(col).Name
Next

xlrng.Offset(1, 0).CopyFromRecordset rs

End If
Set rs = Nothing
rs.Close
db.Close
Set db = Nothing

End If

End Sub

I must be naming something wrong here. References to Excel 12.0 object
library have been selected. Can someone tell me why this class error would
be occuring.
Click to expand...
 
Thanks for the input from both of you. yes, it was the multiple instances
that created some issues. i was going to close it off later, but I guess i
had to do it then.



Steven Cheng said:
I am using the CopyFromRecordSet method in Excel to take the data from my
Access database into Excel. The module is in Access.

As i was testing and debugging, the CopyfromRecordset seemed to be okay, but
now for some reason, I am getting run-time error 430 error: Class does not
support Automation or does not support expected interface.

I know it should be working as Access is opening up a new Excel workbook and
dropping my field headers on row 1. it pops this error when it gets to:

xlrng.Offset(1, 0).CopyFromRecordset rs

See below for code:

Private Sub sqlcreator()
Dim r As String
Dim col As Integer
Dim sqlstring As String
Dim rs As DAO.Recordset
Dim db As DAO.Database
Dim xlwkb As Excel.Workbook
Dim xlwks As Excel.Worksheet
Dim xlrng As Excel.Range
Dim xl As Excel.Application


r = InputBox("What billing period (yyyymmdd)", "Billing Period")

If r <> "" Then
sqlstring = "SELECT A.organization AS Org, A.billing_date AS
Billing_Date, A.billing_number, A.billing_customer_account_number,
A.description, round(Sum([pre_tax_amount]+[pst]),2) AS Subtotal,
IIf(InStr(1,A.[Description],'SERVICEFINDER')>0,'769004','761201') &
Right([ORGANIZATION],5) AS GL FROM ER_30987_" & r & " AS A"
sqlstring = sqlstring & " GROUP BY A.organization, A.Billing_date,
A.Billing_Number, A.billing_customer_account_Number, A.description,
IIf(InStr(1,[Description],'SERVICEFINDER')>0,'769004','761201') &
Right(A.[ORGANIZATION],5)"
sqlstring = sqlstring & " HAVING (Sum(nz([pre_tax_amount]) +
nz([pst]))<> 0 )"

sqlstring = sqlstring & " UNION SELECT B.organization,
B.billing_date, B.billing_number, B.billing_customer_account_number,
B.description, Round(Sum([pre_tax_amount]+[PST]),2) AS SubTotal,
IIf(InStr(1,[Description],'SERVICEFINDER')>0,'769004','761201') &
Right([ORGANIZATION],5) AS GL"
sqlstring = sqlstring & " FROM OCC_30987_" & r & " AS B GROUP BY
B.organization, B.billing_date, B.billing_number,
B.billing_customer_account_number, B.description,
IIf(InStr(1,[Description],'SERVICEFINDER')>0,'769004','761201') &
Right([ORGANIZATION],5)"
sqlstring = sqlstring & " HAVING ((B.description Not In
('PST','GST')) AND (Sum([pre_tax_amount]+[pst])<>0))"

sqlstring = sqlstring & " UNION SELECT C.organization,
C.billing_date, C.billing_number, C.billing_customer_account_number,
C.toll_plan_description, Round(Sum([pre_tax_amount]+[pst]),2) AS Subtotal,
'769004' & Right([ORGANIZATION],5) AS GL FROM Toll_30987_" & r & " AS C"
sqlstring = sqlstring & " GROUP BY C.organization, C.billing_date,
C.billing_number, C.billing_customer_account_number, C.toll_plan_description,
'769004' & Right([ORGANIZATION],5)"
sqlstring = sqlstring & " HAVING (Sum([pre_tax_amount]+[pst])<>0)"

sqlstring = sqlstring & " UNION SELECT D.organization,
D.billing_date, D.billing_number, D.billing_customer_account_number,
D.toll_plan_description, Round(Sum([pre_tax_amount]+[pst]),2) AS Subtotal,
'769004' & Right([ORGANIZATION],5) AS GL FROM Tollfree_30987_" & r & " AS D"
sqlstring = sqlstring & " GROUP BY D.organization, D.billing_date,
D.billing_number, D.billing_customer_account_number, D.toll_plan_description,
'769004' & Right([ORGANIZATION],5)"
sqlstring = sqlstring & " HAVING (Sum([pre_tax_amount]+[pst])<>0);"

Set db = CurrentDb

Set rs = db.OpenRecordset(sqlstring)

If rs.RecordCount <> 0 Then
Set xl = Excel.Application
Set xlwkb = xl.Workbooks.Add
Set xlwks = xlwkb.Worksheets(1)
xl.Visible = True

Set xlrng = xlwks.Range("A1")
For col = 0 To rs.Fields.Count - 1
xlrng.Offset(0, col).Value = rs.Fields(col).Name
Next

xlrng.Offset(1, 0).CopyFromRecordset rs

End If
Set rs = Nothing
rs.Close
db.Close
Set db = Nothing

End If

End Sub

I must be naming something wrong here. References to Excel 12.0 object
library have been selected. Can someone tell me why this class error would
be occuring.
Click to expand...
 
Back
Top