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

Kamis, 29 Januari 2015

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