Nokia C3: Access to Excel spreadsheet, sheet name?

Access to Excel spreadsheet, sheet name?
Hello,
I have the following code (which i found), it basically reads a query from an Access database then exports it to an Excel

spreadsheet. The only issue i have is i can't seem to SPECIFY A SHEETNAME? I wanna place it in Sheet2. Any ideas thanks, how

can i incorporate a sheet reference into this code.
Thanks,
Jac

Sub DAOCopyFromRecordSet(DBFullName As String, tablename As String, fieldname As String, targetrange As Range)

Dim db As Database
Dim rs As Recordset
Dim intColIndex As Integer

Set targetrange = targetrange.Cells(1, 1)

Set db = OpenDatabase(DBFullName)
Set rs = db.OpenRecordset(tablename, dbOpenTable) ' all records

Set rs = db.OpenRecordset("SELECT * FROM " & tablename, dbReadOnly) ' filter records

For intColIndex = 0 To rs.Fields.Count - 1

targetrange.Offset(0, intColIndex).Value = rs.Fields(intColIndex).Name

Next

' write recordset
targetrange.Offset(1, 0).CopyFromRecordset rs
Set rs = Nothing
db.Close
Set db = Nothing

End Sub
Reply With Quote
#2
Old 14-01-2012
Jacques25 Jacques25 is offline
Member

Join Date: May 2009
Posts: 490
Re: Access to Excel spreadsheet, sheet name?
It is possible to define the sheet name while exporting access database to Microsoft Excel. I am just providing the code reference and hope that you can make changes in your script.

Have a look on this :

* Dim strExcelFile As String -- The Excel File
* Dim strWorksheet As String -- The Worksheet
* Dim strDB As String -- The Database Connection
* Dim strTable As String -- The TABLE
* Dim objDB As Database --- The Database

So the changes that can be done is :

* strExcelFile = "C:\My Documents\name-of-excel-file.xls"
* strWorksheet = "name-of-worksheet"
* strDB = "C:\My Documents\name-of-database.mdb"
* strTable = "the-table"

Reply With Quote
#3
Old 14-01-2012
Vandam Vandam is offline
Member

Join Date: May 2009
Posts: 475
Re: Access to Excel spreadsheet, sheet name?
There are certain more methods mentioned on the following link which can guide you for the process. Other than you can also use some readymade tool which can export the database directly in Excel without manipulating the codes. This is less time consuming.

Methods for transferring data to Excel from Visual Basic
Reply With Quote
#4
Old 14-01-2012
Steyn's Avatar
Steyn Steyn is offline
Member

Join Date: May 2009
Posts: 571
Re: Access to Excel spreadsheet, sheet name?
There is another code which I found on Microsoft forums and tried. It worked for me. All it does that it can help you to export the databse in a new sheet of said Excel file. So that you can later on rename the same as you want.

Code:

Dim AccessConn As New System.Data.OleDb.OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Documents and Settings\...\My Documents\My Database\Access\database.accdb;")

AccessConn.Open()

'New sheet in Workbook
Dim AccessCommand As New System.Data.OleDb.OleDbCommand("SELECT * INTO [Excel 12.0 Xml;DATABASE=C:\Users\...\Documents\My Database\Excel\Excelfile.xlsx;HDR=Yes;].[Customers] from [Customers]", AccessConn)

AccessCommand.ExecuteNonQuery()