Hi everyone,

I need to send the results of a query out to excel. However, a simple export does not meet my needs. So I wrote the following code to send the results of my query to excel and format the worksheet. What this code does is as follows

Creates Excel Application
Launces a New workbook based on an existing template
Run the Save dialog box to get a name for the book
Executes the query to get appropriate results
Loop until the EOF of the recordset and send line by line to Excel and does some formating (hence the line by line export)
Saves file

All of this works just dandy when I click the button on the form running this code.

I close down Excel and decide " hey lets try that again ... and bang ... I get Error 91, Object variable or With block variable not set" and the offending line is marked in the code ..... look for ****** in the code ...



++++++++++++++++++++++++++++++++++++++++++++
Sub CreateExecSumm()

Dim xlApp As Excel.Application
Dim xlWB As Excel.Workbook, xlSH As Excel.Worksheet
Dim strExecutiveSummary As String

Dim db As DAO.Database
Dim rsExecSumm As DAO.Recordset
Dim strSQL As String, strFilter As String, strFileName

Dim dtBookingDate
Dim i

strExecutiveSummary = strBookingFormPath & "ExeSumm.xlt"
Set xlApp = CreateObject("Excel.Application")
Set xlWB = xlApp.Workbooks.Add(strExecutiveSummary)
Set xlSH = xlWB.Worksheets(1)

strFilter = ahtAddFilterItem(strFilter, "Excel Files (*.XLS)", "*.XLS")
strFileName = ahtCommonFileOpenSave( _
Filter:=strFilter, OpenFile:=False, _
DialogTitle:="Please enter filename to save Executive summary.", _
Flags:=ahtOFN_HIDEREADONLY)

If strFileName = "" Then
Set xlApp = Nothing
Exit Sub
End If

xlSH.SaveAs strFileName

xlSH.Application.Visible = True

strSQL = "SELECT * FROM qryExportExecSummary ORDER BY booking_date"
'run recordset to get details
Set db = CurrentDb()
Set rsExecSumm = db.OpenRecordset(strSQL)

If rsExecSumm.RecordCount = 0 Then
MsgBox "No valid booking for the criteria selected"
Exit Sub
End If

rsExecSumm.MoveFirst
dtBookingDate = rsExecSumm(1)

With xlApp
.Goto Reference:="Start"
End With

************************************************** ******
With xlSH
ActiveCell.Formula = rsExecSumm(1) 'THE OFFENDING LINE
ActiveCell.Range("A1:G1").Interior.ColorIndex = 15
ActiveCell.Offset(1, 0).Select
End With

Do Until rsExecSumm.EOF
With xlSH
ActiveCell.Offset(0, 1).Formula = rsExecSumm(2)
ActiveCell.Offset(0, 2).Formula = rsExecSumm(3)
ActiveCell.Offset(0, 3).Formula = rsExecSumm(4)
ActiveCell.Offset(0, 4).Formula = rsExecSumm(5)
ActiveCell.Offset(0, 5).Formula = rsExecSumm(6)
ActiveCell.Offset(1, 0).Select
End With
rsExecSumm.MoveNext

If rsExecSumm.EOF = False Then
With xlSH
If dtBookingDate <> rsExecSumm(1) Then
ActiveCell.Formula = rsExecSumm(1)
ActiveCell.Range("A1:G1").Interior.ColorIndex = 15
ActiveCell.Offset(1, 0).Select
End If
End With
dtBookingDate = rsExecSumm(1)

End If
i = i + 1
Loop

With xlApp
.Goto Reference:="Start"
End With

With xlSH
ActiveCell.Range(Cells(2, 1), Cells(i + 3, 7)).Select
End With


xlWB.Save

End Sub
++++++++++++++++++++++++++++++++++++++++++++


I have tried Access 97/Excel 97, Access 2000/Excel2000,97 and Access XP/ExcelXP,97,2000 combinations on Win98, Win2K, winXP and WinNT.

