Excel VBA – Add apostrophe before a field for Access Import

Posted on | September 22, 2010 | No Comments

There are times, you try to import your data to Access but might not end successful due to some alpha numeric values in it. Access determines the type of field by accessing the first few rows of the column. If your data contains numeric values in the first few rows and alphanumeric in the rest of them, then access will try to import the column as a numeric field. But the import won’t be successful as the remaining part of the column contains the alpha numeric.

So, it is wise to make the column as text. This can be achieved by adding an apostrophe (‘) in front of each cell value. This process looks simple, but it turns out to be tedious when you have a large amount of data (say 1000 rows). To make your life simple, you can do a bit of VBA coding and complete the task in few seconds.

VBA Code:

Sub addApostrophe()
Dim rng As Range
Set rng = Selection
For Each c In rng.Cells
c.Value = "'" & c.Value
Next c
End Sub

How to use this macro:

1. Select the range where you want the apostrophe to be added
2. Copy this code and goto VBE by pressing ‘Alt + F11′
3. In the project Explorer – Select ‘ThisWorkbook’ and past the code on the code window
4. Press ‘F5′ and the apostrophe will be added to the selected cells.


Leave a Reply