Unable to Open Forms after Exporting Data to Excel

[zionman4]

Hi,
I am in need of assistance. I have created the following code to export data from an access project to Excel. After running the code and creating the Excel file and then closing the form that calls the code, I am no longer able to re-open the form. Some of the code that is used to load defaults on the form is no longer recognized. The strange part is that other users that have not run the code below, are able to open the form w/o issues. Is the code below doing something to the form or to Access itself? It happens on versions 2003, 2007 and 2010.

Public Sub XLS_Export(strID As String)
On Error GoTo Routine2

Dim objXL As Excel.Application
Dim wbk As Excel.Workbook
Dim wks As Excel.Worksheet
Dim rng As Range
Dim strFullPath As String

Dim rst As ADODB.Recordset
Dim strSQL As String
Dim varDt As Variant

DoCmd.Hourglass True

varDt = Format(Date, "yyyymmdd")
strFullPath = CurrentProject.Path & "\Summary_" & varDt & ".xls"

strSQL = "SELECT * "
strSQL = strSQL & "FROM sql_view WHERE id = '" & strID & "';"

' Set to Break on all Errors
Application.SetOption "Error Trapping", 0


Set rst = New ADODB.Recordset

'Assign to recordset
rst.Open strSQL, Application.CurrentProject.Connection, adOpenStatic, adLockReadOnly

'Check for Results
If Not rst.EOF = True Then

' Create the Excel Applicaiton, Workbook and Worksheet and Database object
Set objXL = CreateObject("Excel.Application")


'Open Excel
With objXL

Set wbk = .Workbooks.Add
Set wks = wbk.Worksheets(1)
wks.NAME = "Summary"

With wks
On Error Resume Next
.Range("A2").CopyFromRecordset rst

.Range("A1:I1").Value = Array("ColumnA", "ColumnB", "ColumnC", "ColumnD", "ColumnE", "ColumnF", "ColumnG", "ColumnH", "ColumnI")
.Range("A1:I1").Font.Bold = True

End With

.ActiveWorkbook.SaveAs strFullPath
.Visible = True

End With

End If



Routine1:
' Cleanup all objects (resume next on errors)
On Error Resume Next
Set wks = Nothing
Set wbk = Nothing
Set objXL = Nothing
rst.Close
Set rst = Nothing
DoCmd.Hourglass False
Exit Sub

Routine2:
MsgBox err.Number & " - " & err.Description, vbCritical, "basUtils.XLS_Export"
DoCmd.Hourglass False
Resume Routine1

End Sub

 

[vbajock]

You are making the object visible, so I assume you want Exel up and running for the user when your program finishes, but doesn't the call to set objXL=Nothing end the Exel process on the machine? Maybe it's popping a dialog somewhere that you can't see asking the user to save or something. I would try just exiting the sub without any of those calls to set those objects to nothing. Then, I would close the Excel object on the screen manually, and then check Task Manager to make sure that isn't leaving any unwanted Excel processes still open.

 

[kjv1611]

but doesn't the call to set objXL=Nothing end the Exel process on the machine?

No, it does not end the instance. It does end the control from the database application, though. So when you set that object to nothing, Excel is still running... in the background, and the only way you'll see it, most likely, is through Windows Task Manager.

But I've not looked at the specific problem of the post yet..

"But thanks be to God, which giveth us the victory through our Lord Jesus Christ." 1 Corinthians 15:57

 

[kjv1611]

On your Excel object, if you're going to kill it, you need to also close the application, b/c it is running in the background. So you need to [red]add[/red]:

Routine1:
' Cleanup all objects (resume next on errors)
On Error Resume Next
Set wks = Nothing
Set wbk = Nothing
objXL.Quit
[green]'Set objXL = Nothing and no longer need this[/green]
rst.Close
Set rst = Nothing
DoCmd.Hourglass False
Exit Sub

Well, really, you just replace one piece of it.

But I do wonder whether that is the problem of reference here. I would expect you'd still be able to open the form again, even if you left 15 Excel applications floating in memory.

"But thanks be to God, which giveth us the victory through our Lord Jesus Christ." 1 Corinthians 15:57

 

[kjv1611]

This may be obvious to you, but why do you store a date field as a variant? Seems that would use unnecessary resources, though in reality it may be very small.

[highlight]Dim varDt As Variant[/highlight] [green]'why not use datetime data type here?[/green]

DoCmd.Hourglass True

varDt = Format(Date, "yyyymmdd")

"But thanks be to God, which giveth us the victory through our Lord Jesus Christ." 1 Corinthians 15:57

 

[kjv1611]

