#NUM Error when linking Excel Spreadsheet to Access Table

This happens because there is a combination of numbers and letters in one of the columns of the Excel file you want to link. This will always create a numeric field overflow error. Access uses the first 8 rows of the spreadsheet to determine the data type of the column in the linked table. Say for example that your first 8 rows in column A of your spreadsheet have the following data:
1
2
3
4
5
6
7
8
9A

If you link this dataset to an Access table, value 9A will produce a #NUM Error.

Here is some vba code that will fix this problem. The code will insert an apostrophe before the value in any cell that you have selected and in order to make the apostrophe invisible, the code will activate the next cell down the column. When the code is done running on the cells you have selected, the upper left hand corner of the cell will turn green. This means that Excel thinks that this cell has a value that is an error. Specifically, the error message says:
"The number in this cell is formatted as text or preceded by an apostrophe." Clicking on ignore error will do just fine. You can apply this “ignore error” fix to all the cells you have currently selected. If the green indicator doesn’t bother you, leave it alone, it’s harmless.

Let me know if the code helped you. Because I found some code in Microsoft’s website that was supposed to fix this problem but it didn’t. Their code deleted the first character of every cell I had selected.

Here’s my solution:
Select the cells that you want to fix and run this macro:

Sub ConvertToAlphanumeric()

Dim cell As Object

For Each cell In Selection
cell.Value = "'" & cell.Value
ActiveCell.Offset(1, 0).Activate
Next

MsgBox "done!"

End Sub

How to Create a Personal Workbook for Easy Access to VBA Macros

If you have a set of macros / vba code that you would like to have readily available every time you open Excel, put them in the personal workbook. The personal workbook will automatically open but remain hidden every time you start Excel. The macros in this workbook will always be available.

Here's how:
First, record a dummy macro. This step will create the Personal Workbook for you.
>>> Tools / Macro / Record New Macro... / Store Macro in: "Personal Macro Workbook" / OK / and STOP. Your personal workbook has been created.

Now to actually go in and input vba code, press ALT + F11 and expand the PERSONAL.XLS Project. Or unhide the personal workbook first: Window / Unhide / Personal.xls and then edit the dummy macro you just created.

Loop Trough Cells in Excel and Change the Value of an Adjacent Cell

There is an Excel spreadsheet with two columns. Column A contains customer names and column B contains the number of invoices billed. Whenever the cell in column B is empty, I want to input a Zero. If the cell in column B is not empty, then I do not want to modify the existing value.
The loop will stop when it reaches an empty cell in column A.


Sub LoopThruCells()
[A1].Select
intRow = 0
Do While ActiveCell.Offset(intRow, 0).Value <> ""
If ActiveCell.Offset(intRow, 1).Value = "" Then
ActiveCell.Offset(intRow, 1).Value = 0
Else
ActiveCell.Offset(intRow, 1).Value = ActiveCell.Offset(intRow, 1).Value
End If
intRow = intRow + 1
Loop
End Sub

Converting Null values in Access

Use the Nz Function in Access to convert a null value to whatever you desire. This function comes in handy when you are doing divisions. You know you can't divide by 0, so wrap your denominator with the Nz function like this:
Syntax: Nz([Field Name], [valueifnull])

Tips for working with Complex Access Databases used for Data Audits

Most of the development I do these days involves creating Access databases to help audit data, this involves importing data from a human resources system and from PeopleSoft, comparing the data against both systems, and finally exporting the data as EDI files for invoicing. The access databases used for these procedures generally have 30 or more queries. With this amount of queries it is hard to come back 3 months later and remember the purpose each query and the queries’ dependencies. So I have become extra careful in the names I give to the queries. Here are a couple of tips.

1) If the queries run in sequential order, precede the query name with a number such as 01, 02, 03. This will help identify when the query is run in the data audit process. My preference is to name the queries like this:
STEP-01-Import_Excel_File
STEP-02-Make_tbl_Excel_PeopleSoft_Comparison
STEP-03-Export_Results_To_Excel
With this naming convention, I can come back later and sort by the query name to get a quick idea of a query’s dependencies without having to open each query in design view. This has been invaluable in troubleshooting and improving the audit solutions.

2) If you inherit a database that uses a lot of consecutive queries and the names have already been defined and you don’t want to change the names, use the query’s Description field in the Properties Window to give the query an ordinal value. You can then sort by the query’s description field.

3) Always use the Description field in the Properties Window of the query. This can be accessed by right-clicking on the query. If you already use a naming convention similar to tip # 1, then use this field to put comments that tell you something significant or unique about the query. For example if the query connects to an external source such as another Access database, SQL Server or Excel file, here would be the perfect place to put the ODBC definition for future reference.

4) If the query creates a table, then in the description field or even in the query’s name, give the name of the table it creates.
For example: STEP-02-Make_tbl_PeopleSoft_Comparison.

5) Do not rely heavily on queries that query other queries, especially if the underlying query performs complex calculations. For example, say you need to come up with the unbilled percentage of invoices unbilled in PeopleSoft by Customer name. You probably could do this with a single query, but my advice is to first create a summary table that has all the numbers required to perform a percentage calculation by customer name. Then create a second query that performs the percentage calculation based on the summary table. This will make future troubleshooting easier and will help when someone questions your final percentage results.

Sarbanes Oxley Compliance tip for Access Database

I created an Access database for an audit procedure to compare data from PeopleSoft and our Human Resources Information System (HRIS). Based on this weekly audit process, a table is created; the table lists timecards that are unbilled or missing from PeopleSoft. And every week our Billing Accountants are supposed to review this table and update the status of the timecard. So to comply with Sarbanes Oxley, I created a form that logs the Billing Accountant’s Windows User ID when he or she changes the timecard’s status.

On the change event of the “Status” combo box, there is VBA code that updates two text boxes on the form. The first text box is called txtChangedBy and the second text box is called txtChangedDate. These two text boxes are linked to an underlying table and map to appropriate table fields. Whenever the Billing Accountant changes the timecard’s status, his Windows User ID and his computer’s system time are logged. And there you have it, a Sarbanes-Oxley compliant Access database.


Private Sub Billing_Status_Change()

Me!txtChangedBy = Environ("USERNAME")
Me!txtChangedDate = Now()

End Sub

**Please note that for the Environ function to work, Excel’s macro security level must be set to low. This means you better have some good anti-virus protection.

Loop through worksheets in Excel

Sub LoopThruSheets()
Dim wsSheet As Worksheet
Dim strName As String
On Error Resume Next
For Each wsSheet In Worksheets

' ***START CODE FOR WHATEVER OPERATION YOU NEED. ***
strName = wsSheet.name
MsgBox name
'*** END CODE FOR WHATEVER OPERATION YOU NEED. ***

Next wsSheet
On Error GoTo 0
End Sub

Testing your Access Program

A good way to test the VBA code behind your forms is to input values of the following three categories:

1) Expected Values -- these are the values in the range you ask for. If you ask for numbers between 1 and 10, the expected values would be 2 through 9.

2) Boundary Condition Values -- these are the values that lie in the boundaries of your range. The boundary condition values would be 1 and 10.

3) Out-of-Bounds values -- these are the values that fall anywhere outside the range of 1 through 10. For example, 0, "true", "false", (empty), !, @, &, *, etc.

How to determine who is logged on to an Access database

Found some more useful code for finding out who is currently using an Access database. The code worked perfectly on Access 2003.

http://support.microsoft.com/default.aspx?scid=kb;en-us;285822

Access 2003 How to Automatically log out idle users

Found very useful code for automatically logging out idle users in Access. It worked like a charm on Access 2003.

http://www.tek-tips.com/faqs.cfm?fid=1432