Results 1 to 11 of 11
  1. #1
    5 Star Lounger
    Join Date
    May 2001
    Location
    Patterson Lakes, Melbourne, Victoria, Australia
    Posts
    637
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Calling Excel from Access (2003 SP2)

    I am manipulating an Excel spreadsheet from within Access, but the following line produces an error.
    with objExcel
    ..various Excel manipulations that work OK, then the error causing line:-
    .Range(Cells(1, 1), Cells(Cntr, 1)).Font.Bold = True
    End with
    I have tried everything but can't stop the error

    30 miunutes later I found the solution
    Set ThisSheet = .ActiveWorkbook.ActiveSheet
    ThisSheet.Range(ThisSheet.Cells(1, 1), ThisSheet.Cells(1, Cntr)).Select
    Unless someone knows a more efficient way, no help needed.
    Thanks anyway
    Regards,
    Peter

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Calling Excel from Access (2003 SP2)

    The two occurrences of Cells don't refer to anything. You could have used
    <code>
    .Range(.Cells(1, 1), .Cells(Cntr, 1)).Font.Bold = True
    </code>
    Note the use of <code>.Cells</code> instead of <code>Cells</code>.

    Alternatively, you could have used
    <code>
    .Range("A1:A" & Cntr).Font.Bold = True</code>

  3. #3
    5 Star Lounger
    Join Date
    May 2001
    Location
    Patterson Lakes, Melbourne, Victoria, Australia
    Posts
    637
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Calling Excel from Access (2003 SP2)

    Far more polished way of doing it , thanks hans.

    Just found another area that is playing up. The line
    objExcel.Workbooks("BKzSchedule.xls").Close SaveChanges:=acSaveNo
    gives the following error - but not always.
    XYZ.xls is a Microsoft Excel 5.0/95 Workbook. Do you want to overwrite it with the latest Excel format?
    Any ideas what could be causing that?
    Regards,
    Peter

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Calling Excel from Access (2003 SP2)

    I assume that BKzSchedule = XYZ? If not, I don't understand where the message comes from.

    How did you create the Excel workbook?

  5. #5
    5 Star Lounger
    Join Date
    May 2001
    Location
    Patterson Lakes, Melbourne, Victoria, Australia
    Posts
    637
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Calling Excel from Access (2003 SP2)

    Here's the code:

    <hr>Public objExcel As Excel.Application

    OutputFl = "C:AccessFldrOldCopiesBKzSchedule.xls"
    If ExcelOpen() Then
    objExcel.Workbooks("BKzSchedule.xls").Close SaveChanges:=acSaveNo
    Kill OutputFl
    Else
    MsgBox "Can't open Excel. Have to abort."
    GoTo Exit_MeetHelpersListing
    End If


    Function ExcelOpen() As Boolean
    Const Err_AppNotRunning As Long = 429
    On Error Resume Next
    ExcelOpen = True
    Set objExcel = GetObject(, "Excel.Application")
    If Err = Err_AppNotRunning Then
    Set objExcel = New Excel.Application
    ElseIf Err <> 0 Then
    ExcelOpen = False
    End If
    End Function
    <hr>
    Regards,
    Peter

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Calling Excel from Access (2003 SP2)

    I asked how the Excel workbook is created.

  7. #7
    5 Star Lounger
    Join Date
    May 2001
    Location
    Patterson Lakes, Melbourne, Victoria, Australia
    Posts
    637
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Calling Excel from Access (2003 SP2)

    Whoop sorry.
    The code that creates the Workbook is just below the code quoted above. Here it tis:
    DoCmd.OutputTo acOutputReport, "rptBKzPlanning", acFormatXLS, OutputFl
    The Workbook that causes the error is this same Workbook that hasn't been closed when the code is run again with different data.
    Regards,
    Peter

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Calling Excel from Access (2003 SP2)

    The OutputTo method creates an Excel 5.0/95 workbook. When you open, then save it, Excel will ask whether you want to save it as a standard Excel workbook (i.e. Excel 97-2003 format). But it shouldn't ask that if you close it without saving. However, you can suppress the prompt as follows:

    objExcel.DisplayAlerts = False
    objExcel.Workbooks("BKzSchedule.xls").Close SaveChanges:=acSaveNo
    objExcel.DisplayAlerts = True

  9. #9
    5 Star Lounger
    Join Date
    May 2001
    Location
    Patterson Lakes, Melbourne, Victoria, Australia
    Posts
    637
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Calling Excel from Access (2003 SP2)

    Yes that works. Thanks for that Hans.
    I can't see why my Excel 2003 SP2 is saving the Workbook as an Excel 5.0/95 workbook. There appears nothing in Tool | Options to set that could cause that.
    Regards,
    Peter

  10. #10
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Calling Excel from Access (2003 SP2)

    > There appears nothing in Tool | Options to set that could cause that.

    No, there isn't. It's just programmed that way. The TransferSpreadsheet has an argument that lets you specify the format, but that can only be used for tables and queries, not for reports. I don't know about Access 2007, but Microsoft didn't update OutputTo between Access 95, Access 97, Access 2000, Access 2002 and Access 2003 <img src=/S/sad.gif border=0 alt=sad width=15 height=15>

  11. #11
    5 Star Lounger
    Join Date
    May 2001
    Location
    Patterson Lakes, Melbourne, Victoria, Australia
    Posts
    637
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Calling Excel from Access (2003 SP2)

    <P ID="nt"><font size=-1>(No Text)</font>
    Regards,
    Peter

Posting Permissions

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