Move Excel Column

[rwboyden via AccessMonster.com]

I'm exporting data from an Access query to an Excel spreadsheet. I have no
control over the order of the fields in the query and would like to be able
to use code to move and resize some of the columns in the Excel Spreadsheet
after the export. I'm already using code to format the column headers and
change the font in the body, but I can't seem to find any references to
moving columns or to adjusting column widths. Any help will be veryuch
appreciated.

Bob Boyden

 

[John Nurick]

Hi Bob,

The Excel Range object has a ColumnWidth property, so you can easily do
something like

Dim oWKS as Excel.Worksheet
Set oWKS = ...
oWKS.Columns(1).ColumnWidth = 5

Moving columns is a bit more complicated because you have to ensure that
nothing gets overwritten accidentally. Have you considered creating a
query that gets its data from the original query but returns the columns
in the order you want?

Otherwise, you can do something like this:
oWKS.Columns(5).Insert 'make room
oWKS.Columns(2).Cut Destination:=oWKS.Columns(5) 'move data
oWKS.Columns(2).Delete 'get rid of empties

 

[rwboyden via AccessMonster.com]

Thanks, John

Hi Bob,

The Excel Range object has a ColumnWidth property, so you can easily do
something like

Dim oWKS as Excel.Worksheet
Set oWKS = ...
oWKS.Columns(1).ColumnWidth = 5

Moving columns is a bit more complicated because you have to ensure that
nothing gets overwritten accidentally. Have you considered creating a
query that gets its data from the original query but returns the columns
in the order you want?

Otherwise, you can do something like this:
oWKS.Columns(5).Insert 'make room
oWKS.Columns(2).Cut Destination:=oWKS.Columns(5) 'move data
oWKS.Columns(2).Delete 'get rid of empties

I'm exporting data from an Access query to an Excel spreadsheet. I have no
control over the order of the fields in the query and would like to be able

[quoted text clipped - 5 lines]

Bob Boyden

 

[rwboyden via AccessMonster.com]

Thanks, John

Hi Bob,

The Excel Range object has a ColumnWidth property, so you can easily do
something like

Dim oWKS as Excel.Worksheet
Set oWKS = ...
oWKS.Columns(1).ColumnWidth = 5

Moving columns is a bit more complicated because you have to ensure that
nothing gets overwritten accidentally. Have you considered creating a
query that gets its data from the original query but returns the columns
in the order you want?

Otherwise, you can do something like this:
oWKS.Columns(5).Insert 'make room
oWKS.Columns(2).Cut Destination:=oWKS.Columns(5) 'move data
oWKS.Columns(2).Delete 'get rid of empties

I'm exporting data from an Access query to an Excel spreadsheet. I have no
control over the order of the fields in the query and would like to be able

[quoted text clipped - 5 lines]

Bob Boyden