Macro to edit text

K

kayabob

When importing data from Excel to Access, I have a set of data that wont go
through as text, no matter what normal Excel formatting I try. The only
thing that works is to manually insert an apostrophe in Excel, then finally
it will go into Access as text. There are thousands of Excel rows, so I want
to make a repetitive macro to go into each cell, and add an apostrophe at
the beginning of each cell.
I want it to select a cell, edit, insert ' at the beginning of the cell,
enter, and move down 1 row. Then I can loop till null. But, what i am
getting is that the initial data from where i created the macro, keeps
entering in each cell as i move down. How can I edit the macro to make it
select the cell, and put the apostrophe without overwriting the text?
Thank you.
 
D

Dirk Goldgar

kayabob said:
When importing data from Excel to Access, I have a set of data that
wont go through as text, no matter what normal Excel formatting I
try. The only thing that works is to manually insert an apostrophe
in Excel, then finally it will go into Access as text. There are
thousands of Excel rows, so I want to make a repetitive macro to go
into each cell, and add an apostrophe at the beginning of each cell.
I want it to select a cell, edit, insert ' at the beginning of the
cell, enter, and move down 1 row. Then I can loop till null. But,
what i am getting is that the initial data from where i created the
macro, keeps entering in each cell as i move down. How can I edit
the macro to make it select the cell, and put the apostrophe without
overwriting the text?
Thank you.
Click to expand...

I had to deal with a similar situation recently, and was too lazy to
figure out the programming model and write an Excel macro. So I entered
a formula into the first cell in a new column,

=TEXT(C1, "0")

(where C was the column that had the data I needed) and then copied that
formula into all the other cells in that column. Then, in Access, I
imported that column instead of the original column.

I wouldn't do it this way if it were a process I had to run all the
time, but if you only have to do it occasionally, the approach may work
for you.
 
K

Ken Snell [MVP]

Some ready-made EXCEL VBA code that John Nurick, ACCESS MVP, posted
previously:

Sub AddApostrophes()
Dim C As Excel.Range
For Each C In Application.Selection.Cells
If IsNumeric(C.Formula) Then
C.Formula = "'" & C.Formula
End If
Next
End Sub

Sub RemoveApostrophes()
Dim C As Excel.Range
For Each C In Application.Selection.Cells
C.Formula = C.Formula
Next
End Sub

Put these subs into an EXCEL file, select all the cells on the worksheet
that you want to "handle", and then run the appropriate macro.
 
Top