Set Column Width in EMailDatabaseObject

[RogerCooper]

I have many queries that I run and e-mail as xlsx attachments. I notice that when received by the users, the columns widths vary and are usually too large.

Is there any way of controlling the column width in the created spreadsheet.

 

[JHB]

Maybe the link could point you in the right direction!
https://msdn.microsoft.com/VBA/Excel-VBA/articles/range-autofit-method-excel

 

[RogerCooper]

https://msdn.microsoft.com/VBA/Excel-VBA/articles/range-autofit-method-excel

Interesting, but I need to control it on the Access end. The user can resize the columns in many ways, but I want to remove the burden from the user.

 

[Minty]

Yes, you can use the column width property https://msdn.microsoft.com/VBA/Excel-VBA/articles/range-columnwidth-property-excel this will require you to use Excel Automation from within Access.

 

[RogerCooper]

As I understand it, Excel Automation allows me to use Excel objects in Access. But what I need to do is to control the way Access creates an Excel file. I don't see how automation would help me.

 

[Minty]

It's the only way you can apply formatting to your excel file. The work flow from Access would be something like;

• Export the query to the excel file to be emailed, saved to a known file location and name.
• Open the file using automation and apply the formatting you require.
• Save the now formatted file
• Email it as an attachment.

This will require a bit of work, and probably a learning curve of how to apply the formatting you need, but it is a widely used method.