Move Excel Column

  • Thread starter Thread starter rwboyden via AccessMonster.com
  • Start date Start date
R

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
 
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
 
Thanks, John

John said:
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
Click to expand...
[quoted text clipped - 5 lines]
Bob Boyden
Click to expand...
Click to expand...
 
Thanks, John

John said:
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
Click to expand...
[quoted text clipped - 5 lines]
Bob Boyden
Click to expand...
Click to expand...
 
Back
Top