Results 1 to 11 of 11
  1. #1
    2 Star Lounger
    Join Date
    Jul 2001
    Posts
    119
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Excel autoformat (97)

    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

  2. #2
    Platinum Lounger
    Join Date
    Nov 2001
    Location
    Vienna, Wien, Austria
    Posts
    5,009
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Excel autoformat (97)

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

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

    <pre>Sub AutoWidth()
    ChooseAll
    With Selection
    .EntireRow.Select
    .Columns.Autofit
    End With
    End Sub</pre>

    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
    Gre

  3. #3
    2 Star Lounger
    Join Date
    Jul 2001
    Posts
    119
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Excel autoformat (97)

    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

  4. #4
    2 Star Lounger
    Join Date
    Jul 2001
    Posts
    119
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Excel autoformat (97)

    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.

  5. #5
    Platinum Lounger
    Join Date
    Nov 2001
    Location
    Vienna, Wien, Austria
    Posts
    5,009
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Excel autoformat (97)

    Well, scrap the ChooseAll sub <img src=/S/grin.gif border=0 alt=grin width=15 height=15>. Try this

    <pre>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").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</pre>

    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 <A target="_blank" HREF=http://www.wopr.com/cgi-bin/w3t/showthreaded.pl?Cat=&Board=xl&Number=7333&Search=t rue&Forum=xl&Words=personal.xls&Match=And&Searchpa ge=1&Limit=99&Old=allposts&Main=6844>this</A> 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.
    Gre

  6. #6
    2 Star Lounger
    Join Date
    Jul 2001
    Posts
    119
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Excel autoformat (97)

    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

  7. #7
    Platinum Lounger
    Join Date
    Nov 2001
    Location
    Vienna, Wien, Austria
    Posts
    5,009
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Excel autoformat (97)

    If you want both columns and rows to AutoFit, try:<pre>Sub AutoSize()
    ChooseAll
    With Selection
    .EntireRow.Select
    .Columns.Autofit
    .EntireColumn.Select
    .Rows.Autofit
    End With
    End Sub</pre>

    Gre

  8. #8
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Excel autoformat (97)

    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.
    Legare Coleman

  9. #9
    Platinum Lounger
    Join Date
    Nov 2001
    Location
    Vienna, Wien, Austria
    Posts
    5,009
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Excel autoformat (97)

    Based on your discussions with WendellB on the Access board <A target="_blank" HREF=http://www.wopr.com/cgi-bin/w3t/showthreaded.pl?Cat=&Board=acc&Number=103526&page= &view=&sb=&o=&vc=1>here</A>, it sounds like the code you need is something like: <pre>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</pre>

    HTH
    Gre

  10. #10
    2 Star Lounger
    Join Date
    Jul 2001
    Posts
    119
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Excel autoformat (97)

    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?

  11. #11
    Platinum Lounger
    Join Date
    Nov 2001
    Location
    Vienna, Wien, Austria
    Posts
    5,009
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Excel autoformat (97)

    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. <UL><LI>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.)
    <LI>The template would contain an AutoOpen sub which would trigger the Import() sub you've already got and an Export() sub.
    <LI>The OLE would open Excel with a new workbook based on the template (a "transit" workbook).
    <LI>The Import() sub would take your Access data and format it as you require.
    <LI>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.
    <LI>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:<pre>Sub Auto_Open()
    Call Import
    Call Export
    End Sub</pre>

    The Export() sub would read something like:<pre>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</pre>

    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
    Gre

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •