Formatting Excel workbook from vba in access 2003

[nyneave]

Did you retype or copy-and-paste? I copied and pasted, but had to change the button name because it is now Format_Sheet (I moved the code to a new button so I can focus on this without recreating the spreadsheet every time).


Where is strFileName assigned? I also removed the strFileName back to what I had below (inserting the whole filepath of filePath = "U:\Enrollment\Public\NEWBORN\" & strFilename & " " & Format(Now, "YYYY-MM-DD") & ".xls"

Is the file extension .xls or .xlsx? The file is .xls which I verified and know because I create the file

Is Option Explicit specified? No

Did you check with Debug/Compile before executing your code? Yes

It's unlikely that a With block is causing the error and therefore the culprit is xlBook or, maybe, filePath. (I have now tested to this point in my system and all works fine.) The favourite culprit is xlBook; check for typos.


OK, copy and paste the following code to replace what you have. Use Debug/Compile to check for sytax errors, missing references, etc. Then execute the code.

Code:

Execution will halt at the Stop statement. Your spreadsheet should, at this point, be loaded and visible.

[nyneave]

oops..hit enter...anyway, now - I copied and pasted your code EXACTLY with the exception of changing the button name to Format_Sheet_Click(). I ran debug and it doesn't like the line that reads:
Err.Raise Err.Number

I commented out that line and ran. It doesn't even get to the STOP point. It gives me the error immediately:

Formatting did not work

Nyneave

[nyneave]

For rpeare...I appreciate the input; unfortunately, the spreadsheet that gets created is dynamic - the tab names change every time it is run. I would have no idea how to use a template and fill it in with dynamic information Not to mention; my deadline to resolve this issue is Tuesday night

Nyneave

[rpeare]

Nynaeve.zip

Here's an example.

The database and the excel file must be in the same folder
the file contains three sheets with basically 1 through 10 listed in order in column A
(open it first to make sure they all have the same 10 point format)
Open the form in the database and click the button.

It takes the file, alters the format of both a single cell and a range of cells and saves the changes.

[Rod]

You should have mentioned your deadline before! Here, if not too late, is the multi-sheet solution in, as near as I can make it, your style. The error handling needs more work. Change the assignation of filePath to what you need.

Private Sub cmdFormatSheet_Click()

    Dim xlApp As Excel.Application
    Dim xlBook As Excel.Workbook
    Dim xlSheet As Excel.Worksheet
    Dim filePath As String
    Dim arySheets() As String
    Dim lngErrNumber As Long
    Dim i As Integer


    On Error GoTo cmdFormatSheet_Click_Error

    
    'Set a reference to an Excel application.
    On Error Resume Next
    Set xlApp = GetObject(, "Excel.Application")
    Select Case Err.Number
    Case 0                  'Excel application already running.
        On Error GoTo cmdFormatSheet_Click_Error
    Case 429                'Excel application not running so create it.
        On Error GoTo cmdFormatSheet_Click_Error
        Set xlApp = CreateObject("Excel.Application")
    Case Else               'Some other run time error, so report it.
        GoTo cmdFormatSheet_Click_Error
    End Select

    
    'If Excel has no workbooks then make it minimized.
    
    If xlApp.Workbooks.Count = 0 Then xlApp.WindowState = xlMinimized
    xlApp.Visible = True

    
    'In normal course of events open the workbook minimized.
    
    filePath = "C:\Users\Rod\Documents\Test.xlsx"
    'filePath = "U:\Enrollment\Public\NEWBORN\" & strFileName & " " & Format(Now, "YYYY-MM-DD") & ".xls"
    
    Set xlBook = GetObject(filePath)
    xlBook.Windows(1).Visible = True
    xlBook.Windows(1).WindowState = xlMinimized

    
    'Redimension array with number of worksheets (base 0)
    'Load worksheet names into array.
    
    ReDim arySheets(xlBook.Worksheets.Count - 1)
    i = 0
    For Each xlSheet In xlBook.Worksheets
        arySheets(i) = xlSheet.Name
        i = i + 1
    Next

    
    'Select multiple worksheets and the cell range.
    
    xlBook.Activate
    xlBook.Sheets(arySheets).Select
    xlBook.Sheets(1).Activate
    Range("A1:M50").Select

    
    'Format
    
    With Selection.Font
        .Name = "Arial"
        .Size = 10
        .Bold = True
    End With

    
    'Save and clean up.
    
    xlBook.Close SaveChanges:=True
    Set xlBook = Nothing
    If xlApp.Workbooks.Count = 0 Then xlApp.Quit
    Set xlApp = Nothing
    MsgBox "Formatting successfully completed.", vbInformation

Exit_Procedure:
    On Error GoTo 0
    Exit Sub

cmdFormatSheet_Click_Error:
    lngErrNumber = Err.Number
    On Error GoTo 0
    If Not xlApp Is Nothing Then
        xlApp.Visible = True
        xlApp.WindowState = xlNormal
    End If
    If Not xlBook Is Nothing Then
        xlBook.Windows(1).Visible = True
        xlBook.Windows(1).WindowState = xlMaximized
    End If
    MsgBox "Formatting failed with run time error " & lngErrNumber, vbCritical
    Resume Exit_Procedure
    Resume

    
End Sub

[nyneave]

Hello Rod,

I posted the code you sent (with file path updates) and I receive the following error:

Formatting failed with runtime error 432

Excel opened, but I didn't see the workbook open; just the app?

Thanks again!
Nyneave

[SteveH2508]

Run-time error '432' File name or class name not found during Automation operation

On the line after you set filePath put

Debug.Print filePath

Show us the output in the Immediate window from that statement.

[nyneave]

Thank you so much! it is working I was missing the text string in the filename. Sorry to have been such as pain; but you guys are great!

[nyneave]

I'm back Now when I run the code (the same code we inserted from above), I get an error of Formatting Failed with Runtime error of 1004...any idea why this suddenly started happening?

Thanks again,
Nyneave

[Rod]

Oh dear. Is this a case of: "It was working and then suddenly I get error 1004?" If so, then the likely culprit is your spreadsheet; something has changed and it is unlikely to be the code. Unfortunately I wrote the error handler to be somewhat basic - I wanted a self-contained handler that would work for you - and it does little to pinpoint the actual cause. I shall review the code and suggest some additions to help debugging the problem. Leave it with me.

Meanwhile, have you tried rebooting to eliminate any hidden Excel applications?

[Rod]

    MsgBox "Formatting failed with run time error " & lngErrNumber, vbCritical
    Stop
    'Resume Exit_Procedure
    Resume

Make two changes to the procedure’s error code:

1. Include the Stop statement between the MsgBox and Resume statements (highlighted in red);
2. Comment-out the first Resume statement.

Before you run the program – and this is important – reboot your machine and wait until all disk activity has ceased. There seem to be situations where the OS has yet to load needed objects if you attempt to run too soon.

Run the format program. If you get an error, click on OK and you will immediately be placed at the Stop statement. Press F8 and you will go to the Resume statement. Press F8 again and note the statement that is now highlighted. This is the statement triggering the error.

[SteveH2508]

If it runs once correctly and then errors out the second time the culprit is usually an unqualified reference to the Excel object in the code. These can be a b*gg*r to find.

Assuming that the code being run is from post 20 I would suspect the statement


Range("A1:M50").Select

Try xlBook.Range("A1:M50").Select

(untested aircode)

Also you probably do not need to select the range - you should be able to just point to the range and do the formatting.

[Rod]

Hi Steve,

If it runs once correctly and then errors out the second time the culprit is usually an unqualified reference to the Excel object in the code. These can be a b*gg*r to find.

Couldn't agree more! Here's an extract from my first post.

Before I even try to help you with your particular requirement may I make a crucial observation. This concerns how you are opening Excel. It is dangerous to simply issue a CreateObject command since this will create another Excel application even if there is already one in existence. With two or more Excel applications running simultaneously weird and 'wonderful' things happen. You may experience errors that occur only on the first, third, fifth, ... iteration (or alternately on the second, fourth, ..). (What you describe is typical of a multi-Excel environment: it appears to work, there is no error message but nothing happens. I suspect it is working, but not in the application you expect!)

Range("A1:M50").Select

Try xlBook.Range("A1:M50").Select

Shouldn't make any difference since there's a xlBook.Activate statement preceding it.

Also you probably do not need to select the range - you should be able to just point to the range and do the formatting.

Not sure this will work as it's a multi-sheet select, but I'll try it and let you all know.

PS (Later) With Range("A1:M50").Font formats only the first sheet! I knew there was a reason for the Select.

[nyneave]

As much as I appreciate your help - I can't get the error to come up again after the reboot (not that I'm complaining). However, I reboot my machine and I believe the end-user reboots nightly. I have rerun the code 6 times today and it won't error out! Anyway, I will keep this post handy this week and see if it comes up again