They all result in the same error. Also when I close the workbook created, the entire Excel Application closes instead of just the workbook.

Any one have any suggestions ...... I would be very grateful to any help that anyone can throw my way.

Thanks in advanced ...

Don't know if it's gonna solve your problem, but it's generally good coding practice to clean up after you're done. That means you need to add:

Set xlWS = nothing
Set xlWB = nothing
Set xlApp = nothing

Do this for recordsets also:

Set rsExecSumm = nothing

Specify them in the opposite order in which you created these objects. Who knows, it might help. If not, your code is cleaner anyway.

Thanks for that. I did have them in the code ... there is more code before the end sub that does some amount of formatting and data manupulation and then the termination lines seeting all the objects to nothing.

They made absolutely no difference ...

If you have not found a solution or fix to your error problem yet, this is how I would do it. I would export data from a query to Excel with a macro, using the OutputTo function in the macro to put the data into a tempory file that will always have the same name and be in the same directory, with Auto Start set to no (which does not open up Excel at this point in the macro). The next step of the macro opens (Auto Start can be set to Yes here) an Excel workbook that has an AUTO_OPEN VBA macro that opens the temporary file, does all the formatting, and saves the file under a different name, if needed.

This technique works for me!
Jerry

Hi Thanks for that options. However, there cannot be any code in the excel workbook as this is being sent out to clients. It has to be formatted from Access, as the formatting is going to be based on the information in Access that will not be in the Excel workbook.

I did manage to solve it by using
xlSh.Range("A1"). format instead of using ActiveCell.

Cheers
Thanks again.

I had this prob. with VB also
ít is because excel is still running on background and you refer to worksheet 1

and if you run your code for the second time it will generate workbook2

what i've done is to realy close excel in the code

excell.quit

then if you re run your code it wil restart excell and generate workbook1 instead of wb2

For the record, there would be no VBA code in an Excel spreadsheet that is created new and formatted, and whatever else. The VBA code would be in the Excel file that does all the work, in its AUTO_OPEN procedure.

Congrats on solving your error problem.
Jerry

Are you suggesting a separate Excel file from the one the data is being transfered into?

The technuque I use is to export Access data to a new Excel worksheet (which will never have VBA code). Then from the same Access macro, I open an Excel template that I have prepared with VBA code in an AUTO_OPEN procecure, which knows from your coding, the name and location of the Excel file with the data. The Excel VBA code opens the Excel file just exported from Access, does the formatting, etc. and saves the file.

Your new Excel file to be distributed has only data, while your Excel template file with AUTO_OPEN does all the work. That's 2 Excel files to get the job done.

Jerry

Unfortunately, the export of data is not straight rows. The actual code to format it once the data is in Excel, if far slower, as the template would have to read line one in Excel compare with a table in Access, move information to the appropriate loaction on sheet .... it is the comparison in the database that determines the format of the sheet, hence the speed degradation using Excel to run throught the tables for each row is way too much for my client to wear.

However I did also find a patch on the MS site that address the Object issue that I raised.

Thanks heaps for your input.

It's not just that this needs to be

is it?

I tried both options. Both worked the first time and then not again until the db was closed and reopened.

I found an article outlining unsuccessful close of the Excel Workbook uisng VBA in Access/Word/PowerPoint.

I am currently going through the article to find out why the code runs only once and then falls overs subsequent times.

Thanks for your input.

I am using the OutputToExcel macro, but can't figure out how to select the folder in which I want to store the data, it is currently being sent to d:\data, but I want to store it in a different folder.

Type in the destination drive, directory ("folder") and filename when using OutputTo in a macro, in the box labeled "Output File". For example, when positioned at the OutputTo item in macro design mode, type in: c:\temp\myfile.xls
if you want to create a new Excel file named myfile.xls in c:\temp. Be sure the directory you choose exists on your destination drive.

Jerry