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"



__._,_.___

Posted by: Jeff Holm <jholm35@yahoo.com>
Reply via web post Reply to sender Reply to group Start a New Topic Messages in this topic (12)

.

__,_._,___

Tidak ada komentar:

Posting Komentar