Access to Excel spreadsheet, sheet name?

[jacDaRippa]

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

[Jacques25]

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"

[Vandam]

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

[Steyn]

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.

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()
    AccessConn.Close()