Hello Friends,
I'm trying to switch to late binding for Excel in the following procedure in Access 2010. When I remove the Excel library from the references, the debugger stops at the line that says " With Worksheets(1)" and gives this error: Sub or Function Not defined. I think I need to declare Worksheets as a constant but I don't understand how to find it's value and how I should declare the constant.
Private Sub btnXLTransfer_Click()
' Send Revenue summary to excel
Dim xl As Object
Dim xlbook As Object
Dim xlSheet1 As Object
Dim strFileName As String
strFileName = "\\RSIPRFP001\Accounting\ACCNTING\Revenue_Summary_" & Format(Date, "mm_dd_yy") & ".xlsx"
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12Xml, "qryRevenueSummary", strFileName
If xl Is Nothing Then
Set xl = CreateObject("Excel.Application")
End If
Set xlbook = GetObject(strFileName)
'Make sure excel is visible on the screen ll
xl.Visible = True
xlbook.Windows(1).Visible = True
'xl.ActiveWindow.Zoom = 75
'Define the sheet in the Workbook as XlSheet
Set xlSheet1 = xlbook.Worksheets(1)
'Then have some fun!
With xlbook
With Worksheets(1)
.Range("A1:J1").Select
'start insert here
Columns("A:A").Select
Selection.Delete Shift:=xlToLeft
Range("A1").Select
Selection.Subtotal GroupBy:=8, Function:=xlSum, TotalList:=Array(7), _
Replace:=True, PageBreaks:=False, SummaryBelowData:=True
Range("A1:H1").Select
Selection.Font.Bold = True
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
With Selection.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
With Selection.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
With Selection.Borders(xlEdgeRight)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
With Selection.Borders(xlInsideVertical)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
With Selection.Borders(xlInsideHorizontal)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
Columns("I:I").Select
Selection.Delete Shift:=xlToLeft
Range("A1:H1").Select
With Selection.Interior
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.ThemeColor = xlThemeColorLight2
.TintAndShade = 0.799981688894314
.PatternTintAndShade = 0
End With
Columns("F:G").Select
Selection.NumberFormat = "$#,##0.00"
Columns("A:H").Select
Columns("A:H").EntireColumn.AutoFit
Rows("1:3").Select
Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
Range("A1:A2").Select
With Selection.Font
.NAME = "Calibri"
.Size = 16
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ThemeColor = xlThemeColorLight1
.TintAndShade = 0
.ThemeFont = xlThemeFontMinor
End With
Selection.Font.Bold = True
Range("A1").Select
ActiveCell.FormulaR1C1 = "Con-way Manufacturing"
Range("A2").Select
ActiveCell.FormulaR1C1 = "Trailer Production and Sales"
Range("A3").Select
'Close up
xlbook.Close savechanges:=True
xl.Application.Quit
Set xlSheet1 = Nothing: Set xlbook = Nothing: Set xl = Nothing
End With
End With
MsgBox "The Revenue Summary has been saved as " & strFileName, vbOKOnly, "File Saved"
End Sub
Thanks in advance for your help.
Doyce
Posted by: winberry.doyce@roadsysinc.com
| Reply via web post | • | Reply to sender | • | Reply to group | • | Start a New Topic | • | Messages in this topic (1) |
Tidak ada komentar:
Posting Komentar