Hi, I have gotten as far as exporting my Table into an Excel sheet and opening said sheet.
However ... Anything else I try after does not seem to work; I need some code to take the data in the Excel sheet and transpose it into a new sheet and delete the old sheet. Then if possible write some further code to change the layout and add extra rows etc ...
My current code looks like this:
However ... Anything else I try after does not seem to work; I need some code to take the data in the Excel sheet and transpose it into a new sheet and delete the old sheet. Then if possible write some further code to change the layout and add extra rows etc ...
My current code looks like this:
Code:
Option Compare Database
Sub exportToXl()
DoCmd.SetWarnings False
Dim dbTable As String
Dim xlWorksheetPath As String
' MsgBox CurrentDb.Updatable
xlWorksheetPath = MyDocsPath & "\Daily_Export.xlsx"
MsgBox xlWorksheetPath
dbTable = "Daily"
' Exports the data from the table into an "xlsx" file on the Desktop
DoCmd.TransferSpreadsheet transfertype:=acExport, spreadsheettype:=acSpreadsheetTypeExcel12Xml, tablename:=dbTable, FileName:=xlWorksheetPath, hasfieldnames:=True
' Opens the Excel Workbook
Dim xlApp As Object
Set xlApp = CreateObject("Excel.Application")
xlApp.Visible = True
xlApp.Workbooks.Open xlWorksheetPath
Set xlApp = Nothing
End Sub
' Identify's the users profile name to allow for export to Desktop
Public Function MyDocsPath() As String
MyDocsPath = Environ$("USERPROFILE") & "\Desktop"
End Function