Export Format question...

[Mobjack]

I did a search on this already, but I am still having problems. Any help would be appreciated.

I have a table that I want to export about half of the fields from into an Excel spreadsheet. I want to keep certain things intact, like the logo on the header, page number, cell outlines, etc.

I have been able to export using the Module Wizard, however, I cannot seem to get the formatting straight. I found the following code in one of the other posts here:

"Option Compare Database

'This is how I modify an exported Excel file.
'The user will not "see" that the Excel file is being modified nor will it ask you to okay the saved changes.
'I use the SysCmd() method to display a message in the status bar. Modify it to meet your needs.

'This is how you would call the below function to modify the "YourExcelFile.xls" file...
Call ModifyExportedExcelFileFormats("C:\Documents and Settings\aa60057\My Documents\Provider Readiness\Reports\TestExport.xls")

'Place the "Public vStatusBar As Variant" at the top of a public module, not a form module.
Public vStatusBar As Variant

Public Sub ModifyExportedExcelFileFormats(sFile As String)
On Error GoTo Err_ModifyExportedExcelFileFormats

Application.SetOption "Show Status Bar", True

vStatusBar = SysCmd(acSysCmdSetStatus, "Formatting export file... please wait.")

Dim xlApp As Object
Dim xlSheet As Object

Set xlApp = CreateObject("Excel.Application")
Set xlSheet = xlApp.Workbooks.Open(sFile).Sheets(1)

With xlApp
.Application.Sheets("923ProfessionalProviders Query").Select
.Application.Cells.Select
.Application.Selection.ClearFormats
.Application.Rows("1:1").Select
.Application.Selection.Font.Bold = True
.Application.Cells.Select
.Application.Selection.RowHeight = 12.75
.Application.Selection.Columns.AutoFit
.Application.Range("A2").Select
.Application.ActiveWindow.FreezePanes = True
.Application.Range("A1").Select
.Application.Selection.AutoFilter
End If
.Application.Activeworkbook.Save
.Application.Activeworkbook.Close
.Quit
End With

Set xlApp = Nothing
Set xlSheet = Nothing

vStatusBar = SysCmd(acSysCmdClearStatus)

Exit_ModifyExportedExcelFileFormats:
Exit Sub

Err_ModifyExportedExcelFileFormats:
vStatusBar = SysCmd(acSysCmdClearStatus)
MsgBox Err.Number & " - " & Err.Description
Resume Exit_ModifyExportedExcelFileFormats"

1st. I dont know where to put this code. I tried creating a new module, but when I tried to run it I was getting a "Select Macro" message box.

Any help would be appreciated.

Windows 2K - Access 2002

Thanks!

 

[Mobjack]

Anyone?

 

[Mobjack]

One solution....

For anyone who may be following this thread, I found 1 workaround to my problem. Instead of trying to export from Access, I decided to try and import from Excel. I created a template file first with the layout I wanted and then I import the query and it works pretty nicely. It does not look as nice as the Access report version, but it is functional none the less.