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

Rabu, 28 Januari 2015

Re: [MS_AccessPros] Error controlling Excel from Access

Hi Adam,

thanks for your comments ~ in addition to the BAS download on RogersAccessLibrary, I have updated the post to include the code ;) ~

http://www.rogersaccesslibrary.com/forum/document-calculated-fields-in-queries_topic619.html



				Document Calculated Fields in Queries Document Calculated Fields in QueriesDo you have calculated fields in queries of your database? To see...

				View on www.rogersaccesslib...	Preview by Yahoo

I did the most common things here that one would do when automating with Excel. Late Binding is used so I also commented the values of the constants to be better understood

Notice that FreezePanes and AutoFilter are done outside the WITH block ... B2 on the sheet is selected in the WITH block -- always like to select the cell I want others to see when they open the workbook. WITH uses the first sheet:

With xlWb.Worksheets.Item(1) 

which does NOT have to be the active one for anything in the WITH block ... so it could point to another sheet :)

Warm Regards,

Crystal

~ have an awesome day ~

On Wednesday, January 28, 2015 3:48 PM, "Crystal strive4peace2008@yahoo.com [MS_Access_Professionals]" <MS_Access_Professionals@yahoogroups.com> wrote:

Hi Adam,

John is right -- you need to reference the desired sheet, not the Excel application.

The Excel macro recorder does not reference ActiveSheet since that is the default. However, when you write automation code from Access, you DO need to reference the sheet. Also, keep in mind, you can reference ANY sheet, not just the active one :) It is better NOT to use SELECTION and most times not necessary either. Exceptions include Freeze and Zoom.

also, .Application would not be right anyway ... the prefaced . means that code is between WITH <object> and End With. There is nothing higher than application.

look at the automation code for Excel from Access posted in the download here:

Document Calculated Fields in Queries
http://www.rogersaccesslibrary.com/forum/document-calculated-fields-in-queries_topic619.html

Warm Regards,

Crystal

~ have an awesome day ~

On Wednesday, January 28, 2015 2:58 PM, "John Viescas JohnV@msn.com [MS_Access_Professionals]" <MS_Access_Professionals@yahoogroups.com> wrote:

Adam-

Why did you add the .Application? If it works in Excel, try it in Access without that.

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

http://www.viescas.com/

(Paris, France)

On Jan 28, 2015, at 10:31 PM, runuphillracing@yahoo.com [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com> wrote:

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