Freelance Project Requests Software Development in C# WPF Asp.Net Core Vba Excel Word SQL-Server EF Linq, UWP Net


Datei 1: 2017-05-05 Export Access Table direct to

This demo gives a table directly in Access to Excel
Output large amounts of Access data to Excel using CopyFromRecordset? Task: • I want to output a large amount of data, about 3000 records directly to Excel. In Access you can do this via TransferSpeadsheet, but I would like to do it in the way, that I open an Excel document and copy into the cell A2 the complete data, similar to drag and drop by hand. 
Demo in Access


Public Function fg_Daten_als_Excel_ausgeben()
'--------------------< fg_Freigabe_Liste_ausgeben() >--------------------
'----< Excel bearbeiten >----
'< create >
Dim objExcel As Excel.Workbook
Set objExcel = Workbooks.Add
Dim objSheet As Excel.Worksheet
Set objSheet = objExcel.Worksheets.Add()
'</ create >

'< Daten holen >
Dim rec As Recordset
Set rec = CurrentDb.OpenRecordset("SELECT * FROM tbl_Artikel")
'</ Daten holen >

'--< ALLE Daten komplett einfuegen >--
objSheet.Range("A2").CopyFromRecordset rec
'--</ ALLE Daten komplett einfuegen >--
'--< speichern >--
Dim sPath As String
sPath = CurrentProject.Path
Dim sFilename_full As String
sFilename_full = sPath & "\Ausgabe_Access_Daten_in_Excel"
On Error Resume Next
objExcel.Close SaveChanges:=True, FileName:=sFilename_full
'--</ speichern >--

'</ Abschluss >
'*unbedingt schliessen...
Set objExcel = Nothing
'</ Abschluss >

'< oeffnen >
Shell "Excel.exe " & sFilename_full & ".xlsx"
'</ oeffnen >
'----</ Excel bearbeiten >----
'--------------------</ fg_Freigabe_Liste_ausgeben() >--------------------
End Function

Export to Excel

Under vba with Alt-F11 you can find the actual VBA code
Export the public function fg_Date_Excel

As usual in Access, the references to Access and Excel must be given
Under Tools-> References
Visual Basic for Applications
Microsoft Access xx Objects Library
Microsoft Excel xx Obejcts Library (for Export to Excel)
Microsoft Office xx Access Database engine Objects (for recordsets)
Excel 2010, 2013 ....
Open the references and locate the new Access Excel and Office references later in the list


Contact for Jobs, Project Requests: