Howdy, Stranger!

It looks like you're new here. If you want to get involved, click one of these buttons!

Categories

how can I fix "Automation error", access97 file format to excel

ron_celesron_celes Member Posts: 1
Goodday to all of you. I built a program on vb6.0 which has a function of exporting all the contents of the access dbase to excel worksheets. I created this program on winXP. It uses ADO to read and export the contents of the database and I use Jet 4.0 as the driver. It all works fine when I am running this program on winXP but when I tried to run this program on win98, I got this error message "Automation error", Do you know how to fix this error and where this error comes from?? Please help me in resolving this problem. Below are lines of codes on my module, if you found some errors in these codes, please let me know. Any kind of help will be greatly appreciated. Thanks! God Bless!!

Public Function Export2XL(InitRow As Long, DBAccess As String, DBTable As String) As Long

Dim cn As New ADODB.Connection 'Use for the connection string
Dim cmd As New ADODB.Command 'Use for the command for the DB
Dim rs As New ADODB.Recordset 'Recordset return from the DB
Dim MyIndex As Integer 'Used for Index
Dim MyRecordCount As Long 'Store the number of record on the table
Dim MyFieldCount As Integer 'Store the number of fields or column
Dim ApExcel As Object 'To open Excel
Dim MyCol As String
Dim Response As Integer

Set ApExcel = CreateObject("Excel.application") 'Creates an object

ApExcel.Visible = True 'This enable you to see the process in Excel
ApExcel.Workbooks.Add 'Adds a new book.

'Set the connection string
cn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & DBAccess
'Open the connection
cn.Open

'Check that the connection is open
If cn.State = 0 Then cn.Open
Set cmd.ActiveConnection = cn
cmd.CommandText = DBTable
cmd.CommandType = adCmdTable
Set rs = cmd.Execute
'Count the number of fields or column
MyFieldCount = rs.Fields.Count

'Fill the first line with the name of the fields
For MyIndex = 0 To MyFieldCount - 1
ApExcel.Cells(InitRow, (MyIndex + 1)).Formula = rs.Fields(MyIndex).Name 'Write Title to a Cell
ApExcel.Cells(InitRow, (MyIndex + 1)).Font.Bold = True
ApExcel.Cells(InitRow, (MyIndex + 1)).Interior.ColorIndex = 36
ApExcel.Cells(InitRow, (MyIndex + 1)).WrapText = True
Next

'Draw border on the title line
MyCol = Chr((64 + MyIndex)) & InitRow
ApExcel.Range("A" & InitRow & ":" & MyCol).Borders.Color = RGB(0, 0, 0)
MyRecordCount = 1 + InitRow

'Fill the excel book with the values from the database
Do While rs.EOF = False
For MyIndex = 1 To MyFieldCount
ApExcel.Cells(MyRecordCount, MyIndex).Formula = rs((MyIndex - 1)).Value 'Write Value to a Cell
ApExcel.Cells(MyRecordCount, MyIndex).WrapText = False 'Format the Cell
Next
MyRecordCount = MyRecordCount + 1
rs.MoveNext
Loop

'Suggest to the user to save it's work
Response = MsgBox("Save the Excel Sheet and clik OK", vbOKOnly, "Save your file")

'Close the connection with the DB
rs.Close

'Return the last position in the workbook
Export2XL = MyRecordCount

End Function

Comments

  • jlegjleg Member Posts: 237
    : Goodday to all of you. I built a program on vb6.0 which has a function of exporting all the contents of the access dbase to excel worksheets. I created this program on winXP. It uses ADO to read and export the contents of the database and I use Jet 4.0 as the driver. It all works fine when I am running this program on winXP but when I tried to run this program on win98, I got this error message "Automation error", Do you know how to fix this error and where this error comes from?? Please help me in resolving this problem. Below are lines of codes on my module, if you found some errors in these codes, please let me know. Any kind of help will be greatly appreciated. Thanks! God Bless!!
    :
    : Public Function Export2XL(InitRow As Long, DBAccess As String, DBTable As String) As Long
    :
    Make sure that you include a version of MDAC with your install or install the Jet 4 drivers on the Win98 machines. You will also have to have DCOM98 available if you make an install package.

    Hope this helps

    John ;-)
    : Dim cn As New ADODB.Connection 'Use for the connection string
    : Dim cmd As New ADODB.Command 'Use for the command for the DB
    : Dim rs As New ADODB.Recordset 'Recordset return from the DB
    : Dim MyIndex As Integer 'Used for Index
    : Dim MyRecordCount As Long 'Store the number of record on the table
    : Dim MyFieldCount As Integer 'Store the number of fields or column
    : Dim ApExcel As Object 'To open Excel
    : Dim MyCol As String
    : Dim Response As Integer
    :
    : Set ApExcel = CreateObject("Excel.application") 'Creates an object
    :
    : ApExcel.Visible = True 'This enable you to see the process in Excel
    : ApExcel.Workbooks.Add 'Adds a new book.
    :
    : 'Set the connection string
    : cn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & DBAccess
    : 'Open the connection
    : cn.Open
    :
    : 'Check that the connection is open
    : If cn.State = 0 Then cn.Open
    : Set cmd.ActiveConnection = cn
    : cmd.CommandText = DBTable
    : cmd.CommandType = adCmdTable
    : Set rs = cmd.Execute
    : 'Count the number of fields or column
    : MyFieldCount = rs.Fields.Count
    :
    : 'Fill the first line with the name of the fields
    : For MyIndex = 0 To MyFieldCount - 1
    : ApExcel.Cells(InitRow, (MyIndex + 1)).Formula = rs.Fields(MyIndex).Name 'Write Title to a Cell
    : ApExcel.Cells(InitRow, (MyIndex + 1)).Font.Bold = True
    : ApExcel.Cells(InitRow, (MyIndex + 1)).Interior.ColorIndex = 36
    : ApExcel.Cells(InitRow, (MyIndex + 1)).WrapText = True
    : Next
    :
    : 'Draw border on the title line
    : MyCol = Chr((64 + MyIndex)) & InitRow
    : ApExcel.Range("A" & InitRow & ":" & MyCol).Borders.Color = RGB(0, 0, 0)
    : MyRecordCount = 1 + InitRow
    :
    : 'Fill the excel book with the values from the database
    : Do While rs.EOF = False
    : For MyIndex = 1 To MyFieldCount
    : ApExcel.Cells(MyRecordCount, MyIndex).Formula = rs((MyIndex - 1)).Value 'Write Value to a Cell
    : ApExcel.Cells(MyRecordCount, MyIndex).WrapText = False 'Format the Cell
    : Next
    : MyRecordCount = MyRecordCount + 1
    : rs.MoveNext
    : Loop
    :
    : 'Suggest to the user to save it's work
    : Response = MsgBox("Save the Excel Sheet and clik OK", vbOKOnly, "Save your file")
    :
    : 'Close the connection with the DB
    : rs.Close
    :
    : 'Return the last position in the workbook
    : Export2XL = MyRecordCount
    :
    : End Function
    :
    :

Sign In or Register to comment.