Formatting Excel document from Access

[SiGill]

Hi

I have copied and pasted the code from the below link.

http://www.access-programmers.co.uk/forums/showthread.php?t=84119

But I am getting an error message saying End If with no block If.

Can anyone helpw me adapt it to work.

Thanks

 

[SiGill]

Further to this if I take the End If out I get error message

9 - Subscript out of range

The workbook then stays open in the background and I am unable to go into the excel document to edit it as its saying I am already in the workbook

 

[namliam]

Your talking about this:

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("YourSheetName").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

You should just delete the endif, where are you getting the other error?

 

[SiGill]

Thats the code.

I have assigned this to the On Click of a button using
Call ModifyExportedExcelFileFormats(X:/myfile.xls)

It starts to look like its working then I get the 9 - Subscript out of range message.

I then can't go into the excel file as its sayign I am already in it.

 

[DCrake]

The first time you run the code and it failed the Excel instance was still open. Got to Task Manager and close all the instances of Excel and retry.

 

[SiGill]

Hi DCrake I rebooted my computer to close the Excel document as it wasn't appearing in the Task Manager, and then retried the code without the end if. Thats when I get the "9 - Subscript out of range" message.

 

[SiGill]

!!!!UPDATE!!!!

I have worked it out. Basically I was exporting a query to excel then formating it once exported. I was calling the File "Initial Distributions" but I noticed that Access had replaced the Space with an _.

Thanks though for your help

 

[ghudson]

Oops. Sorry about that. I removed the erroneous End If in the code you were referencing in the http://www.access-programmers.co.uk/forums/showthread.php?p=373163#post373163 thread.