Wrox Programmer Forums
Go Back   Wrox Programmer Forums > Visual Basic > VB.NET 1.0 > VB.NET 2002/2003 Basics
|
VB.NET 2002/2003 Basics For coders who are new to Visual Basic, working in .NET versions 2002 or 2003 (1.0 and 1.1).
Welcome to the p2p.wrox.com Forums.

You are currently viewing the VB.NET 2002/2003 Basics section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old April 12th, 2007, 09:18 AM
Authorized User
 
Join Date: Dec 2006
Posts: 35
Thanks: 0
Thanked 0 Times in 0 Posts
Default Exporting form Access Database to Excel

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.

 
Old April 12th, 2007, 11:08 AM
Friend of Wrox
 
Join Date: Nov 2004
Posts: 1,621
Thanks: 1
Thanked 3 Times in 3 Posts
Default

Could you post the setup (Diming and initializing variables, etc.) and the error-producing statement?
 
Old April 12th, 2007, 11:47 AM
Authorized User
 
Join Date: Dec 2006
Posts: 35
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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


 
Old April 12th, 2007, 12:16 PM
Friend of Wrox
 
Join Date: Nov 2004
Posts: 1,621
Thanks: 1
Thanked 3 Times in 3 Posts
Default

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?
 
Old April 13th, 2007, 03:13 AM
Authorized User
 
Join Date: Dec 2006
Posts: 35
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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.

 
Old April 13th, 2007, 02:23 PM
Friend of Wrox
 
Join Date: Nov 2004
Posts: 1,621
Thanks: 1
Thanked 3 Times in 3 Posts
Default

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





Similar Threads
Thread Thread Starter Forum Replies Last Post
Exporting From Access To Excel jimnich Access VBA 17 February 8th, 2008 07:23 AM
Exporting Query Data From Access to Excel JimInSouthernCal Access VBA 3 December 21st, 2007 08:34 AM
Exporting to Existing Excel Worksheet from Access kfs Access VBA 2 August 3rd, 2006 10:56 AM
exporting a workbook from excel to access zisko3 Access 1 February 3rd, 2004 12:05 PM
Exporting access database to Excel .xls Squall Leonhart Classic ASP Databases 5 December 2nd, 2003 07:42 PM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.