Excel Manipulation from Access…

Sometimes, I have to send data out to Excel.  Every once in a while I have to add a little something to that data… row count, specific Worksheet, a formula, etc.  For those times when you find you need to do the same, here’s what I’ve used… 

I always use Late Binding, highlighted in green.  For more on Late Binding see, http://www.granite.ab.ca/access/latebinding.htm  by Tony an Access MVP

Dim ApXL As Object
Dim xlWBk As Object
Dim xlWSh As Object

Const xlUp As Long = -4162
Const xlDown As Long = -4121

Set ApXL = CreateObject(“Excel.Application”)

Add Row Numbers…
To accomplish this task you must be sure to count a column that has data.  In my case it was Column J.

With xlWSh
xlWSh.Range(“A2:A” & .Cells(.rows.Count, “J”).End(xlUp).Row).Formula = “=IF(OR(ROW()=1,ISTEXT(INDIRECT(ADDRESS(ROW()-1,COLUMN())))),1,INDIRECT(ADDRESS(ROW()-1,COLUMN()))+1)”
End With

Add Filters to your Columns…

 xlWSh.Cells.rows(1).AutoFilter

Auto Size the Columns to Fit the Data…
This is not advised on a Field that has been defined as a Memo data type in Access!

xlWSh.Cells.rows(1).EntireColumn.AutoFit

Remove Prompt to Save Report…

ApXL.DisplayAlerts = False
xlWBk.SaveAs “C:\MyReports\YourReport.xlsx”, 51
ApXL.DisplayAlerts = True

Name Report based on a Form Field Name…

 xlWBk.SaveAs C:\MyReports\” & Me.txtReportName & “.xlsx”, 51

Add Total to a Column…
In the example provided it was Column H that I wanted to have a *Grand Total* on.  (Tip: .End(xlUp) to get Last Row in a Column)

With xlWSh
xlWSh.Cells(.Rows.Count, 8).End(xlUp).Offset(1, 0).Formula = “=SUM(H5:H” & .Range(“H5”).End(xlDown).Row & “)”
xlWSh.Cells(.Rows.Count, 8).End(xlUp).Select
End With

          …and Make Bold
            After I added a* Grand Total* to the Column I wanted to make it Bold.

            ApXL.Selection.Font.Bold = True

Insert a Worksheet from another Workbook (and rename it)…
In this example I use frmExport with fields as specified below.

Dim xlapp As Object
Dim xlWbkNew As Object
Dim xlWbkOld As Object
Dim strSheetName As String

    Set xlWbkNew = xlapp.Workbooks.Open(Forms![frmExport]![txtExportPath] & “\” & Forms![frmExport]![txtCombinedFileName])
     Set xlWbkOld = xlapp.Workbooks.Open(Forms![frmExport]![txtExportPath] & “\” & Forms![frmExport]![txtNewFileName] & “-” & Forms![frmExport]![txtXXX] & Forms![frmExport]![txtNewFileExtension])

     strSheetName = Forms![frmExport]![txtWorksheetName]

    xlWbkOld.Worksheets(strSheetName).Copy After:=xlWbkNew.Worksheets(xlWbkNew.Worksheets.Count)
   ‘Rename the inserted Worksheet
    xlWbkNew.Worksheets(strSheetName).Name = Forms![frmExport]![txtNewFileName] & “-” & Forms![frmExport]![txtXXX]

    xlWbkOld.Close SaveChanges:=True
    xlWbkNew.Close SaveChanges:=True

Excel Constants…

Declared from within the Procedure…
Const xlUp As Long = -4162
Const xlDown As Long = -4121
Const xlCenter As Long = -4108
Const xlBottom As Long = -4107

OR

Declared in General section, at top of Module…
Private Const xlUP As Long = -4162
Private Const xlDown As Long = -4121
Private Const xlCenter As Long = -4108
Private Const xlBottom As Long = -4107

For a complete list see Microsoft Excel Constants, while for Excel 2003, they have not changed.

 2,611 total views,  1 views today