|
 |
access thread: automation error when using access to run excel
Message #1 by "Carol Mandra" <carol_mandra@r...> on Wed, 29 Aug 2001 21:45:29
|
|
I have some code which exports some data to the same excel spreadsheet 3
times, and creates three charts. This code works fine on my machine, but
not on another machine in my office. I have Access 97 SR2. The first time
access was crashing, so I had it reinstalled on the machine. Then I got
the error message 3170 - Can't find Installable ISAM and "Automation
Error - the server Threw an Exception". Then, Excel was reinstalled. Now
the error message is -2147417851 Method 'Open' of object Workbook failed.
I am not missing any references. I don't know if the problem is in the
Windows registry, or how to fix it if it is. Here is the code:
Function CreateChart(strSourceName As String, strFileName As String, num
As Integer, whereStr As String)
On Error GoTo Err_CreateChart
Dim xlApp As Excel.Application
Dim xlWrkbk As Excel.Workbook
Dim xlChartObj As Excel.Chart
Dim xlSourceRange As Excel.Range
Dim xlColPoint As Excel.Point
Dim rowCount As Integer, colCount As Integer, i As Integer
Dim QuesNum_col As Integer
Dim rowRange As String
'Create an Excel workbook file based on the object specified in the
second argument.
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel97,
strSourceName, strFileName, False
' "qryExcel_OfficeQuesDate", "c:\temp\OffScores.xls"
Set xlApp = CreateObject("Excel.Application") ' Create a Microsoft
Excel object.
Set xlWrkbk = xlApp.Workbooks.Open(strFileName) 'Open the spreadsheet
to which you exported the data.
Set xlSourceRange = xlWrkbk.Worksheets(num).Range
("a1").CurrentRegion 'Determine the range size and store it.
Set xlChartObj = xlApp.Charts.Add ' Create a new chart.
colCount = xlSourceRange.Columns.count
rowCount = xlSourceRange.Rows.count
Dim alpha As Variant
alpha = Array
("A", "B", "C", "D", "E", "F", "G", "H", "I", "J", "K", "L", "M", "N", "O",
"P", "Q", "R", "S", "T", "U", "V", "W", "X", "Y", "Z")
'get the column number whose header is "QuesNum". This column will be
the x-axis labels range.
For i = 1 To colCount
If xlWrkbk.Worksheets(num).Range(alpha(i) & "1").Value = "QuesNum"
Then
QuesNum_col = i
Exit For
End If
Next i
With xlChartObj ' Format the chart.
.ChartType = xlLineMarkers ' Specify chart type
.SetSourceData Source:=xlSourceRange, PlotBy:=xlColumns 'Set the
range of the chart.
.Location WHERE:=xlLocationAsNewSheet ' Specify that the
chart is located on a new sheet.
.HasTitle = True ' Create and set the title; set title
font.
.ChartTitle.Characters.Text = GraphHeader(whereStr)
.ChartTitle.Font.Size = 16
.Axes(xlValue).HasTitle = True
.Axes(xlValue).AxisTitle.Text = "Quality Rate"
.Axes(xlValue).AxisTitle.Font.Size = 16
.Axes(xlValue).MinimumScale = 0
.Axes(xlValue).MaximumScale = 1
.Axes(xlValue).MajorUnit = 0.2
.Axes(xlValue).MinorUnit = 0.1
For i = 1 To .SeriesCollection.count
With xlChartObj.SeriesCollection(i)
If .Name = "QuesNum" Then
.Delete
Exit For
End If
End With
Next i
rowRange = "R2C" & QuesNum_col & ":R" & rowCount & "C" &
QuesNum_col
.SeriesCollection(1).XValues = "=" & strSourceName & "!" & rowRange
' example: Charts("Chart1").SeriesCollection(1).XValues =
Worksheets("Sheet1").Range("r2c1:r9c1")
.Axes(xlCategory).HasTitle = True
.Axes(xlCategory).AxisTitle.Text = "Section " & num & " Questions"
.Axes(xlCategory).AxisTitle.Font.Size = 16
.HasDataTable = True
.HasLegend = True
.Legend.Position = xlLegendPositionTop
End With
xlApp.ActiveWindow.Activate
' With xlApp.ActiveWindow
' .Width = 660
' .Height = 400
' .Top = 1
' .left = 19.75
' .Zoom = 75
' .TabRatio = 78
'End With
' xlWrkbk.Worksheets(num).Range("A2:A" & rowCount).Select
' xlWrkbk.Worksheets(num).Names.Add Name:="QNumRange",
RefersToR1C1:="=" & strSourceName & "!R2C1:R" & rowCount & "C1"
'Save and close the workbook and quit Microsoft Excel.
xlWrkbk.Save
xlWrkbk.Close
xlApp.Quit
Exit_CreateChart:
Set xlSourceRange = Nothing
Set xlColPoint = Nothing
Set xlChartObj = Nothing
Set xlWrkbk = Nothing
Set xlApp = Nothing
Exit Function
Err_CreateChart:
MsgBox CStr(Err) & " " & Err.Description
Resume Exit_CreateChart
End Function
Note: on my computer this code snippet works, but not on the other machine:
xlApp.ActiveWindow.Activate
With xlApp.ActiveWindow
.Width = 660
.Height = 400
.Top = 1
.left = 19.75
.Zoom = 75
'.TabRatio = 78
End With
Why would My acces98 be ok with this code, but another maching with
access97 doesn't work? Thank-you!! Carol
Message #2 by "Pardee, Roy E" <roy.e.pardee@l...> on Wed, 29 Aug 2001 14:31:37 -0700
|
|
Start with this:
http://support.microsoft.com/support/kb/articles/Q155/6/66.asp
and see if that doesn't fix the problem.
HTH,
-Roy
-----Original Message-----
From: Carol Mandra [mailto:carol_mandra@r...]
Sent: Wednesday, August 29, 2001 2:44 PM
To: Access
Subject: [access] automation error when using access to run excel
I have some code which exports some data to the same excel spreadsheet 3
times, and creates three charts. This code works fine on my machine, but
not on another machine in my office. I have Access 97 SR2. The first time
access was crashing, so I had it reinstalled on the machine. Then I got
the error message 3170 - Can't find Installable ISAM and "Automation
Error - the server Threw an Exception". Then, Excel was reinstalled. Now
the error message is -2147417851 Method 'Open' of object Workbook failed.
I am not missing any references. I don't know if the problem is in the
Windows registry, or how to fix it if it is. Here is the code:
Function CreateChart(strSourceName As String, strFileName As String, num
As Integer, whereStr As String)
On Error GoTo Err_CreateChart
Dim xlApp As Excel.Application
Dim xlWrkbk As Excel.Workbook
Dim xlChartObj As Excel.Chart
Dim xlSourceRange As Excel.Range
Dim xlColPoint As Excel.Point
Dim rowCount As Integer, colCount As Integer, i As Integer
Dim QuesNum_col As Integer
Dim rowRange As String
'Create an Excel workbook file based on the object specified in the
second argument.
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel97,
strSourceName, strFileName, False
' "qryExcel_OfficeQuesDate", "c:\temp\OffScores.xls"
Set xlApp = CreateObject("Excel.Application") ' Create a Microsoft
Excel object.
Set xlWrkbk = xlApp.Workbooks.Open(strFileName) 'Open the spreadsheet
to which you exported the data.
Set xlSourceRange = xlWrkbk.Worksheets(num).Range
("a1").CurrentRegion 'Determine the range size and store it.
Set xlChartObj = xlApp.Charts.Add ' Create a new chart.
colCount = xlSourceRange.Columns.count
rowCount = xlSourceRange.Rows.count
Dim alpha As Variant
alpha = Array
("A", "B", "C", "D", "E", "F", "G", "H", "I", "J", "K", "L", "M", "N", "O",
"P", "Q", "R", "S", "T", "U", "V", "W", "X", "Y", "Z")
'get the column number whose header is "QuesNum". This column will be
the x-axis labels range.
For i = 1 To colCount
If xlWrkbk.Worksheets(num).Range(alpha(i) & "1").Value = "QuesNum"
Then
QuesNum_col = i
Exit For
End If
Next i
With xlChartObj ' Format the chart.
.ChartType = xlLineMarkers ' Specify chart type
.SetSourceData Source:=xlSourceRange, PlotBy:=xlColumns 'Set the
range of the chart.
.Location WHERE:=xlLocationAsNewSheet ' Specify that the
chart is located on a new sheet.
.HasTitle = True ' Create and set the title; set title
font.
.ChartTitle.Characters.Text = GraphHeader(whereStr)
.ChartTitle.Font.Size = 16
.Axes(xlValue).HasTitle = True
.Axes(xlValue).AxisTitle.Text = "Quality Rate"
.Axes(xlValue).AxisTitle.Font.Size = 16
.Axes(xlValue).MinimumScale = 0
.Axes(xlValue).MaximumScale = 1
.Axes(xlValue).MajorUnit = 0.2
.Axes(xlValue).MinorUnit = 0.1
For i = 1 To .SeriesCollection.count
With xlChartObj.SeriesCollection(i)
If .Name = "QuesNum" Then
.Delete
Exit For
End If
End With
Next i
rowRange = "R2C" & QuesNum_col & ":R" & rowCount & "C" &
QuesNum_col
.SeriesCollection(1).XValues = "=" & strSourceName & "!" & rowRange
' example: Charts("Chart1").SeriesCollection(1).XValues =
Worksheets("Sheet1").Range("r2c1:r9c1")
.Axes(xlCategory).HasTitle = True
.Axes(xlCategory).AxisTitle.Text = "Section " & num & " Questions"
.Axes(xlCategory).AxisTitle.Font.Size = 16
.HasDataTable = True
.HasLegend = True
.Legend.Position = xlLegendPositionTop
End With
xlApp.ActiveWindow.Activate
' With xlApp.ActiveWindow
' .Width = 660
' .Height = 400
' .Top = 1
' .left = 19.75
' .Zoom = 75
' .TabRatio = 78
'End With
' xlWrkbk.Worksheets(num).Range("A2:A" & rowCount).Select
' xlWrkbk.Worksheets(num).Names.Add Name:="QNumRange",
RefersToR1C1:="=" & strSourceName & "!R2C1:R" & rowCount & "C1"
'Save and close the workbook and quit Microsoft Excel.
xlWrkbk.Save
xlWrkbk.Close
xlApp.Quit
Exit_CreateChart:
Set xlSourceRange = Nothing
Set xlColPoint = Nothing
Set xlChartObj = Nothing
Set xlWrkbk = Nothing
Set xlApp = Nothing
Exit Function
Err_CreateChart:
MsgBox CStr(Err) & " " & Err.Description
Resume Exit_CreateChart
End Function
Note: on my computer this code snippet works, but not on the other machine:
xlApp.ActiveWindow.Activate
With xlApp.ActiveWindow
.Width = 660
.Height = 400
.Top = 1
.left = 19.75
.Zoom = 75
'.TabRatio = 78
End With
Why would My acces98 be ok with this code, but another maching with
access97 doesn't work? Thank-you!! Carol
Message #3 by "Carol Mandra" <carol_mandra@r...> on Thu, 30 Aug 2001 14:55:55
|
|
>
Roy, THANK YOU so much. I searched the microsoft support knowledge base
but didn't find the one article I needed, telling me to replace the one
dll I didn't replace! msexcl35.dll was the ISAM that was corrupt on the
other machine, and the reinstall did not replace the dlls, only adds them
if they don't already exist. I'm glad I didn't have to go into the
registry. Thanks again! Carol :-)
Start with this:
> > http://support.microsoft.com/support/kb/articles/Q155/6/66.asp
> > and see if that doesn't fix the problem.
> > HTH,
> -Roy
>
> -----Original Message-----
> From: Carol Mandra [mailto:carol_mandra@r...]
> Sent: Wednesday, August 29, 2001 2:44 PM
> Subject: [access] automation error when using access to run excel
|
|
|
View Cart
