Problem with Exported Excel file

[Cire via AccessMonster.com]

Hi all, i've got a problem with the excel file which is created on a click of
a "export to excel" button. Apparently when i double click on it, it doesnt
launch but if i open it within excel i get a "file is Locked for editing"
msg and the options for Read only and Notify. but this is not what i want..i
used the copy from recordset method to export the query data to excel. the
query is a pass-through query, which i understand is read-only but i remember
being able to export and open the file without the "locked for editng"
message. so i'm kind of confused now..heres my code of the export to excel
button

Public mainqdf As DAO.QueryDef
Public maindb As DAO.Database
Public mainRst As DAO.Recordset

Private Sub cmdExpToExcel_Click()

'code behind command button "Export to Excel"
Dim lngMax As Long
Dim lngCount As Long
Dim xlApp As Object
Dim xlBook As Object
Dim xlSheet As Object
Dim strFile As String

Set maindb = CurrentDb()
Set mainqdf = maindb.QueryDefs("qryCOSearch")
Set mainRst = mainqdf.OpenRecordset(dbOpenDynaset, dbEdit)
'all code below explains exporting the query results to excel
'allow user to choose path to save to
strFile = GetSaveFile_CLT("C:\", "Save this file as", "Untitled.
xls")
If Not strFile Like "*.xls" Then
MsgBox "you have saved in the wrong file format"
Exit Sub
End If
'defining the variables
Set xlApp = CreateObject("Excel.Application")
Set xlBook = xlApp.Workbooks.Add
Set xlSheet = xlBook.Worksheets.Add
'formatting cells in excel
With xlSheet
For Each Cell In xlSheet.Range("A1", "J1")
Cell.Font.Size = 10
Cell.Font.Name = "Arial"
Cell.Font.Bold = True
Cell.Interior.Color = rgb(204, 255, 255)
Cell.HorizontalAlignment = xlHAlignCenter
Cell.WrapText = True
Next
.Cells(1, 2).HorizontalAlignment = xlHAlignLeft
.Columns("A").ColumnWidth = 10
.Columns("B").ColumnWidth = 24
.Columns("C").ColumnWidth = 16
.Columns("C").HorizontalAlignment = xlHAlignLeft
.Columns("E").ColumnWidth = 30
.Columns("F").ColumnWidth = 20
.Columns("G").ColumnWidth = 8
.Columns("H").ColumnWidth = 32
.Columns("I:J").ColumnWidth = 24
.Rows(1).RowHeight = 16
End With

'copying the query results from the recordset to the
excel file
With xlSheet
.Name = "Results"
.UsedRange.ClearContents
lngMax = mainRst.Fields.Count
For lngCount = lngMax To 1 Step -1
.Cells(1, lngCount).Value = mainRst.Fields(lngCount -
1).Name
Next lngCount
.Range("A2").CopyFromRecordset mainRst
End With
lngMax = xlBook.Worksheets.Count
'deleting all other worksheets except for "Results"
For lngCount = lngMax To 1 Step -1
If xlBook.Worksheets(lngCount).Name <> "Results" Then
xlBook.Worksheets(lngCount).Delete
End If
Next lngCount

xlBook.SaveAs strFile
MsgBox "Export Completed", vbInformation
End Sub

 

[Random]

Hi,

I might be wrong here, but I did have similar problems in the past. I
think the line 'Set xlApp = CreateObject("Excel.Application") ' will
create an object which is not visible and remains open (but not
visible) when the procedure ends. Therefore when you try and open this
file again it is already open (but not visible), hence the 'locked for
editing' message. I would try the following:
After the line xlBook.SaveAs strFile
add the folloiwng lines:
Set xlSheet = Nothing
Set xlBook = Nothing
Set xlApp = Nothing

This should release any references to the objects and hopefully cure
the problem.

Hope this helps
Cheers
Steve.

 

[JonWayn]

Or, at some point after the statement, 'Set xlApp =
CreateObject("Excel.Application")
', you could insert the statement, xlApp.Visible = TRUE

Furthermore, you said after double-clicking the button ..., try clicking the
button once next time - since your code is behind the click event

This is beyond the issue at hand, however, for efficiency purposes, the
lines :
For Each Cell In xlSheet.Range("A1", "J1")
Cell.Font.Size = 10
Cell.Font.Name = "Arial"
Cell.Font.Bold = True
Cell.Interior.Color = rgb(204, 255, 255)
Cell.HorizontalAlignment = xlHAlignCenter
Cell.WrapText = True
Next
could be replaced with :
With xlSheet.Range("A1:J1")
.Font.Size = 10
.Font.Name = "Arial"
.Font.Bold = True
'etc.
End With
That way, instead of looping 1 time for each cell in the range (10 times),
it is applied to entire range just once

 

[Cire via AccessMonster.com]

Or, at some point after the statement, 'Set xlApp =
CreateObject("Excel.Application")
', you could insert the statement, xlApp.Visible = TRUE

Furthermore, you said after double-clicking the button ..., try clicking the
button once next time - since your code is behind the click event

This is beyond the issue at hand, however, for efficiency purposes, the
lines :
For Each Cell In xlSheet.Range("A1", "J1")
Cell.Font.Size = 10
Cell.Font.Name = "Arial"
Cell.Font.Bold = True
Cell.Interior.Color = rgb(204, 255, 255)
Cell.HorizontalAlignment = xlHAlignCenter
Cell.WrapText = True
Next
could be replaced with :
With xlSheet.Range("A1:J1")
.Font.Size = 10
.Font.Name = "Arial"
.Font.Bold = True
'etc.
End With
That way, instead of looping 1 time for each cell in the range (10 times),
it is applied to entire range just once

Hi,

[quoted text clipped - 16 lines]

Cheers
Steve.

Jon tks for the advice but its running very quickly now so i don't see a
reason to change the code, if its not broke don't fix it!

Steve, ur advice! it worked although i had to make some changes, instead
of putting it after that line, i used Exit_Handler:

If Not xlSheet Is Nothing Then
Set xlSheet = Nothing
End If

If Not xlBook Is Nothing Then
Set xlBook = Nothing
End If

If Not xlApp Is Nothing Then
xlApp.Quit
Set xlApp = Nothing
End If

If Not mainRst Is Nothing Then
mainRst.Close
Set mainRst = Nothing
End If

If Not mainqdf Is Nothing Then
Set mainqdf = Nothing
End If

If Not maindb Is Nothing Then
Set maindb = Nothing
End If

Exit Sub

Err_Handler:
On Error Resume Next
MsgBox Err.Description, vbExclamation, "Error No: " & Err.Number
Resume Exit_Handler
Resume
End Sub

now it works great thanks for ur help.
and couple of other questions, i'm going to use .mdb for my end-users since
my application is a front-end to a ms-sql server so the mdb file is pretty
small. so is there any way to
1. get rid of the security warning at the start?
2. get rid of the access "X" but keep the minimise and maximise? because i've
already programmed a exit application with auto compact code in it so i
wouldnt want to user to exit thru "X"