Autorun macro to fix Tools-Options->view with imported worksheet

[Lyndon Rickards]

Greetings,

Have scanned the archive and google so now I come to the Oracles...

Problem:
Multi-worksheet excel file created with perl script. This is a report
with graphics, buttons and hyperlinks probably best done in
html. Don't ask why. Now I need to 'clean up' the appearance by removing
Row&Column headers etc. as available under Tools->Options->View, in each
worksheet.

Proposed:
After the spreadsheet is created, I add a cover page from a template
spreadsheet. I'm thinking it may be possible to copy in a macro
at the same time (from the template) to set the appearance of the
destination workbook. Maybe the macro could even autorun 'run-once' on
the import and delete itself..?

Is this practical? Any pointers, references, examples please?

Bear in mind my native planet is in the galaxy of *nix ;-)

TIA - Lynn.

 

[Dave Peterson]

Etc is a lot to do (or not).

But this shell may give you an idea of what to do. It displays a file|open
dialog. The user can select as many files as they want and then the template
workbook will be added to each workbook--and just the row/column headings are
hidden.

Option Explicit
Sub testme01()

Dim wksTemplateName As String
Dim myFileNames As Variant
Dim wkbk As Workbook
Dim wks As Worksheet
Dim fCtr As Long
Dim myWindow As Window

wksTemplateName = "C:\my documents\excel\book1.xls"

myFileNames = Application.GetOpenFilename("Excel Files, *.xls", _
MultiSelect:=True)

If IsArray(myFileNames) = False Then
Exit Sub
End If

For fCtr = LBound(myFileNames) To UBound(myFileNames)
Set wkbk = Workbooks.Open(Filename:=myFileNames(fCtr))
Sheets.Add before:=wkbk.Sheets(1), Type:=wksTemplateName
For Each wks In wkbk.Worksheets
wks.Activate
ActiveWindow.DisplayHeadings = False
'anything else you need here
Next wks
Application.Goto wkbk.Worksheets(1).Range("a1"), scroll:=True
wkbk.Save
wkbk.Close savechanges:=False
Next fCtr

End Sub

 

[Dave Peterson]

ps...

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

 

[Lyndon Rickards]

Thanks Dave - and on Xmas morning, too! Guess this'll be what I'll
be playing with later...

- Lynn.

 

[Dave Peterson]

Good luck...

You may want to record a macro while you're turning things on/off to get the
code to merge into that shell.