Locking column widths when importing data

Microsoft Microsoft excel 2007 (pc)
March 8, 2010 at 18:00:00
Specs: Windows 7
How do you lock column widths in Excel 2007 when importing data from Access 2007? I am using an invoice template I designed that pulls records from an Access 2007 query. The column widths for the data change each time new data is imported. It is totally screwing up the formatting I want for the worksheet. How can I lock the widths of these columns? Please, I'm pulling my hair out over this one!

See More: Locking column widths when importing data

Report •


#1
March 8, 2010 at 18:47:20
Not sure this will work for you, but have you tried this:

I’m not using 2007 so these are for pre-2007
It shouldn’t be to much trouble to convert them.

First, Highlight the cells / columns that you want to be able to import to
Then click Format
Select Cells
Select the Protection tab.
Uncheck the Locked check box
Click OK
Next, select Tools,
Protection,
Protect Sheet

Now you cannot change the column width,
But you should be able to edit only those cells / columns that are formatted as unlocked.

Once you have the data in place, if you want, just reverse the protections.

MIKE

http://www.skeptic.com/


Report •

#2
March 8, 2010 at 18:59:45
Thanks Mike, but I've tried that, and even with the cells to be imported to unlocked, I get an error, as if the whole sheet was protected. Any other ideas?

Report •

#3
March 8, 2010 at 19:03:42
Best I can think of.

Sorry

What's the error message you get?


MIKE

http://www.skeptic.com/


Report •

Related Solutions

#4
March 8, 2010 at 20:46:47
It says:

The cell or chart that you are trying to change is protected and therefore read-only.


Report •

#5
March 9, 2010 at 04:21:46
Hi,

How about running a macro after the data import. The macro would set the required column widths. Add a button to the quick access toolbar and link it to the macro, then it's a 'one click' solution. Not ideal, but better than manually correcting each column width.

The code required is just this:

Sub SetWidths()
With Workbooks("AccessInput.xls").Worksheets("Invoice")
    .Columns("B").ColumnWidth = 12
    .Columns("C").ColumnWidth = 1.25
    .Columns("D").ColumnWidth = 17
End With
End Sub

Regards


Report •


Ask Question