hiding excel manipulations and showing a progress bar

[steve_bris]

Hi.

When a user hits a button to format an excel report that is generated by my database i would like to be able to set excel to do it invisibly and to have some sort of progress bar or something to just let the user know that it is doing the formatting in the background.....I know how to make excel be invisible( ExcelApp.Visible = False), but how would I go about making a progress bar ( possibly animated if it is doable) or a message box to say it is working.

Thanks for any help
Steve

 

[ghudson]

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.

[COLOR=Blue]'This is how you would call the below function to modify the "YourExcelFile.xls" file...[/COLOR]
Call ModifyExportedExcelFileFormats("X:\YourExcelFile.xls")

[COLOR=Red]'Place the "Public vStatusBar As Variant" at the top of a public module, not a form module.[/COLOR]
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("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

            .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

 

[steve_bris]

Thanks for the help ghudson

 

[Robgould]

I used this and it worked beautifully. I am not an experienced programmer though, and I am not sure I completely understand it all. I am using this right now to modify a spreadsheet that I know the name of. My question is, how would I use this to do two spreadhseets without re-running the whole thing? I guess, how can I call two seperate spreadhseets in my call statement? I am performing exactly the same functions on both sheets. Thanks for the help.

 

[ghudson]

Calling the Sub just like I have it listed above will allow you to call the Sub for a specific file. You have to supply the exact location and file name as the 'string'. Like this...

Call ModifyExportedExcelFileFormats("C:\YourFile.xls")

 

[Robgould]

What if there are two files with two different names? I would like them to both update in the same step with the same bar. sorry for the questions that probably seem obvious to you.

 

[ghudson]

Then you call the ModifyExportedExcelFileFormats() function each time you need it for "each" file.

Call ModifyExportedExcelFileFormats("C:\YourFile#1.xls")
Call ModifyExportedExcelFileFormats("C:\YourFile#2.xls")

I designed the code to be reusable for you only has to supply the location to the file that needs to be modified.

 

[Robgould]

Thank you for the help

 

[Summer123]

hello i am running into similar issue but when i use ur code it give me an error stating "9-subscript out of range".. what is this for?? my form has one button only and here is what i am doing in the onclick function and then created a module to incude your code that u have posted above ghudson

Option Compare Database
Function fn() As String
fn = "C:\Workspace\SCF DB\trial\test"
End Function
Function sn() As String
sn = "testsheet"
End Function
Private Sub Command0_Click()
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "2- Missing_Data_on_01", fn, True, sn
Call ModifyExportedExcelFileFormats(fn)
End Sub

please help ghudson

 

[tgwizkid]

Try debugging, I had the same issue and if you notice that the line

.Application.Sheets("YourSheetName").Select

has a generic sheet name, you have to reference the exact sheet name or you will get an error. Hope that helps.

If you put a breakpoint inside of the function(ModifyExportedExcelFileFormats) rather than before it in the function you are calling it from, you can go through it line by line and determine which line is generating the error, that might help you out if it isn't the sheetname error. Hope that makes sense.

 

[mubi_masti]

I have used same code and facing same problem (i.e. "9-subscript out of range".. ) and no solution is identified, can any one help me out in this regard.

 

[VNVfan]

Hi ghudson -

I've used your code here alongside a DoCmd.TransferSpreadsheet command to export, then format a spreadsheet - which, as Robgould put it, worked beautifully

However, I do have a slight issue I was wondering if you'd be able to shed light upon as I'm a rubbish coder! When I run the code the first time it works absolutely fine, but if I delete the exported spreadsheet and try to run it again (including the export part), I get an error - "91 - Object variable or With block variable not set." Investigating a little further, I'm finding that when it has ran and is complete, there is still an "EXCEL.EXE" process open in the Windows Task Manager. I thought this was the cause of the issue and was going to ask you how I can get it to close the process (I'm using Office 2003 on Windows XP), however when I ran a quick test to see if this was the case I had a different issue.

