ACCESS FEED: Re: [MS_AccessPros] Error controlling Excel from Access

Kamis, 29 Januari 2015

Re: [MS_AccessPros] Error controlling Excel from Access

Hello,

Instead of:

Set xlSheet = xlApp.Sheets(1)
xlSheet.Sheets("qryDetail").Name = "Detail"

Try:

Set xlSheet = xlApp.Sheets("qryDetail")

xlSheet.Name = "Detail"

I prefer to set references to sheets using the sheet's name instead of it's number but both should work. Your real problem was the second line. xlSheet is a sheet object so it doesn't have a sheet collection of it's own. You might have meant to use xlApp.Sheets("qryDetail").Name = "Detail" which would have worked.

Hope that helps.

Cheers,

Jeff

From: "runuphillracing@yahoo.com [MS_Access_Professionals]" <MS_Access_Professionals@yahoogroups.com>
To: MS_Access_Professionals@yahoogroups.com
Sent: Thursday, January 29, 2015 9:23 AM
Subject: RE: [MS_AccessPros] Error controlling Excel from Access

Here's the whole code, latest version. It's not working.
I get RT 438 at     xlSheet.Sheets("qryDetail").Name = "Detail"
If I rem it out, I get RT 1004 on the next line.
etc.

Dim stQryName, stFileName As String
Dim xlApp, xlSheet As Object
Dim SumRow As Long
stFileName = "PathName\" & Me.ClientAbbrev & Me.YYMM

    stQryName = "qryDetail"
    DoCmd.TransferSpreadsheet acExport, , stQryName, stFileName
    stQryName = "qrySummary"
    DoCmd.TransferSpreadsheet acExport, , stQryName, stFileName

    Set xlApp = CreateObject("Excel.Application")
    Set xlSheet = xlApp.Workbooks.Open(stFileName & ".xlsx").Sheets(1)

' FormatDetail
    Set xlSheet = xlApp.Sheets(1)
    xlSheet.Sheets("qryDetail").Name = "Detail"

    SumRow = xlSheet.cells(1, 1).end(xlDown).row + 1

    'Add totals to bottom of detail and format
    xlSheet.cells(SumRow, 3).FormulaR1C1 = "=COUNT(R2C:R[-1]C)"
    xlSheet.cells(SumRow, 3).NumberFormat = "#,##0"
    xlSheet.cells(SumRow, 3).Bold = True
    xlSheet.cells(SumRow, 14).FormulaR1C1 = "=SUM(R2C:R[-1]C)"
    xlSheet.cells(SumRow, 14).Font.Bold = True

    'Format columns
    xlSheet.Columns("C").NumberFormat = "000000000"
    xlSheet.Columns("G:H").HorizontalAlignment = xlCenter
    xlSheet.Columns("M").NumberFormat = "0"
    xlSheet.Columns("N").NumberFormat = "$#,##0.00"

    'Unwrap and auto fit col width
    xlSheet.cells.Select
    xlSheet.selection.WrapText = False
    xlSheet.cells.EntireColumn.AutoFit
    xlSheet.range("A2").Select
    xlSheet.ActiveWindow.FreezePanes = True


'Format Summary
    xlSheet.Sheets("qrySummary").Name = "Summary"
    Set xlSheet = xlDoc.Sheets("Summary")

    SumRow = xlSheet.range(1, 1).end(xlDown).row + 1

    'Add totals to bottom of Summary
    xlSheet.range(SumRow, 6 & ":" & SumRow, 11).FormulaR1C1 = "=SUM(R2C:R[-1]C)"
    xlSheet.selection.Font.Bold = True

    'Format columns
    xlSheet.Columns("F:F,H:H,J:J").NumberFormat = "#,##0"
    xlSheet.Columns("G:G,I:I,K:K").NumberFormat = "$#,##0.00"
    xlSheet.Columns("G:H").HorizontalAlignment = xlCenter

    'Unwrap and auto fit col width
    xlSheet.cells.Select
    xlSheet.selection.WrapText = False
    xlSheet.cells.EntireColumn.AutoFit

    xlSheet.range("A2").Select
    xlSheet.ActiveWindow.FreezePanes = True

    xlSheet.ActiveWorkbook.Save
    xlSheet.ActiveWorkbook.Close

    Application.FollowHyperlink stFileName & ".xlsx"