Or do:
The Worksheets collection is a member of the Workbook object. You've set your xlSheet1 object to the object xlbook.Worksheets(1). Your code should look similar to the following:
'Define the sheet in the Workbook as XlSheet
Set xlSheet1 = xlbook.Worksheets(1)
'Then have some fun!
With xlbook
With xlSheet1
.Range("A1:J1").Select
Darrell
From: MS_Access_Professionals@yahoogroups.com [mailto:MS_Access_Professionals@yahoogroups.com]
Sent: Monday, August 10, 2015 5:17 PM
To: MS_Access_Professionals@yahoogroups.com
Subject: [MS_AccessPros] Late Binding to Excel
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"
DoC md.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
&nb sp; '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
&n bsp; 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
&n bsp; .Weight = xlThin
End With
With Selection.Borders(xlEdgeRight)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
With Selection.Borders(xlInsideVertical)
.LineStyle = xlContin uous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
With Selection.Borders(xlInsideHorizontal)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
&nb sp; 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
&n bsp; 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&qu ot;
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
The information contained in this communication is highly confidential and is intended solely for the use of the individual(s) to whom this communication is directed. If you are not the intended recipient, you are hereby notified that any viewing, copying, disclosure or distribution of this information is prohibited. Please notify the sender, by electronic mail or telephone, of any unintended receipt and delete the original message without making any copies.
Blue Cross Blue Shield of Michigan and Blue Care Network of Michigan are nonprofit corporations and independent licensees of the Blue Cross and Blue Shield Association.
Posted by: John Viescas <johnv@msn.com>
| Reply via web post | • | Reply to sender | • | Reply to group | • | Start a New Topic | • | Messages in this topic (3) |
Tidak ada komentar:
Posting Komentar