Using Access and Excel and formatting Excel data

I’ve worked with many companies that use Excel and Access together.  Copying tables or query results from Access and pasting into Excel can be beneficial to analyze if you are more comfortable in Excel.

When pasting data into Excel, the cell structure isn’t visually appealing.  The widths and heights are not setup that make it easy to read.  The following macro will put the data into an easy to read format.

Copy and paste this code (in between bolded rows) into your personal macro workbook (see earlier blog post).  You can set up keyboard shortcuts to any macro you create, I have this set up to Ctrl+j on my file.

START COPYING ON NEXT ROW

Sub Format_Access()

‘ Keyboard Shortcut: Ctrl+j

Application.ScreenUpdating = False

On Error GoTo Errormsg

If ActiveCell = Range(“a1”) Then

Columns(“A:A”).Select

Range(Selection, Selection.End(xlToRight)).Select

Selection.ColumnWidth = 106.71

Columns(“A:IV”).EntireColumn.AutoFit

Cells.Select

Cells.EntireRow.AutoFit

End If

If ActiveCell <> Range(“A1”) Then

ActiveCell.CurrentRegion.Select

Selection.ColumnWidth = 106.71

Selection.EntireColumn.AutoFit

Cells.Select

Cells.EntireRow.AutoFit

End If

Exit Sub

Errormsg:

MsgBox “There was an error in the macro, it did not run effectively!!”, vbCritical

Exit Sub

End Sub

STOP SECTION TO COPY ON PREVIOUS ROW

I use this macro on a daily basis, it comes in handy when your column structure isn’t visually how you want it.  The key is that cell selected will determine what area will be changed on the height and width.  Let me know if you have any questions.

Create an Excel file that holds just your personal macros…

I’m not taking any credit for this first post, but understanding how to build your own excel file that holds all of your personal macros will save MUCH time and effort.  I even have the file open automatically when excel opens, so my macros are always at my fingertips.

http://office.microsoft.com/en-us/excel/HA010872961033.aspx

Once you understand how this works, you can hop on board to some of the cool macros I’ve created over the years as a guy in Finance for various companies.