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.
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.
Code:
[COLOR=Blue]'This is how you would call the below function to modify the "YourExcelFile.xls" file...[/COLOR]
Call ModifyExportedExcelFileFormats("X:\YourExcelFile.xls")
Code:
[COLOR=Red]'Place the "Public vStatusBar As Variant" at the top of a public module, not a form module.[/COLOR]
Public vStatusBar As Variant
Code:
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
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.
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...
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.
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
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.
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.
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:
Code:
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:
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:
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:
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 WindowsXP), 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.
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.
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:
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"
Code:
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