Here is what you say the problem is or problems are:

After running the code and creating the Excel file and then closing the form that calls the code, I am no longer able to re-open the form. Some of the code that is used to load defaults on the form is no longer recognized.

So first off... either the form does NOT load, or it does load. You say that you're no longer able to open the form... then you say it cannot pull in the defaults. Are you saying the code that is pulling the defaults is not allowing the form to open?

"But thanks be to God, which giveth us the victory through our Lord Jesus Christ." 1 Corinthians 15:57

 

[kjv1611]

Really, I think for your main problem, we're going to have to look at the code under Form_Load and/or Form_Current to see what's going on there to compare.

"But thanks be to God, which giveth us the victory through our Lord Jesus Christ." 1 Corinthians 15:57

 

[zionman4]

Hi kjv1611,
Good point. Let me explain, I have a main form and a subform. The forms actually begin to load but when it gets to the code that formats fields on the subform, the code hangs. This code is on the subform. The subform is displayed as a datasheet.

Here is the piece of code that it is hanging on:

Dim fcd As FormatCondition
Dim I As Integer

For I = 0 To Me.Controls.Count - 1
With Me.Controls(I).FormatConditions <----- Here it is where the error occurs
Set fcd = .Add(acExpression, acEqual, "[nCount] > 1")
fcd.BackColor = RGB(232, 232, 232)
fcd.Enabled = True
End With
Next I

I get Runtime error 438
Object doesn't support this property or method.

For the people that have not run the export to Excel code, there is no issues...

Thanks!

 

[zionman4]

By the way, I also noticed that the code fails on odd numbers for (I) not on even numbers...

The part that has me puzzle is that this happened after running the Excel export code.

 

[kjv1611]

Alright, you say the subform is showing in datasheet view. Just to make sure, you do mean "data sheet view", and not "Continuous" which in some cases can look rather close.

If that is the case, then I think you may not be able to do the formatting code the way you want.

Instead, you may want to look at using some other methods:
1. For forcing a certain format, look at the table, and set the default format there.
2. Or it may fit best to just use an Input Mask and/or Validation Rule - you just need to figure out what you need there.
3. As for formatting the cells with a certain color based on the values, use Conditional formatting rather than vb code.

My initial guess is that when you have it in datasheet view (which I practically never use), Access cannot control the fields the same as it can in a normal "Single" or "Continuous" format, b/c in those views, it sees the fields as objects. I don't think that carries across to DataSheet view.

Or if none of that works, and you need to do what you're doing - not just a "want to" - then try creating another copy as a continuous form instead of datasheet view, and see if you can do what you need that way.



"But thanks be to God, which giveth us the victory through our Lord Jesus Christ." 1 Corinthians 15:57

 

[zionman4]

But why is the code working w/o issues on those computers that have not run the export to excel code above? So I know the code works for formatting the fields. It almost looks like the code did something to the app....just don't know what.

 

[kjv1611]

My first guess right now is that it has something to do with the way you're referring to the current database for the export.

If it were me, I'd probably look at just doing this for an export anyway:

DoCmd.TransferSpreadsheet acExport,,,"MyTableName","WorksheetFullPath"

# of commas may not be correct... probably wrong, actually, as I just typed w/o looking. I just think I remember leaving a couple variables empty in that line usually.

Any reason you cannot do it that way?

You could always still create the Excel.Application, and work with the new workbook if needed.

It's possible I've overlooked a good reason for doing it the way you're currently do it, but I just didn't see it - at least today.

"But thanks be to God, which giveth us the victory through our Lord Jesus Christ." 1 Corinthians 15:57

 

[zionman4]

I actually tried to to it but the data is coming from a stored proc. This is an .adp (Access project) but i am not sure how to send the string to the sp for it to run using DoCmd.TransferSpreadsheet.

As I was stepping through the code today, I noticed that the culprit of the issue is that the "On Error Resume Next" is not working. Would you know why or what would the above code cause to prevent that code from working anymore?

 

[zionman4]

I found the issue!!!!

' Set to Break on all Errors
Application.SetOption "Error Trapping", 0

This code prevents the error from continuing on and forces a break. I removed the code and reset the option to Break on Unhandled Errors under Tools - Options - General.

Thanks for your input as I made some changes you suggested to the code to make it cleaner!

Phil 4:13...

 

[kjv1611]

Glad you got it sorted out. Even though the On Error Resume Next is generally frowned upon, I never even thought of looking at it, b/c of it's location in the code.

"But thanks be to God, which giveth us the victory through our Lord Jesus Christ." 1 Corinthians 15:57