Outputting Excel file from MS Access as a table

[daniel]

We have a MS Access database that is used by supervisors to input output figures from machines in the factory, My boss built this a few years ago and everything he built on it worked fine.


We are currently trying to create some reports based on the data inputted (Highest performing ay etc.) Copilot has been helping me with certain bits but it is struggling with getting the code correct to have output data outputted as a table in excel.
The code starts at the press of a button, the code then goes like this:


Option Compare Database




Private Sub Command22_Click()
Dim PauseTime, Start
PauseTime = 1# ' Set duration in seconds
Start = Timer ' Set start time.
Do While Timer < Start + PauseTime
DoEvents ' Yield to other processes.
Loop




DoCmd.OutputTo acOutputReport, "Hourly_Report_2021_Email_New", acFormatPDF, "\\IP address\Common Files drive\ACCESS FEEDBACK\Feedback 2021\shifthourlyreport.pdf", False
DoCmd.OutputTo acOutputQuery, "Hourly1_new", acFormatXLSX, Environ$("USERPROFILE") & "\My Company\Feedback Reports - Hourly Updates\Raw Data\shifthourlyreport.xlsx", False






Const cstrSMTPServer = "smtp.ionos.co.uk"




Const cintCDOSendUsingPort = 465




Const cintCDOSendUsing = 2




Dim objConfig, objMsg










Set objConfig = CreateObject("CDO.Configuration")












objConfig.Fields.Item("http://schemas.microsoft.com/cdo/configuration/sendusing") = cintCDOSendUsing




objConfig.Fields.Item("http://schemas.microsoft.com/cdo/configuration/smtpserverport") = cintCDOSendUsingPort




objConfig.Fields.Item("http://schemas.microsoft.com/cdo/configuration/smtpserver") = cstrSMTPServer




' ' Passing SMTP authentication
objConfig.Fields.Item("http://schemas.microsoft.com/cdo/configuration/smtpauthenticate") = 1
objConfig.Fields.Item("http://schemas.microsoft.com/cdo/configuration/smtpusessl") = True
objConfig.Fields.Item("http://schemas.microsoft.com/cdo/configuration/sendusername") = "email@address.co.uk"
objConfig.Fields.Item("http://schemas.microsoft.com/cdo/configuration/sendpassword") = "zd-/x9SvNQa+7ny"




objConfig.Fields.Update








strSubject = "Hourly Updates Report"


strBody = "Please find attached latest hourlys Report. <P> Thanks, <P>Feedback 2021</P>"




strFile = "I:\common file folder\Feedback 2021\shifthourlyreport.pdf"


Set objMsg = CreateObject("CDO.Message")




Set objMsg.Configuration = objConfig














With objMsg




.From = "email@address.co.uk"




.To = "email@address.com"




.Subject = strSubject




.HTMLBody = strBody




.AddAttachment strFile




.Send




Kill "\\IP Address\ Common Files drive\ACCESS FEEDBACK\Feedback 2021\shifthourlyreport.pdf"






End With




MsgBox "Report sent successfully. Click OK to continue"
End Sub




Private Sub Form_Open(Cancel As Integer)
DoCmd.Maximize
End Sub




As you can see, it creates a PDF based on the data to attach to an email, then kills the stored PDF once Email has been sent, it also creates an Excel XLSX file, we tried XLS and CSV but the data formats better using XLSX.
What do I need to add to get this data outputted as a table in excel, rather than just data?
Any help appreciated, thank you all.
(edit): for those asking, I meant it doesn't have a table name, I'm trying to use this file in a Power Automate flow but it can't find a table in the sheet because it outputs it to look like a table without table formatting.

[Minty]

Firstly, I would edit your post to remove the authentication information - that will get scraped by bots and sent all over the world.

to your question: You can't format anything in Excel using a simple

DoCmd.OutputTo acOutputQuery, "Hourly1_new", acFormatXLSX ...
 

You will have to investigate Excel automation, which can be a little bit of a steep learning curve.
That means opening a Excel object and then using VBA to manipulate the Excel worksheet.

There is a small sample code here:
https://www.access-programmers.co.uk.../#post-1965298

[CJ_London]

difficult to see as your code is not in code tags, please use the # button to preserve spacing and indentation

Also please clarify what you mean by

get this data outputted as a table in excel, rather than just data?

It will be exported as a table - so do you mean you want to give the data a table name in Excel?

If you mean the data as seen in the underlying query, consider using the transferSpreadsheet command

[Welshgasman]

Re the automation, create a table in Excel with the data using Macro recording.
That will show you the steps required.

Alternatively you could pull the data from Access within Excel.
I do that for my Diabetes DB, as Excel has better chart facilities.

That then appears to create it's own table?

[alansidman]

Another option is to import the data in Access using Power Query. You can then manipulate the data in PQ and export to Native Excel. No coding required as it can all be done in the UI.