Code to import excel to access

[neeko1226]

I writing a vba script to import an excel spreadsheet to an access database so the data can be reconciled to another system. The spreadsheet is created and maintained by another group, so I don't have the luxory of reformatting it.

I'm using the transferspreadsheet method, but when I do I've found that not all the data in the columns is formatted like the column itself. For example, there is a zip code column that is formatted as 'number' on the spreadsheet, but when I import it to access it blanks out 30 percent of the records data in that field because it interprets them as text.

Is there any way to format an import of a spreadsheet (through vba if possible) to take the data exactly as it shows on the sheet? If not, is there a way to format the spreadsheet all fields in each column are formated the same as the column itself?

 

[Remou]

You may wish to look at this thread where some of the difficulties are discussed, and possible solutions mentioned:
Transferspreadsheet not importing some data
thread705-1256252

 

[neeko1226]

Remou

Thanks for the reply. I read through the thread you reccomended and was not able to derive a solution. I've been searching the web in the meantime and have read other articles that reccomend linking the spreadsheet into the database and then appending (while reformating with user defined functions) to another table.

I tried that, but when I link in the spreadsheet the values in the zip code column that are formatted as text show up as "#Num!".

While doing this, I've also noticed that a 'date opened' column has dates in the 1800's that are also showing as "#Num!" when I link in the spreadsheet.

Is there anyway to select the data in the worksheet and indicate a conversion to run on each column (CDate, CStr etc.) before I import or link in the data? If not, do you (or anyone else) have any other possible solutions?

 

[SkipVought]

Hi,

Dates in the 1800?

They are not REAL dates in Excel. Dates are referenced to 1/1/1900 and are STORED as a NUMBER, like today is 38945 that can be FORMATTED as a date in any number of different FORMATS. the DATE VALUE is still 38945, no matther how its formatted.

A "Date" in the 1800's is only a STRING -- NOT a NUMBER like a REAL DATE.

Chances are some of your ZIP codes are NUMBERS, like 71234, while other are STRINGS, like 71234-5678.

You're going to have to COMVERT each suspect column into an UNEQUIVICAL STRING
[tt]
=if(isnumber(A1),"'","")&A1
=TEXT(B1,"yyyy/mm/dd")
[/tt]



Skip,

_[red][/red]

 

[neeko1226]

Skip

I see what you're saying. The dates in the 1800's on the spreadsheet look like this:

07/22/1893 (with a preceeding 0 in the month)

where the dates in the 1900's look like this:

7/22/1993 (no preceeding 0)

The date column is formated as Category = Date, Type = *3/14/2001, but obviously not all the cells are adhereing to that format.

Same for the zip column, some cells are text (they have that little green triangle in the upper left-hand corner) but the column is formatted as number.

As for your solution, I have a couple questions.

1. Does this require me to alter the spreadsheet? It doesn't belong to our group, so I'm not sure that's a possibility.

2. Where would I place those formulas? In the cell itself? Won't that eliminate the data?

Thank you for your help.

 

[neeko1226]

Skip,

I figured out to place the formulas in another column on the sheet, and it works great. Thanks.

Now, is there a way for me to copy the data from the other groups spreadsheet and paste it into a pre-created template (that has my "conversion" columns) on click of a button in Access?

I'm trying to automate this as much as possible so analysts with no coding/data management experiece can run the process. I would hate to have them copying and pasting data from on sheet to another.

Any suggesstions?

 

[SkipVought]

Will this be an on-going process; daily, weekly, monthly etc?

If so, then you ought to create a macro that will perform all the steps from A to Z.

To correct a column of data you must create a formula in a blank column and copy down thru all data rows. Then COPY that column and Edit/Paste Special - VALUES over the column to correct.

Skip,

_[red][/red]

 

[neeko1226]

This will be a weekly & monthly process, so I think it's definately worth automating. I would prefer to use an Access module rather than an Excel or Access macro if it can be avoided. Our MIS standards are not to use macros.

 

[SkipVought]

Well we just agred that the PROBLEM is the DATA in Excel.

How do you supposed it will get rectified by a MACRO in Access? NOT!

Skip,

_[red][/red]