ACCESS FEED: RE: [MS_AccessPros] Error controlling Excel from Access

Rabu, 28 Januari 2015

RE: [MS_AccessPros] Error controlling Excel from Access

Hi Phil

Remember Selection is usually a user-initiated process. I prefer to avoid using the Selection object when writing automation code (the same goes for any object starting with "Active"). Note that the Excel Macro Recorder always uses Selection and Active* because it is recording what is happening in the user's context.

When you open a workbook or create a worksheet, always create a reference to that object - for example:

#If EarlyBinding Then
Debug.Assert xlCenter = -4108
Dim oxlApp As Excel.Application
Dim oxlDoc As Excel.Workbook
Dim oXLSheet As Excel.WorkSheet
#Else
Const xlCenter = -4108
Dim objXLApp As Object
Dim objXLBook As Object
#End If
Dim SumRow As Long
Set oxlApp = CreateObject("Excel.Application")
Set oxlDoc = oxlApp.Workbooks.OPEN("C:\FolderName\TestXL.xlsx"))
Set oXLSheet = oxlDoc.Sheets("Sheet1")
oXLSheet.Columns("G:H").HorizontalAlignment = xlCenter
SumRow = oXLSheet.UsedRange.End(xlDown).Row + 1

The last two lines are achieving what you are trying to do with Selection in your original post.

Best wishes,
Graham Mandeno [Access MVP 1996-2015 (not quite as long as John!)]

From: MS_Access_Professionals@yahoogroups.com [mailto:MS_Access_Professionals@yahoogroups.com]
Sent: Thursday, 29 January 2015 10:32
To: MS_Access_Professionals@yahoogroups.com
Subject: Re: [MS_AccessPros] Error controlling Excel from Access

John,

You mean you don't know all, see all? :)

The VB code runs properly in Excel. I created and tested it there, then just copied and pasted into Access, adding the .Application.

I have the MS Access Package Solution Wizard 2010 COM, ACCESSDE.dll, add in. I assume that includes the Excel library loaded because other lines work. It's just those few lines that didn't work that I included here.

I'll see if Crystal comes up with something. I have an off list convo going on with her. Us Coloradans have to stick together.

Thanks