help!!!!vb pgm to import data from access and copy to new excel sheet


DevX Home    Today's Headlines   Articles Archive   Tip Bank   Forums   

Results 1 to 6 of 6

Thread: help!!!!vb pgm to import data from access and copy to new excel sheet

  1. #1
    Join Date
    Aug 2009
    Posts
    2

    help!!!!vb pgm to import data from access and copy to new excel sheet

    hi give me codes of a vb pgm to import data from access database and copy it to new excel sheet.......

  2. #2
    Join Date
    Jun 2009
    Posts
    135
    Search the web for tutorials for both ends of your request as there are many out there and probably some on this site. Then when you run across a specific problem come back and post as I doubt someone will spend their time doing your work for you.



    Good Luck

  3. #3
    Join Date
    Aug 2009
    Posts
    2
    'EXPORTS TABLE IN ACCESS DATABASE TO EXCEL
    'REFERENCE TO DAO IS REQUIRED

    Dim strExcelFile As String
    Dim strWorksheet As String
    Dim strDB As String
    Dim strTable As String
    Dim objDB As DataSet



    'Change Based on your needs, or use
    'as parameters to the sub
    strExcelFile = "C:\My Documents\MySpreadSheet.xls"
    strWorksheet = "WorkSheet1"
    strDB = "C:\My Documents\company.mdb"
    strTable = "MyTable"

    objDB = OpenDatabase(strDB)

    'If excel file already exists, you can delete it here
    If Dir(strExcelFile) <> "" Then Kill(strExcelFile)

    objDB.Execute( _
    "SELECT * INTO [Excel 8.0;DATABASE=" & strExcelFile & _
    "].[" & strWorksheet & "] FROM " & "[" & strTable & "]")
    objDB.EndInit()
    objDB = Nothing








    I have tried this code but its giving me error on "Opendatabase" is not declared
    and "Error 2 'Execute' is not a member of 'System.Data.DataSet'.

    so how to solve it

  4. #4
    Join Date
    Jun 2009
    Posts
    135
    Well now we know that you are using VB.NET and this forum is for "classic" vb. See if you can PM a mod to have your thread moved.



    Good Luck

  5. #5
    Join Date
    Aug 2004
    Location
    Orange, California
    Posts
    1,262
    FYI: This code is for .Net, not VB Classic
    Code marked in Red needs to be changed to match your files, paths, and tables.
    Code:
    Imports System.Data.OleDb
    Public Class Form1
      Private connstring As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\AccessProjects\Tests\db1.mdb;"
      Private conn As OleDb.OleDbConnection
    
      Private Sub Form1_FormClosed(ByVal sender As Object, ByVal e As System.Windows.Forms.FormClosedEventArgs) Handles Me.FormClosed
        conn.Close()
        conn = Nothing
      End Sub
    
      Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        conn = New OleDbConnection(connstring)
        conn.Open()
      End Sub
    
      Private Sub btnExport_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnExport.Click
        Dim sSQL As String = String.Empty
        Dim sExcelFile As String = "C:\VB.NetProjects\AccessToExcel\xltest.xls"
        Dim sWorksheet As String = "tblTest"
        Dim sTable As String = "tblTest"
    
        'Delete old excel file if it exsists
        If IO.File.Exists(sExcelFile) Then
          IO.File.Delete(sExcelFile)
        End If
        sSQL = "SELECT * INTO [Excel 8.0;DATABASE=" & sExcelFile & "].[" & sWorksheet & "] FROM " & "[" & sTable & "]"
        Dim command As New OleDbCommand(sSQL, conn)
        command.ExecuteNonQuery()
      End Sub
    End Class

  6. #6
    Join Date
    Apr 2007
    Location
    Sterling Heights, Michigan
    Posts
    8,666
    Moved To .NET section

    (Thanks Ron!)
    I don't answer coding questions via PM or Email. Please post a thread in the appropriate forum section.
    Please use [Code]your code goes in here[/Code] tags when posting code.
    Before posting your question, did you look here?
    Got a question on Linux? Visit our Linux sister site.
    Modifications Required For VB6 Apps To Work On Vista

Similar Threads

  1. Replies: 60
    Last Post: 09-13-2002, 05:41 PM
  2. Access 97 to 2000 Conversion Error
    By Michelle in forum VB Classic
    Replies: 8
    Last Post: 04-19-2002, 05:01 PM
  3. Access & SQL Server
    By David Jones in forum Database
    Replies: 0
    Last Post: 08-31-2001, 12:22 PM
  4. Web data access and the ADO control
    By Tim A. in forum VB Classic
    Replies: 0
    Last Post: 02-23-2001, 04:57 PM
  5. Data Access Pages to Emulate Website on CD
    By John Simmons in forum Enterprise
    Replies: 0
    Last Post: 02-09-2001, 12:42 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
HTML5 Development Center
 
 
FAQ
Latest Articles
Java
.NET
XML
Database
Enterprise
Questions? Contact us.
C++
Web Development
Wireless
Latest Tips
Open Source


   Development Centers

   -- Android Development Center
   -- Cloud Development Project Center
   -- HTML5 Development Center
   -- Windows Mobile Development Center