Access MVP (2010-2015)


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,  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…


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!


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


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.

1,149 total views, 1 views today

Comments are closed.