You have to put the period for the object's property. Since you are using late binding, you don't have the Excel library loaded. Access knows nothing about the Excel properties and methods until you explicitly tie them to the Excel object.
'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
xl.Selection.Delete Shift:=xlToLeft '<<<<< you either need to define the constant xlToLeft or use the value -4159
.Range("A1").Select
xl.Selection.Subtotal GroupBy:=8, Function:=xlSum, TotalList:=Array(7), _
Replace:=True, PageBreaks:=False, SummaryBelowData:=True
.Range("A1:H1").Select
xl.Selection.Font.Bold = True
xl.Selection.Borders(xlDiagonalDown).LineStyle = xlNone
xl.Selection.Borders(xlDiagonalUp).LineStyle = xlNone
With xl.Selection.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
Darrell
From: MS_Access_Professionals@yahoogroups.com [mailto:MS_Access_Professionals@yahoogroups.com]
Sent: Tuesday, August 11, 2015 7:18 AM
To: MS_Access_Professionals@yahoogroups.com
Subject: Re: [MS_AccessPros] Late Binding to Excel
Good morning John and Darrel,
Thanks for your help. Both of your solutions get me past that error. Now it is hanging up on the line that begins with Columns ("A:A").Select
'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
Doyce
---In MS_Access_Professionals@yahoogroups.com, <JohnV@...> wrote :
Or do:
With .Worksheets(1)
John Viescas, Author
Microsoft Access 2010 Inside Out
Microsoft Access 2007 Inside Out
Microsoft Access 2003 Inside Out
Building Microsoft Access Applications
SQL Queries for Mere Mortals
(Paris, France)
On Aug 11, 2015, at 1:09 AM, 'Embrey, Darrell' DEmbrey@... [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com> wrote:
Hi Doyce,
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 IfSet 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:=Truexl.Application.Quit
Set xlSheet1 = Nothing: Set xlbook = Nothing: Set xl = Nothing
End With
End WithMsgBox "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.
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: "Embrey, Darrell" <DEmbrey@bcbsm.com>
| Reply via web post | • | Reply to sender | • | Reply to group | • | Start a New Topic | • | Messages in this topic (5) |
Tidak ada komentar:
Posting Komentar