{"id":691,"date":"2013-07-20T23:44:42","date_gmt":"2013-07-20T23:44:42","guid":{"rendered":"http:\/\/regina-whipp.com\/blog\/?p=691"},"modified":"2013-11-08T18:43:03","modified_gmt":"2013-11-08T23:43:03","slug":"excel-manipulation-from-access","status":"publish","type":"post","link":"https:\/\/regina-whipp.com\/blog\/?p=691","title":{"rendered":"Excel Manipulation from Access…"},"content":{"rendered":"

Sometimes, I have to send data out to Excel.\u00a0 Every once in a while I have to add a little something to that data… row count, specific Worksheet, a formula, etc.\u00a0 For those times when you find you need to do the same, here\u2019s what I\u2019ve used…<\/span><\/span>\u00a0<\/span><\/p>\n

I always use Late Binding, highlighted in green.\u00a0 For more on Late Binding see, <\/span>http:\/\/www.granite.ab.ca\/access\/latebinding.htm<\/span><\/a>\u00a0 by Tony an Access MVP<\/span><\/span><\/p>\n

Dim ApXL As Object
\n<\/b><\/span>Dim xlWBk As Object
\n<\/span>Dim xlWSh As Object<\/span><\/p>\n

Const xlUp As Long = -4162
\n<\/span>Const xlDown As Long = -4121<\/span><\/p>\n

Set ApXL = CreateObject(“Excel.Application”)<\/b><\/span><\/p>\n

Add Row Numbers…
\n<\/span><\/span><\/b>To accomplish this task you must be sure to count a column that has data. \u00a0In my case it was Column J.<\/span><\/p>\n

With xlWSh
\n<\/span>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)”
\n<\/b><\/span>End With<\/span><\/p>\n

Add Filters to your Columns…<\/span><\/span><\/b><\/p>\n

\u00a0xlWSh.Cells.rows(1).AutoFilter<\/b><\/span><\/p>\n

Auto Size the Columns to Fit the Data…
\n<\/span><\/span><\/b>This is <\/span>not<\/b> <\/span>advised on a Field that has been defined as a Memo data type in Access!<\/span><\/p>\n

xlWSh.Cells.rows(1).EntireColumn.AutoFit<\/b><\/span><\/p>\n

Remove Prompt to Save Report…<\/span><\/span><\/b><\/p>\n

ApXL.DisplayAlerts = False
\n<\/span><\/b>xlWBk.SaveAs “C:\\MyReports\\YourReport.xlsx”, 51
\n<\/span>ApXL.DisplayAlerts = True<\/span><\/b><\/p>\n

Name Report based on a Form Field Name…<\/span><\/span><\/b><\/p>\n

\u00a0xlWBk.SaveAs C:\\MyReports\\” & Me.txtReportName <\/b>& “.xlsx”, 51<\/span><\/p>\n

Add Total to a Column…
\n<\/span><\/span><\/b>In the example provided it was Column H that I wanted to have a *Grand Total* on.\u00a0 (Tip: .End(xlUp) to get Last Row in a Column)<\/span><\/p>\n

With xlWSh
\n<\/span>xlWSh.Cells(.Rows.Count, 8).End(xlUp).Offset(1, 0).Formula = “=SUM(H5:H” & .Range(“H5”).End(xlDown).Row & “)”
\n<\/b><\/span>xlWSh.Cells(.Rows.Count, 8).End(xlUp).Select
\n<\/span>End With<\/span><\/p>\n

\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 …and Make Bold
\n<\/b><\/span><\/span>\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 <\/span><\/b>After I added a* Grand Total* to the Column I wanted to make it Bold.<\/span><\/p>\n

\u00a0\u00a0<\/span>\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 <\/span>ApXL.Selection.Font.Bold = True<\/span><\/p>\n

Insert a Worksheet from another Workbook (and rename it)…
\n<\/span><\/span><\/b>In this example I use frmExport with fields as specified below.<\/span><\/p>\n

Dim xlapp As Object
\n<\/span>Dim xlWbkNew<\/b> As Object
\n<\/span>Dim xlWbkOld<\/b> As Object
\n<\/span>Dim <\/span>strSheetName<\/b> <\/span>As String<\/span><\/p>\n

\u00a0\u00a0\u00a0 <\/span>Set xlWbkNew<\/b> = xlapp.Workbooks.Open(Forms![frmExport]![txtExportPath] & “\\” & Forms![frmExport]![txtCombinedFileName])
\n<\/span>\u00a0\u00a0\u00a0\u00a0 Set xlWbkOld<\/b> = xlapp.Workbooks.Open(Forms![frmExport]![txtExportPath] & “\\” & Forms![frmExport]![txtNewFileName] & “-” & Forms![frmExport]![txtXXX] & Forms![frmExport]![txtNewFileExtension])<\/span><\/p>\n

\u00a0\u00a0\u00a0\u00a0 strSheetName<\/span> <\/strong>= Forms![frmExport]![txtWorksheetName]<\/span><\/p>\n

\u00a0\u00a0\u00a0\u00a0xlWbkOld<\/b>.Worksheets(<\/span>strSheetName<\/b><\/span>).Copy After:=xlWbkNew<\/b>.Worksheets(xlWbkNew.Worksheets.Count)
\n<\/span>\u00a0\u00a0 \u2018Rename the inserted Worksheet
\n<\/span>\u00a0\u00a0\u00a0 xlWbkNew<\/b>.Worksheets(<\/span>strSheetName<\/b><\/span>).Name = Forms![frmExport]![txtNewFileName] & “-” & Forms![frmExport]![txtXXX]<\/span><\/p>\n

\u00a0\u00a0\u00a0 xlWbkOld<\/strong>.Close SaveChanges:=True
\n<\/span>\u00a0\u00a0\u00a0 xlWbkNew<\/strong>.Close SaveChanges:=True<\/span><\/p>\n

Excel Constants…
\n<\/b><\/h5>\n

Declared from within the Procedure…
\n<\/strong>Const xlUp As Long = -4162
\nConst xlDown As Long = -4121
\nConst xlCenter As Long = -4108
\nConst xlBottom As Long = -4107<\/p>\n

OR<\/h5>\n

Declared in General section, at top of Module…
\n<\/strong>Private Const xlUP As Long = -4162
\nPrivate Const xlDown As Long = -4121
\nPrivate Const xlCenter As Long = -4108
\nPrivate Const xlBottom As Long = -4107<\/p>\n

For a complete list see Microsoft Excel Constants<\/a>, while for Excel 2003, they have not changed.<\/p>\n

<\/div>\n

<\/path><\/svg><\/i> \"Loading\"<\/p>\n

<\/div>\n","protected":false},"excerpt":{"rendered":"

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\u2019s what I\u2019ve used… <\/p>\n

I always use Late Binding, highlighted in […]<\/p>\n

<\/div>\n

<\/path><\/svg><\/i> \"Loading\"<\/p>\n

<\/div>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[5],"tags":[34,21],"class_list":["post-691","post","type-post","status-publish","format-standard","hentry","category-access-tips","tag-excel","tag-vba","odd"],"a3_pvc":{"activated":true,"total_views":2757,"today_views":0},"_links":{"self":[{"href":"https:\/\/regina-whipp.com\/blog\/index.php?rest_route=\/wp\/v2\/posts\/691","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/regina-whipp.com\/blog\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/regina-whipp.com\/blog\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/regina-whipp.com\/blog\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/regina-whipp.com\/blog\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=691"}],"version-history":[{"count":12,"href":"https:\/\/regina-whipp.com\/blog\/index.php?rest_route=\/wp\/v2\/posts\/691\/revisions"}],"predecessor-version":[{"id":860,"href":"https:\/\/regina-whipp.com\/blog\/index.php?rest_route=\/wp\/v2\/posts\/691\/revisions\/860"}],"wp:attachment":[{"href":"https:\/\/regina-whipp.com\/blog\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=691"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/regina-whipp.com\/blog\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=691"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/regina-whipp.com\/blog\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=691"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}