Importing text from Excel into Access changes it to exponential

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I am importing data files from Excel into an Access database which I have
already designed. I have one variable that is an ID number which is saved as
text in Excel and defined as text in Access. When I import it, some of the
ID numbers come up as exponential (e.g., 8.00578e+0)

Any thoughts? This is making the links next to impossible and the ID
numbers meaningless.
 
Prepend an apostrophe to each of the ID "numbers" in Excel. This forces
Excel to treat them as text in all circumstances, but does not show up
on the worksheet or in formulas. The function below will add an
apostrophe to every cell in the selection.

Sub AddApostrophesNumericToSelection()
Dim C As Excel.Range
For Each C In Application.Selection. _
SpecialCells(xlCellTypeConstants).Cells
If IsNumeric(C.Formula) Then
C.Formula = "'" & C.Formula
End If
Next
End Sub
 
I am unfamiliar with the use of these types of functions. How do I use this
in Excel? Where do I start?

Thanks for your help.
 
1) On the Tools menu, select Visual Basic Editor (VBE).

2) In the VBE, go to Tools|Options and make sure that "Require variable
declaration" is selected on the Editor tab.

3) Select the VBA Project associated with your workbook and insert a new
Module. Paste the code into the module.

4) Back in Excel, save the workbook. Then select the cells you want to
alter and go to Tools|Macro|Macro... This brings up the Macro dialog,
from which you can run the macro.

That's the basics. With a little research in the help files and on Excel
websites you'll be able to work out how to make this macro available to
other workbooks, and to attach it to a toolbar button or menu command.
 
THANKS!

John Nurick said:
1) On the Tools menu, select Visual Basic Editor (VBE).

2) In the VBE, go to Tools|Options and make sure that "Require variable
declaration" is selected on the Editor tab.

3) Select the VBA Project associated with your workbook and insert a new
Module. Paste the code into the module.

4) Back in Excel, save the workbook. Then select the cells you want to
alter and go to Tools|Macro|Macro... This brings up the Macro dialog,
from which you can run the macro.

That's the basics. With a little research in the help files and on Excel
websites you'll be able to work out how to make this macro available to
other workbooks, and to attach it to a toolbar button or menu command.
Click to expand...
 
I would very much like to know how you are importing info from excel to
access. It appears that you are doing exactly what I would like to do. I
have unique identifiers in my sheets as well. I want to track these in a
database. How do you do this? I am new to access and pretty much a novice
in scrpt writing. Any assistance would be great! Thanks.
 
ACD said:
I would very much like to know how you are importing info from excel to
access. It appears that you are doing exactly what I would like to do. I
have unique identifiers in my sheets as well. I want to track these in a
database. How do you do this? I am new to access and pretty much a novice
in scrpt writing. Any assistance would be great! Thanks.
Click to expand...
 
Back
Top