I have several reports that I export from Access to Excel which look
terrible. I was wondering if there is a simple way to auto format the columns
(align) by code via the menu item? Example the manual way. Select complete
workbook, format, columns, autoformat. can this be done via VBA?

If you can do it manually, you can record the VBA code.

ToolsRecord New macro..make your changes. stop recording.
The generated code will probably not be the most efficient, but will show
you the requirements.

NickHK

"TKM" ...
I have several reports that I export from Access to Excel which look
terrible. I was wondering if there is a simple way to auto format the
columns
(align) by code via the menu item? Example the manual way. Select complete
workbook, format, columns, autoformat. can this be done via VBA?

Thank you it is a great start.. Do you know where I would put the code so
when I export an updated report from Access that this code runs and does what
I recorded?

Thanks again

"NickHK" wrote:

If you can do it manually, you can record the VBA code.

ToolsRecord New macro..make your changes. stop recording.
The generated code will probably not be the most efficient, but will show
you the requirements.

NickHK

"TKM" ...
I have several reports that I export from Access to Excel which look
terrible. I was wondering if there is a simple way to auto format the
columns
(align) by code via the menu item? Example the manual way. Select complete
workbook, format, columns, autoformat. can this be done via VBA?

If you use the Record Macro option described by NickHK, you will see an
option on the "Record Macro" dialog box titled "Store macro in:"
Select "Personal Macro Workbook" and this macro will always be
available to you when you use Excel.

If you create/edit your macro in VBA, you'll want add a module to the
"VBAProject (PERSONAL.XLS)" project. (By the way, if you record a
macro, it will automatically create a new module in this project for
you)

When you close Excel, if you've added/changed macros to your Personal
Macro Workbook, you will be prompted whether you want to save the
changes you made to the Personal Macro Workbook. Click the "Yes"
button, otherwise your macros will be lost.

Good Luck,
Glenn Ray



TKM wrote:
Thank you it is a great start.. Do you know where I would put the code so
when I export an updated report from Access that this code runs and does what
I recorded?

Thanks again

"NickHK" wrote:

If you can do it manually, you can record the VBA code.

ToolsRecord New macro..make your changes. stop recording.
The generated code will probably not be the most efficient, but will show
you the requirements.

NickHK

"TKM" ...
I have several reports that I export from Access to Excel which look
terrible. I was wondering if there is a simple way to auto format the
columns
(align) by code via the menu item? Example the manual way. Select complete
workbook, format, columns, autoformat. can this be done via VBA?

Any ideas on how to keep the alignment from the recorded macro when a user
runs an updated report. Seems the report get overwritten and I have to start
from scratch? Any ideas on this? I am using an Access report and using the
output and or transferspread sheet acion in Access. Thanks again

"Glenn" wrote:

If you use the Record Macro option described by NickHK, you will see an
option on the "Record Macro" dialog box titled "Store macro in:"
Select "Personal Macro Workbook" and this macro will always be
available to you when you use Excel.

If you create/edit your macro in VBA, you'll want add a module to the
"VBAProject (PERSONAL.XLS)" project. (By the way, if you record a
macro, it will automatically create a new module in this project for
you)

When you close Excel, if you've added/changed macros to your Personal
Macro Workbook, you will be prompted whether you want to save the
changes you made to the Personal Macro Workbook. Click the "Yes"
button, otherwise your macros will be lost.

Good Luck,
Glenn Ray



TKM wrote:
Thank you it is a great start.. Do you know where I would put the code so
when I export an updated report from Access that this code runs and does what
I recorded?

Thanks again

"NickHK" wrote:

If you can do it manually, you can record the VBA code.

ToolsRecord New macro..make your changes. stop recording.
The generated code will probably not be the most efficient, but will show
you the requirements.

NickHK

"TKM" ...
I have several reports that I export from Access to Excel which look
terrible. I was wondering if there is a simple way to auto format the
columns
(align) by code via the menu item? Example the manual way. Select complete
workbook, format, columns, autoformat. can this be done via VBA?