430 automation error

[nat1967]

Hi Everyone,

I dont know where to start. I have an A2K database that I have support for 2 years and something has gone wrong.

I export a report to Excel via automation. Everything has worked fine. Now, the code gets

Run Time 430: Automation failure

The only thing that has happened is my laptop had a hard drive failure and the pc got re-imaged. The previous version on my users desktop still works fine. The version I am working on as an update has quit working on mine.

There has been no changes to the code for the export. NONE! I have done extensive searching and reading. Seems like everything points to an MDAC problem. I went to microsoft's website and updated my MDAC file. Still it fails.

Does anyone have any suggestions?

Have A Great Day!!!,

Nathan
Senior Test Lead

 

[jgoodman00]

Can you compile the code for the database?

Can you manually export the data to excel?

James Goodman MCP

http://www.james-goodman.co.uk

 

[nat1967]

Yes, I have compiled the database. In fact, I decompiled and recompiled. I have exported the data "manually" 3 different ways.

File > Export
Toolbar > Analyze with Excel
Highlight query > Copy & Paste

All work fine.

Here is my code:

On Error Resume Next
Dim objXL As Excel.Application
Dim objWkb As Excel.Workbook
Dim objSht As Excel.Worksheet
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim intLastCol As Integer
Dim strSQL As String
Dim qParmDef As QueryDef

Const conMAX_ROWS = 200
Const conSHT_NAME = "ENCORE" 'excel sheet name
Const conWKB_NAME = "\\01gaf5142042011\BSC\Electronic Interfaces\RELEASE STATUS\releasestatus.xls" 'excel file location and name

strSQL = "SELECT Main.Feature, Sum(IIf(uat=-1,1,0)) AS TotalUAT, Sum(IIf(TestStatus=4,1,0)) AS Removed, Sum(IIf(executed=-1,1,0)) AS TotalExecuted, Sum(IIf(pass=-1,1,0)) AS TotalPass, Sum(IIf(fail=-1,1,0)) AS TotalFail, Sum(IIf(TestStatus=2,1,0)) AS Delayed, Sum(IIf(TestStatus=3,1,0)) AS Blocked, Sum(IIf(System='XML (LOTT)',1,0)) AS XML, Sum(IIf(System='EDI',1,0)) AS EDI, Sum(IIf(System='LENS',1,0)) AS LENS" & _
" FROM Main" & _
" WHERE (Main.Release ='" & Forms!frm_chooseexcel!cboRelease & "') And (Main.Group = 'ENCORE')" & _
" GROUP BY Main.Feature"

Set db = CurrentDb

Set objXL = GetObject(, "Excel.Application&quot
If Err.Number <> 0 Then
Set objXL = CreateObject(&quot;Excel.Application&quot
End If

Set rs = db.OpenRecordset(strSQL, dbOpenSnapshot) 'name of query or table to export

With objXL
.Visible = False
Set objWkb = .Workbooks.Open(conWKB_NAME)
On Error Resume Next
Set objSht = objWkb.Worksheets(conSHT_NAME)
If Not Err.Number = 0 Then
Set objSht = objWkb.Worksheets.Add
objSht.Name = conSHT_NAME
End If
Err.Clear
On Error GoTo 0
intLastCol = objSht.UsedRange.Columns.Count
With objSht
.Cells(2, 3) = Date
.Range(.Cells(11, 1), .Cells(rs.RecordCount, intLastCol)).Font.Bold = True '<---problem child
.Range(.Cells(11, 1), .Cells(conMAX_ROWS, _
intLastCol)).ClearContents '<---problem child
.Range(&quot;A11&quot.CopyFromRecordset rs 'location the paste to excel starts <---- 430 automation error
End With
End With

Set objSht = Nothing
Set objWkb = Nothing
Set objXL = Nothing
Set rs = Nothing
Set db = Nothing

Everything worked fine before. The code hasnt changed. I am at a complete loss. Any suggestions are greatly appreciated.

Have A Great Day!!!,

Nathan
Senior Test Lead

 

[jgoodman00]

And on what line does the code fail?

Looking at the code, the most obvious point for error looks like the path to the excel file. However, it is a UNC name, so it shouldnt be affected.

If you copy the UNC path into Start-Run, does it successfully open the path/file?

James Goodman MCP

http://www.james-goodman.co.uk

 

[nat1967]

hi James,

The code fails at:

.Range(.Cells(11, 1), .Cells(rs.RecordCount, intLastCol)).Font.Bold = True '<---problem child
.Range(.Cells(11, 1), .Cells(conMAX_ROWS, _
intLastCol)).ClearContents '<---problem child
.Range(&quot;A11&quot.CopyFromRecordset rs 'location the paste to excel starts <---- 430 automation error

The problem seems to be localized to my pc. I have just run it on another users computer and the code is fine. I just dont know what could be up with my own pc. I am thinking of stripping out Office 2000 and re-install. I dont know what else to do.

Have A Great Day!!!,

Nathan
Senior Test Lead

 

[jgoodman00]

Have you checked the service pack levels are the same for Office?



James Goodman MCP

http://www.james-goodman.co.uk

 

[vbajock]

IS your PC and the test PC all running the same version of Excel? I believe 97 supports DAO while 2000 supports ADO.

 

[nat1967]

Hi everyone,

triple checked the versions and service paks. everything running service pack 1 and office 2000.

i uninstalled office and re-installed. still dont work.

I am going to turn it over to sys admin. the database works well on every other computer. the only thing left is the windows 2000 image used on my laptop must have some problems. dont know what but maybe sys admin can figure it out.

Thanks for you ideas and sugestions.

Have A Great Day!!!,

Nathan
Senior Test Lead

 

[nat1967]

Followup:

sys admin removed and installed office 3 times. dont know why but now everything works.

Go figure.

Thanks again for everyone's help and suggestions.

Have A Great Day!!!,

Nathan
Senior Test Lead