Thank you!
Nyneave

[Rod]

It's frustrating! Office Automation can be very fragile at times. I'm pleased to hear that the problem has gone away but that's not a satisfactory solution. Steve puts it very plainly:

If it runs once correctly and then errors out the second time the culprit is usually an unqualified reference to the Excel object in the code. These can be a b*gg*r to find.

I'm not too sure what he means by an 'unqualified reference' but situations can occur where Access has an object variable that is no longer valid or Access has an object variable pointing to one of a multiple set of Excel applications. (Excel should only have one copy of the application that hosts zero to many workbooks. Access on the other hand has one copy of the application for every open database.)

So how do these situations occur? Despite my best efforts to clean up if an error occurs, it is usually after the Office Automation crashes for the first time that the errors start. So what causes it to crash the first time? The following are my empirical observations, I have no technical evidence for making these assertions.

1. Running an Office Automation too soon after booting the system may cause it to crash. I suspect that in an effort to make the boot seem quick, Microsoft has deferred installing some of the processes and objects necessary for Office Automation. Certainly there is furious disk activity even after the user has logged on and the OS appears. Waiting until this disk activity subsides seems to avoid this cause of crash.
2. If there is no error handling (or error handling unaware of the dangers) it is possible that users get the standard run-time error message with the 'End' and 'Debug' options. Clicking on 'End' stops and cancels the code and thus, perhaps, the clean up code is not run. Hence it may be that the Excel application is still running or that Access variables are still assigned. The strategy of using GetObject-CreateObject should ensure that the existing copy of Excel is retrieved but this does not always seem foolproof. After a crash it is instructive to invoke the task manager - Ctrl+Alt+Delete - and see if: a) Excel is listed among the applications; b) Excel is listed under the processes. Often it is a case that b) is true while a) is false.
3. I have a suspicion that sometimes it is sheer bad luck and is caused by a timing conflict or a process conflict. I really cannot justify this statement. However modern computer systems, especially those connected to the net, have many processes that may occur automatically - disk defrags, emails arriving, antivirus scanning, open web sites updating, notices from software suppliers, etc. Perhaps, just perhaps, one of these causes a conflict.

Thus my advice, that the moment a crash occurs, to reboot the system; it's the only sure way. Closing and restarting Access may solve the problem if the cause is a rogue Access variable but won't solve the problem if it is caused by rogue (sometimes hidden) Excel applications and processes.