This time I ran the code, then opened Task Manager and ended the "EXCEL.EXE" process, then deleted the exported file. On running the code again, I now get the error "462 - The remote server machine does not exist or is unavailable." (I'm exporting the file to my local C: drive).

I have changed your code, but only very slightly - just to change the way that the file was being formatted - here's what I changed it to:

Option Explicit
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("TrafficLightAdminSERepex").Select
            .Application.Cells.Select
            .Application.Selection.ClearFormats
            .Application.Rows("1:1").Select
            .Application.Selection.Font.Bold = True
            .Application.Columns("A:A").EntireColumn.AutoFit
            .Application.Columns("B:B").EntireColumn.AutoFit
            .Application.Columns("C:C").EntireColumn.AutoFit
            .Application.Columns("D:D").EntireColumn.AutoFit
            .Application.Columns("E:E").EntireColumn.AutoFit
            .Application.Columns("F:F").EntireColumn.AutoFit
            .Application.Columns("G:G").EntireColumn.AutoFit
            .Application.Rows("2:2").Select
            .Application.Range(Selection, Selection.End(xlDown)).Select
            .Application.Range(Selection, Selection.End(xlDown)).Select
            .Application.Selection.FormatConditions.Delete
            .Application.Selection.FormatConditions.Add Type:=xlExpression, Formula1:="=$G2>10"
            .Application.Selection.FormatConditions(1).Interior.ColorIndex = 3
            .Application.Selection.FormatConditions.Add Type:=xlExpression, Formula1:="=AND($G2<11,$G2>4)"
            .Application.Selection.FormatConditions(2).Interior.ColorIndex = 45
            .Application.Selection.FormatConditions.Add Type:=xlExpression, Formula1:="=$G2<5"
            .Application.Selection.FormatConditions(3).Interior.ColorIndex = 10
            .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
End Sub

This is held in a module, which is being called from a button on a form that does the following:

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "TrafficLightAdminSERepex", "C:\DB\FormatTest.xls", True
Call ModifyExportedExcelFileFormats("C:\DB\FormatTest.xls")

Any ideas?

 

[VNVfan]

Nevermind, I think I've solved it - as least it works repeatedly now. Before I asked the question I didn't know how to get Access to step through each line of code so I could figure out what line was causing the error. But I do now, and I found the line causing the error was:

.Application.Range(Selection, Selection.End(xlDown)).Select

It didn't seem to like this, when I hovered the cursor over the word "Selection" in that line I was getting the With block variable error (though strangely it still worked fine on the first run after restarting Access and closing all Excel's processes). I've changed the line to:

.Application.Range("A2", "Z65536").Select

Seems to be working fine now.

 

[Mr. B]

Investigating a little further, I'm finding that when it has ran and is complete, there is still an "EXCEL.EXE" process open in the Windows Task Manager. I thought this was the cause of the issue and was going to ask you how I can get it to close the process (I'm using Office 2003 on Windows XP), however when I ran a quick test to see if this was the case I had a different issue.

The problem is related to the fact that Excel is remaining open. This occurs if you try to use Excel commands without expressly defining an object and expressly using that object to perform the command.

Take a look at this thread:
http://www.access-programmers.co.uk/forums/showthread.php?t=178799&highlight=Excel+open

In this thread Bob Larson explains why this happens and hopefully you will be able to find where you are missing the mark.

 

[VNVfan]

Thanks for that link, it was an interesting read - although I'm not going to pretend I understood all of it! Bookmarked for the next time I try a similar activity though.
As far as my project above is concerned, after I changed that line that it didn't seem to like, the Excel process is now closing correctly once it's complete.

 

[Rx_]

This code has not left orphaned Excel object. It avoids setting the Worksheet object.
Each of my Excel 'reports' are an individual Function with a standard set of arguments. The Function returns a T/F to indicate pass fail.

In advance to running code to create an instance of Excel, the SQL statement is run in a recordset object. Then the user name is checked, if this report does not already have a network folder, permission is asked to create a new folder with the report name under the user's network folder with a timestamp appended to the name. This way Excel will have a home early in the procedure.
Excel is created, then the CopyFromRecordset method is used. The Recordset object allows a count of columns and rows that come in handy for the Excel formatting later.
My Excel reports are complex with custom formulas inserted and can contain multiple worksheets with crosstabs and the like. I am not the greatest coder and make lots of mistakes during development. Avoiding orphaned Excel objects during a debug crash is important.

The Progress Bar - question would be of great interest to me and others. Maybe continute that on another thread? - Thanks

For what it is worth here is the code:

380       If ObjXL Is Nothing Then
390           Set ObjXL = New Excel.Application
400           ObjXL.EnableEvents = False
420       Else
430           Excel.Application.Quit
440           DoEvents
450           Set ObjXL = New Excel.Application
460           ObjXL.EnableEvents = False
480       End If
490   On Error GoTo PROC_ERROR
500     ObjXL.Visible = False                                                              
510     ObjXL.Workbooks.Add
520     intWorksheetNum = 1
530     intRowPos = 1
540        ObjXL.Worksheets(intWorksheetNum).Name = "WTF Report"

PROC_EXIT:
3240    On Error Resume Next
3250    ObjXL.Quit
3260    Set ObjXL = Nothing
3270    Set rsWTF = Nothing
3280       On Error Resume Next
3290       Exit Function
PROC_ERROR:
3300       Select Case Err.Number
              'Case 'several Cases not shown for demo purposes
              Case Else
3310               ObjXL.DisplayAlerts = True
3320               Call LogUsage("WTF Report Excel error", "Proc_Error", Err.Description)  ' A custom Error logging routine
3330               Resume PROC_EXIT
3340       End Select

 

[MMNewGuy]

Thank You ghudson for this excellent code !!

I had "9-subscript out of range" like some of the others. I added a second argument to the function for the sheet's name

Public Sub ModifyExportedExcelFileFormats(sFile As String, sSheet As String)

and changed

.Application.Sheets("YourSheetName").Select

to

.Application.Sheets(sSheet).Select

and then I call it using

Call ModifyExportedExcelFileFormats("mypathfile.xlsx", "mysheetname")

This worked for me.