Results 1 to 14 of 14

Thread: Access to Excel

  1. #1
    Join Date
    Sep 2002
    Location
    Land of OZ
    Posts
    173

    Angry Unanswered: Access to Excel

    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 ...
    Mona
    ________________________________
    Life is too short to be sane or sensible. Weird people rule and normal people suck

  2. #2
    Join Date
    Jul 2003
    Location
    Netherlands
    Posts
    29
    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.
    Bye,

    Jeroen

    A 3D editor project
    www.delgine.com

  3. #3
    Join Date
    Sep 2002
    Location
    Land of OZ
    Posts
    173
    Originally posted by JeroenNL
    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 ...
    Mona
    ________________________________
    Life is too short to be sane or sensible. Weird people rule and normal people suck

  4. #4
    Join Date
    Jan 2002
    Location
    Bay Area
    Posts
    511
    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

  5. #5
    Join Date
    Sep 2002
    Location
    Land of OZ
    Posts
    173
    Originally posted by JerryDal
    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.
    Mona
    ________________________________
    Life is too short to be sane or sensible. Weird people rule and normal people suck

  6. #6
    Join Date
    Jul 2003
    Location
    Amsterdam, Nederland
    Posts
    450

    Exclamation

    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

  7. #7
    Join Date
    Jan 2002
    Location
    Bay Area
    Posts
    511
    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

  8. #8
    Join Date
    Sep 2002
    Location
    Land of OZ
    Posts
    173
    Originally posted by JerryDal
    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?
    Mona
    ________________________________
    Life is too short to be sane or sensible. Weird people rule and normal people suck

  9. #9
    Join Date
    Jan 2002
    Location
    Bay Area
    Posts
    511
    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

  10. #10
    Join Date
    Sep 2002
    Location
    Land of OZ
    Posts
    173
    Originally posted by JerryDal
    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.
    Mona
    ________________________________
    Life is too short to be sane or sensible. Weird people rule and normal people suck

  11. #11
    Join Date
    May 2002
    Location
    London
    Posts
    87
    Code:
    With xlSH
    ActiveCell.Formula = rsExecSumm(1) 'THE OFFENDING LINE
    ActiveCell.Range("A1:G1").Interior.ColorIndex = 15
    ActiveCell.Offset(1, 0).Select
    End With
    It's not just that this needs to be

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

  12. #12
    Join Date
    Sep 2002
    Location
    Land of OZ
    Posts
    173
    Originally posted by Risky
    Code:
    With xlSH
    ActiveCell.Formula = rsExecSumm(1) 'THE OFFENDING LINE
    ActiveCell.Range("A1:G1").Interior.ColorIndex = 15
    ActiveCell.Offset(1, 0).Select
    End With
    It's not just that this needs to be

    Code:
    With xlSH
    .ActiveCell.Formula = rsExecSumm(1) 'THE OFFENDING LINE
    .ActiveCell.Range("A1:G1").Interior.ColorIndex = 15
    .ActiveCell.Offset(1, 0).Select
    End With
    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.
    Mona
    ________________________________
    Life is too short to be sane or sensible. Weird people rule and normal people suck

  13. #13
    Join Date
    Sep 2003
    Posts
    1

    Folder Location

    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.



    Originally posted by JerryDal
    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

  14. #14
    Join Date
    Jan 2002
    Location
    Bay Area
    Posts
    511

    Thumbs up

    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •