Formatting Spreadsheet After TransferSpreadsheet

[Patten]

I run an Access application that creates about 700 Excel spreadsheets every month, each with a unique name. I'm using the 'DoCmd.TransferSpreadsheet'. My problem is that I need these spreadsheets to be formatted, ie. font, cell size, page size and protect the entire spreadsheet. I have looked at solutions using the Keyword Search, but I still don't understand how to do it. Can anyone help?

 

[scking]

If you are looking for a solution in Access help files you are on the wrong track. Access allows you to create an Excel file but does not allow you to perform anything else. If you want to consider using automation and COM libraries merely add the Excel object libraries as a reference and learn by doing. You could also open up Excel and go to Tools\Visual Basic Editor to use their help files.

You will be learning a completely different object model that Access uses and should be comfortable with using the object browser on the various libraries.

-----------
1) Set the reference in Access
2) dimension and set your objects
3) Load and format your spreadsheets

Sample Snippet to use automation to open Excel and load a file.
-----------
Dim i As Integer
Dim strListOfFiles As String
Dim oExcel As Excel.Application
Dim oWb As Workbook
Dim oWs As Worksheet

On Error Resume Next

Set oExcel = GetObject("Excel.Application&quot
If Err.Number <> 0 Then
Set oExcel = CreateObject(&quot;Excel.Application&quot
End If
oExcel.Visible = True

oExcel.Workbooks.Add
Set oWb = oExcel.ActiveWorkbook
Workbooks.Open Filename:=&quot;C:\Apps\ToMgmt\Outbox\OCALC_LII_BOM3.xls&quot;
ActiveWindow.Visible = False ----------------------
scking@arinc.com
Life is filled with lessons.
We are responsible for the
results of the quizzes.
-----------------------