https://msdn.microsoft.com/en-us/library/microsoft.office.interop.excel.constants.aspx
Darrell
From: MS_Access_Professionals@yahoogroups.com [mailto:MS_Access_Professionals@yahoogroups.com]
Sent: Wednesday, August 12, 2015 12:39 PM
To: MS_Access_Professionals@yahoogroups.com
Subject: RE: [MS_AccessPros] Late Binding to Excel
That's what I don't know how to do. Please tell me how to look it up.
Doyce
---In MS_Access_Professionals@yahoogroups.com, <DEmbrey@...> wrote :
You have to look it up. Guess where, in the Excel library.
Darrell
From: MS_Access_Professionals@yahoogroups.com [mailto:MS_Access_Professionals@yahoogroups.com]
Sent: Wednesday, August 12, 2015 12:36 PM
To: MS_Access_Professionals@yahoogroups.com
Subject: RE: [MS_AccessPros] Late Binding to Excel
Darrell,
I read about defining constants but I couldn't figure out how to find the value for the constants. How did you know the value for xlToLeft is -4159?
Doyce
---In MS_Access_Professionals@yahoogroups.com, <DEmbrey@...> wrote :
Doyce,
xlToLeft is a constant in the Excel library. Since you are using late binding, you either have to define the constant or use the actual value.
'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
Also, Selection is a member of the Application object. You need to use your application object variable xl in front of Selection.
Darrell
From: MS_Access_Professionals@yahoogroups.com [mailto:MS_Access_Professionals@yahoogroups.com]
Sent: Wednesday, August 12, 2015 12:21 PM
To: MS_Access_Professionals@yahoogroups.com
Subject: RE: [MS_AccessPros] Late Binding to Excel
I certainly appreciate the help. I'm trying to learn how to use late binding. I've gone through and added my periods as instructed. Not it is stopping on the line that says ".Selection.Delete Shift:=xlToLeft" with xlToLeft highlighted and the error says variable not found.
Doyce
---In MS_Access_Professionals@yahoogroups.com, <bob@...> wrote :
He's not using the variable as Darrell suggested but he is defining it.
Instead of
'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 (etc.)
Do this
'Define the sheet in the Workbook as XlSheet
Set xlSheet1 = xlbook.Worksheets(1)
'Then have some fun!
With xlSheet1
.Range("A1:J1").Select (etc.)
Bob Peterson
From: MS_Access_Professionals@yahoogroups.com [mailto:MS_Access_Professionals@yahoogroups.com]
Sent: Tuesday, August 11, 2015 8:50 AM
To: MS_Access_Professionals@yahoogroups.com
Subject: Re: [MS_AccessPros] Late Binding to Excel
Same problem. If Columns is a collection of a Worksheet (I assume it is), then you need to say .Columns. The whole point of using With is to avoid having to say:
xlbook.Worsheets(1).Columns("A:A").Select
But to clue VBA that you want the item referenced to be used with the WITH, you have to start with the dot or bang (!) as appropriate.
You'll probably have the same problem with Selection and Range.
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:18 PM, winberry.doyce@... [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com> wrote:
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.
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: John Viescas <johnv@msn.com>
| Reply via web post | • | Reply to sender | • | Reply to group | • | Start a New Topic | • | Messages in this topic (14) |
Tidak ada komentar:
Posting Komentar