Excel autoformat (97)

Topic

New Reply

IS there anyway to apply formatting changes automatically to an excel spreadsheet. I import from access to excel and then always have to resize 20+ columns of info to see everything in each cell. Can an optin be set that will automatically fix this problem.

Thanks,

Ed

Reply | Quote

Replies

To the best of my recollection Autofit was available in Excel 97. On that basis, you could try these two VBA routines:

Sub ChooseAll()
    ActiveSheet.Cells.SpecialCells(xlCellTypeLastCell).Select
    Range(ActiveCell, "a1").Select
End Sub

Sub AutoWidth()
    ChooseAll
    With Selection
        .EntireRow.Select
        .Columns.Autofit
    End With
End Sub

You can store these in a Module named (say) “ToolBox” in your personal.xls file. Attach the AutoWidth macro to a custom icon on one of your toolbars and then activate it when you import the data. There are more detailed ways of automating, if you can be more specific about your importing routines.

HTH

Reply | Quote

This is almost ideally what i would like but i need a little bit more control upon investigation. Can I indivdually and manully set the width of each column/row. So no autofit, I would like column A width = 5, column B width =10.2, row 1 length= 7.7 etc. is that allowed. thanks alot, this one worked but if i had full control it would be alot easier to read. thanks again.

Ed

Reply | Quote

Well, scrap the ChooseAll sub . Try this

Sub Format()
    Columns("A:A").Select
    Selection.ColumnWidth = 5
    Columns("B:B").Select
    Selection.ColumnWidth = 10.2
    Columns("C:C").Select
    Selection.ColumnWidth = 5.71
    Columns("D:D").Select
    Selection.ColumnWidth = 19.86
    [copy from above to add code for remaining Columns here]
    Rows("1:1").Select
    Selection.RowHeight = 7.7
    Rows("2:2").Select
    Selection.RowHeight = 8
    Rows("3:3").Select
    Selection.RowHeight = 7.25
    [copy from above to add code for remaining Rows here]
End Sub

Adjust the Widths and Heights to match your constraints and fill in each of your remaining Rows and CoIumns; making sure that you take out the lines in square brackets before you finish.

Your other post says you can’t remember how to set up a personal.xls file. There’s a good description in steps 1 to 4 of this post. Freely adapt the remaining steps to your own circumstances. You should now have the code available automatically every time you open Excel; rather than in the “target” workbook.

Reply | Quote

unmamunka you’ve been a huge help everything works but like always theres 1 more thing. I think the autofit will suffice for now and i tried to auto fit the rows too with the line “.Rows.AutoFit”

Sub AutoWidth()
ChooseAll
TopAlign
With Selection
.EntireRow.Select
.EntireColumn.Select
.Rows.AutoFit // my addition
End With
End Sub

this line will only auto fit the first row and not the rest, i tried “EntireColumns.Select” above it but that didn’t work. Got the solution for this one too, thank you.

Ed

Reply | Quote

If you want both columns and rows to AutoFit, try:

Sub AutoSize()
    ChooseAll
    With Selection
        .EntireRow.Select
        .Columns.Autofit
        .EntireColumn.Select
        .Rows.Autofit
    End With
End Sub

Reply | Quote

Based on your discussions with WendellB on the Access board here, it sounds like the code you need is something like:

Sub Import()
   ActiveSheet.PasteSpecial Format:="Biff5", Link:=False, DisplayAsIcon:=False
   With Selection 
            .ColumnWidth = 20 'or whatever width happens to suit you
            .WrapText = True
   End With
End Sub

HTH

Reply | Quote

ok so in my access macro i added the line runcode. i set the function name = Import(). But when i run the program i get “the expression you entered has a function name access can’t find. am i missing something in the set function name? i copy and pasted your code into a module and named it Import. I don’t see whats wrong, do you?

Reply | Quote

Hi

Reread your posts with WendellB. Firstly: apologies you need to take “Biff5” out and put in “Unicode Text”, “Text” or “Csv”. In all other respects, the Import() sub code stands.

What I am supplying you with – based partly on your earlier posts – is the code you need to call once you have opened the Excel file using OLE. There is no Paste Special in Access.

I think we may need some more details of how this would run. On the basis that you’re going to have a number of these Excel files floating around, a good way to go, as already has been said, may be to use a template. These have *.xlt extensions.

• Your template would sit in your Program FilesMicrosoft OfficeOfficeTemplates folder. (Check this as I’m mostly using Office 2000. Once you’ve got you *.xlt file in the right folder, it’ll show up as a choice when you select File|New in Excel.)
• The template would contain an AutoOpen sub which would trigger the Import() sub you’ve already got and an Export() sub.
• The OLE would open Excel with a new workbook based on the template (a “transit” workbook).
• The Import() sub would take your Access data and format it as you require.
• The transit workbook, however, would contain the macros that it derived from the template. One way of saving the data in your desired format without having the Import() sub trigger every time you opened the file would be to strip the formatted sheets out into a “plain vanilla” workbook. This is what the Export() sub would be needed for.
• The Export sub() would move the sheet(s) out, save and close the plain vanilla workbook and then close the transit file without saving.[/list]The AutoOpen sub would simply read:

  Sub Auto_Open()
    Call Import
    Call Export
  End Sub

  The Export() sub would read something like:

  Sub Export()
    Sheets("Sheet1").Select  
    Sheets("Sheet1").Move  'This creates your plain vanilla workbook
    ActiveWorkbook.SaveAs Filename:="[NetworkPathFileName.xls - see comment below]", _
          FileFormat:= xlNormal, Password:="", WriteResPassword:="", _
          ReadOnlyRecommended:=False , CreateBackup:=False  'CHECK these - see below
    ActiveWindow.Close     'This will leave you with only the transit workbook
    ActiveWorkbook.Saved=True  'Remove the "Do you want to save" prompt
    Application.Quit           'Close Excel
  End Sub

  Whatever file-naming convention you have, you’ll probably need to set up a sub to call a String value for your filenames. There’s probably code for this already on the Lounge. The arguments for the File Save need to be checked for Excel97 (my copy is not available at present). CreateBackup is almost certainly an Excel 2000+ feature. Try autorecording a File Save As Sub on your machine and it will probably enable you to find the defaults.

  There may be suggestions of a more elegant way to do this, but first you need to get the OLE sorted out. Good luck!

  HTH

Reply | Quote

If you always want the format to be the same fixed width/height, then you probably should open a blank workbook and format the rows/columns the way you want. Then save this workbook as a Template. Then use this template to create the empty workbook before pasting in the values.

Reply | Quote

Also this sounds really dumb but how do i save the code so that when this file is overwritten the code does not get lost? i can’t remember what to do.

Reply | Quote