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

Kamis, 29 Januari 2015

RE: [MS_AccessPros] Error controlling Excel from Access

Hi Phil

You are confusing WorkBooks and WorkSheets.

A Workbook is the Excel document or file (.xls or .xlsx) which you open on the Excel.Application. A single application can have many Workbooks open simultaneously, and therefore has a Workbooks collection. You add members to the collection with the Open method (open an existing document) or the Add method (creates a new document). Closing a workbook removes it from the collection. Each member of this collection is an Excel.Workbook object.

A Worksheet is a single spreadsheet within a Workbook, and is represented visually by a named tab in the user interface. A single Workbook can contain many Worksheets. and therefore has a Sheets collection. It is the Worksheet, not the Workbook or the Application, that contains Rows, Columns and Cells.

As I said, it is a good idea to maintain different object variables referencing the Application, Workbook, and Worksheet, respectively, that you are working on. It is possible to "skip" a level, but I would not recommend it as it easily becomes confusing.

You are skipping the Workbook level with this line:
Set xlSheet = xlApp.Workbooks.Open(stFileName & ".xlsx").Sheets(1)

I would prefer to say:
Set xlDoc = xlApp.Workbooks.Open(stFileName & ".xlsx")
Set xlSheet = xlDoc.Sheets(1)

… or better:
Set xlSheet = xlDoc.Sheets("qryDetail")

Later on, your confusion becomes apparent with these lines:
Set xlSheet = xlApp.Sheets(1)
xlSheet.Sheets("qryDetail").Name = "Detail"

xlApp is an Excel.Application, so although it has a Sheets collection, it should never be used, because it is just an indirect reference to Application.ActiveWorkbook.Sheets, which uses that nasty "Active" word! xlSheet is already an Excel.Worksheet, and so has no Sheets collection (remember the Sheets collection belongs to an Excel.Workbook). So both these lines are incorrect.

The first line is redundant, and the second line should simply be:
xlSheet.Name = "Detail"

Further on you have made extensive use of the Select method and the Selection object (and several Active* objects). Remember that you should try to avoid them in an automated environment because they are defined by the user interface***

*** The ONLY exception I can think of is ActiveWindow which in some cases is the only way to set properties of a Worksheet, such as FreezePanes and the Display* properties. As such, your code here is correct:
xlSheet.Range("A2").Select
xlSheet.ActiveWindow.FreezePanes = True

The Selection object usually returns a Range object, and so it is often useful to declare a Range object to replace Selection, and also for general worksheet manipulation. For example, instead of:
xlSheet.cells.Select
xlSheet.selection.WrapText = False
xlSheet.cells.EntireColumn.AutoFit

use this…
Dim xlRng as Excel.Range ' with the other declarations at the top
...
Set xlRng = xlSheet.Cells
xlRng.WrapText = False
xlRng.EntireColumn.AutoFit

These two lines are going to cause you trouble:
xlSheet.range(SumRow, 6 & ":" & SumRow, 11).FormulaR1C1 = "=SUM(R2C:R[-1]C)"
xlSheet.selection.Font.Bold = True

The Range method takes two arguments, not three. Those arguments are typically the top left and bottom right cells in the Range you want to reference (or a single string describing that range). The two cells defining your Range here are xlSheet.Cell(SumRow, 6) and xlSheet.Cell(SumRow, 11). As before, I suggest you define a Range object variable for the two operations on the same range (setting the formula and bolding the font):
Set xlRng = xlSheet.Range(xlSheet.Cell(SumRow, 6), xlSheet.Cell(SumRow, 11))
xlRng.FormulaR1C1 = "=SUM(R2C:R[-1]C)"
xlRng.Font.Bold = True

There are many more recommendations I could give, but just lastly, a couple of things:

