Exporting form Access Database to Excel

asters · April 12th, 2007, 09:18 AM

I get the error: "An unhandled exception has occurred in a component in your application. click continue and application will ignore this error and attempt to continue. Bad variable type"
This happens when i am exporting data from an access database to an excel on a clients Windows 2000 Machine but works perfect on my Windows XP.
This is a windows form application.

BrianWren · April 12th, 2007, 11:08 AM

Could you post the setup (Diming and initializing variables, etc.) and the error-producing statement?

asters · April 12th, 2007, 11:47 AM

This is the code:

Public Function Exporting() As String

        ' Create the Excel application.
        excel_app = CreateObject("Excel.Application")

        '' Uncomment this line to make Excel visible.
        'excel_app.Visible = True

        ' Open the Excel spreadsheet.
        excel_app.Workbooks.Open("C:\UPLOADER.XLS")

        ' Check for later versions.
        If Val(excel_app.Application.Version) >= 8 Then
            ' Uncomment the following code to select
            ' a particular worksheet.
            excel_app.Sheets(1).Select()
            excel_sheet = excel_app.ActiveSheet
        Else
            excel_sheet = excel_app
        End If

        ' Open the Access database.
        conn = New ADODB.Connection

        'Map to server
        conn.ConnectionString = ("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=d:\ Deduction\InsureDeduct.mdb;")
        conn.Open()

        ' Select the data.
        rs = conn.Execute( _
        "SELECT * FROM CUPLOADER ORDER BY Up_Date")

        ' Make the column headers.
        For col = 0 To rs.Fields.Count - 1
            excel_sheet.Cells(1, col + 1) = rs.Fields(col).Name
        Next col

        ' Get data from the database and insert
        ' it into the spreadsheet.
        row = 2
        Do While Not rs.EOF
            For col = 0 To rs.Fields.Count - 1
                excel_sheet.Cells(row, col + 1) = _
                rs.Fields(col).Value
            Next col

            row = row + 1
            rs.MoveNext()
        Loop

        ' Make the columns autofit the data.
        excel_sheet.Range( _
            excel_sheet.Cells(1, 1), _
            excel_sheet.Cells(1, _
                rs.Fields.Count)).Columns.AutoFit()

        ' Close the database.
        rs.Close()
        rs = Nothing
        conn.Close()
        conn = Nothing

        ' Make the header bold.
        excel_sheet.Rows(1).Font.Bold = True

        ' Freeze the header row so it doesn't scroll.
        excel_sheet.Rows(2).Select()
        excel_app.ActiveWindow.FreezePanes = True

        ' Select the first cell.
        excel_sheet.Cells(1, 1).Select()

        ' Comment the Close and Quit lines to keep
        ' Excel running so you can see it.

        ' Close the workbook saving changes.
        excel_app.ActiveWorkbook.Close(True)
        excel_app.Quit()

        excel_sheet = Nothing
        excel_app = Nothing

        MessageBox.Show("Copied " & Format$(row - 2) & " upload data file.", "Load File Creation", MessageBoxButtons.OK, MessageBoxIcon.Information)
    End Function

BrianWren · April 12th, 2007, 12:16 PM

If you precede your code with a pair of square brackets ([]) with âcodeâ in them, and end it with a pair of brackets having â/codeâ in them, your code postings will be rendered in fixed font. (These âtags/tokensâ will be added for you if you click the âInsert Codeâ button (has a pound sign on it â#â).

Which line triggers the error?

asters · April 13th, 2007, 03:13 AM

The development is on my Windows XP Machine and there are no errors generated but when deployed on a Windows 2000 machine then the error is generated.

I just can't locate which line triggers the error.

BrianWren · April 13th, 2007, 02:23 PM

You can zero in on it this way (add the bold blue parts):

Code:

Public Function Exporting() As String

    On Error GoTo Er

    Dim Loc As Integer

    ' Create the Excel application.
    excel_app = CreateObject("Excel.Application")

    '' Uncomment this line to make Excel visible.
    'excel_app.Visible = True

    ' Open the Excel spreadsheet.
    Loc = 10
    excel_app.Workbooks.Open("C:\UPLOADER.XLS")

    ' Check for later versions.
    Loc = 20
    If Val(excel_app.Application.Version) >= 8 Then
        ' Uncomment the following code to select
        ' a particular worksheet.
        excel_app.Sheets(1).Select()
        Loc = 25
        excel_sheet = excel_app.ActiveSheet
    Else
        Loc = 26
        excel_sheet = excel_app
    End If

    ' Open the Access database.
    conn = New ADODB.Connection

    'Map to server
    Loc = 30
    conn.ConnectionString = ("Provider=Microsoft.Jet.OLEDB.4.0;" & _
                             "Data Source=d:\ Deduction\InsureDeduct.mdb;")
    conn.Open()

    ' Select the data.
    Loc = 40
    rs = conn.Execute("SELECT *         " & _
                      "FROM   CUPLOADER " & _
                      "ORDER BY Up_Date ")

    ' Make the column headers.
    Loc = 50
    For col = 0 To rs.Fields.Count - 1
        excel_sheet.Cells(1, col + 1) = rs.Fields(col).Name
    Next col

    ' Get data from the database and insert it into the spreadsheet.
    row = 2
    Loc = 60
    Do While Not rs.EOF
        For col = 0 To rs.Fields.Count - 1
            excel_sheet.Cells(row, col + 1) = _
            rs.Fields(col).Value
        Next col

        row = row + 1
        rs.MoveNext()
    Loop

    ' Make the columns autofit the data.
    excel_sheet.Range(excel_sheet.Cells(1, 1), _
                      excel_sheet.Cells(1, rs.Fields.Count) _
                     ).Columns.AutoFit()

    ' Close the database.
    Loc = 70
    rs.Close()
    rs = Nothing
    conn.Close()
    conn = Nothing

    ' Make the header bold.
    Loc = 80
    excel_sheet.Rows(1).Font.Bold = True

    ' Freeze the header row so it doesn't scroll.
    Loc = 90
    excel_sheet.Rows(2).Select()
    excel_app.ActiveWindow.FreezePanes = True

    ' Select the first cell.
    Loc = 100
    excel_sheet.Cells(1, 1).Select()

    ' Comment the Close and Quit lines to keep
    ' Excel running so you can see it.

    ' Close the workbook saving changes.
    excel_app.ActiveWorkbook.Close(True)
    excel_app.Quit()

    excel_sheet = Nothing
    excel_app = Nothing

Rs: MessageBox.Show("Copied " & Format$(row - 2) & " upload data file.", _
                    "Load File Creation", _
                    MessageBoxButtons.OK, MessageBoxIcon.Information)
    Exit Function

Er: MessageBox.Show("Error " & Err.Number & ", " & _
                    """" & err.Description & """.  " & _
                    "Loc = " & Loc, _
                    "Load File Creation", _
                    MessageBoxButtons.OK, MessageBoxIcon.Information)

    Resume Rs

End Function