get data from access and save it into a new excel file using vb 2005

get data from access and save it into a new excel file using vb 2005

• Ben-TSK

  
  ExcelSheetName is the name of the Worksheet in the Workbook (Book1.xls) that data will be imported to. The source of the data is AccessTableName (db1.mdb).

  If this is not working then you're going to need to provide some more details, such as the error you are encountering.

  
  
  

• Asim Zeeshan

  
  Below is a very simple example:

  Dim AccessConn As New System.Data.OleDb.OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;" & _

  "Data Source=c:\Test Files\db1 XP.mdb")

  AccessConn.Open()

  Dim AccessCommand As New System.Data.OleDb.OleDbCommand("SELECT * INTO [Excel 8.0;DATABASE=c:\Test Files\Book1.xls;HDR=NO;].[ExcelSheetName] from [AccessTableName]", AccessConn)

  AccessCommand.ExecuteNonQuery()

  AccessConn.Close()

  
  
  

• Ruud van der Werf

  
  Right. The last and latest version is 8.0 although it will support up to 11.0 version files.
  
  

• Matthew Langley

  My method is to automate it when i click a button, but upon clicking, i find that the new excel book does not appear in c:\temp.
  

• Jan Byvaly

  Ok nvm i finally got it the answer, i had to use excel 8 instead of 11. The code is workable

  
  

• tasleemarif

  Dim AccessCommand As New System.Data.OleDb.OleDbCommand("SELECT * INTO [Excel 8.0;DATABASE=c:\Test Files\Book1.xls;HDR=NO;].[ExcelSheetName] from [AccessTableName]", AccessConn)

  Sorry the code does not work,   is the {ExcelSheetName] from [AccessTableName]",  a table name in the data base

  This is the my modified version

  Dim AccessConn As New System.Data.OleDb.OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;" & _

  "Data Source=c:\Temp\db1.mdb")

  AccessConn.Open()

  Dim AccessCommand As New System.Data.OleDb.OleDbCommand("SELECT * INTO [Excel 11.0;DATABASE=c:\Temp\Book1.xls;HDR=NO;].[ExcelSheetName] from [AccessTableName]", AccessConn)

   

  
  

• Jeffyoung1234

  
  The code I posted will not create the Workbook file. It will create the Worksheet but the Workbook file is assumed to already exist.
  
  
• get data from access and save it into a new excel file using vb 2005