1. Your xlDoc variable already refers to the open worksheet. so instead of (for example) xlSheet.ActiveWorkbook.Save, use xlDoc.Save. (Besides, I don't think the Worksheet object even has an ActiveWorkbook property!)

2. Instead of closing the workbook and then reopening it with FollowHyperlink, just make the application visible and under user control and leave the workbook open:
xlApp.UserControl = True
xlApp.Visible = True
Set xlApp=Nothing

3. I strongly recommend that when developing you use "early binding". To do this, you first set a reference (Tools > References) to the "Microsoft Excel xx.0 Object Library" (where xx is your version), and then declare all variables as their explicit object types (for example, Dim xlSheet As Excel.Worksheet, not Dim xlSheet As Object). This will give you all the advantages of intellisense and syntax checking as you are developing. If you need to distribute your application to other computers/Office versions later, then you can switch to "late binding" then. See my post yesterday for an example of using conditional compilation to switch easily between the two.

Good luck!
Graham

_____

Graham Mandeno
Microsoft Access MVP 1996 - 2015

From: MS_Access_Professionals@yahoogroups.com [mailto:MS_Access_Professionals@yahoogroups.com]
Sent: Friday, 30 January 2015 06:23
To: MS_Access_Professionals@yahoogroups.com
Subject: RE: [MS_AccessPros] Error controlling Excel from Access

Here's the whole code, latest version. It's not working.

I get RT 438 at xlSheet.Sheets("qryDetail").Name = "Detail"

If I rem it out, I get RT 1004 on the next line.

etc.

Dim stQryName, stFileName As String
Dim xlApp, xlSheet As Object
Dim SumRow As Long

stFileName = "PathName\" & Me.ClientAbbrev & Me.YYMM

stQryName = "qryDetail"
DoCmd.TransferSpreadsheet acExport, , stQryName, stFileName

stQryName = "qrySummary"
DoCmd.TransferSpreadsheet acExport, , stQryName, stFileName

Set xlApp = CreateObject("Excel.Application")
Set xlSheet = xlApp.Workbooks.Open(stFileName & ".xlsx").Sheets(1)

' FormatDetail
Set xlSheet = xlApp.Sheets(1)
xlSheet.Sheets("qryDetail").Name = "Detail"

SumRow = xlSheet.cells(1, 1).end(xlDown).row + 1

'Add totals to bottom of detail and format
xlSheet.cells(SumRow, 3).FormulaR1C1 = "=COUNT(R2C:R[-1]C)"
xlSheet.cells(SumRow, 3).NumberFormat = "#,##0"
xlSheet.cells(SumRow, 3).Bold = True
xlSheet.cells(SumRow, 14).FormulaR1C1 = "=SUM(R2C:R[-1]C)"
xlSheet.cells(SumRow, 14).Font.Bold = True

'Format columns
xlSheet.Columns("C").NumberFormat = "000000000"
xlSheet.Columns("G:H").HorizontalAlignment = xlCenter
xlSheet.Columns("M").NumberFormat = "0"
xlSheet.Columns("N").NumberFormat = "$#,##0.00"

'Unwrap and auto fit col width
xlSheet.cells.Select
xlSheet.selection.WrapText = False
xlSheet.cells.EntireColumn.AutoFit
xlSheet.range("A2").Select
xlSheet.ActiveWindow.FreezePanes = True

'Format Summary
xlSheet.Sheets("qrySummary").Name = "Summary"
Set xlSheet = xlDoc.Sheets("Summary")

SumRow = xlSheet.range(1, 1).end(xlDown).row + 1

'Add totals to bottom of Summary
xlSheet.range(SumRow, 6 & ":" & SumRow, 11).FormulaR1C1 = "=SUM(R2C:R[-1]C)"
xlSheet.selection.Font.Bold = True

'Format columns
xlSheet.Columns("F:F,H:H,J:J").NumberFormat = "#,##0"
xlSheet.Columns("G:G,I:I,K:K").NumberFormat = "$#,##0.00"
xlSheet.Columns("G:H").HorizontalAlignment = xlCenter

'Unwrap and auto fit col width
xlSheet.cells.Select
xlSheet.selection.WrapText = False
xlSheet.cells.EntireColumn.AutoFit

xlSheet.range("A2").Select
xlSheet.ActiveWindow.FreezePanes = True

xlSheet.ActiveWorkbook.Save
xlSheet.ActiveWorkbook.Close

Application.FollowHyperlink stFileName & ".xlsx"

[Non-text portions of this message have been removed]