importing data from excel into access using asp

[snowmantle]

I have an asp page that you can use to download a table from an access dbase into a saved excel file and then once you have editted the excel file you can upload the file into the dbase again.

Any changes to existing id's get updated and any new rows without an id get inserted.

The problem i have got at the moment is that when it gets exported the dates go into a date format column but as text.

While when you add a new date into excel it formats the date correctly, eg. a number 39885 with the date mask over it.

As a work around i have set the date columns to always be text and used the below:

aryDateSp = Split(rstExcel(aryColNames(k)),"/")
Set objParam = cmd.CreateParameter(aryColNames(k), adDate, adParamInput, 255, dateSerial(aryDateSp(2),aryDateSp(1),aryDateSp(0)))
						cmd.Parameters.Append objParam

The code uses a name of the field from an array, splits the text up and uses the dateSerial function to return a date variant value, the parameter is then created for the command object so that the update or insert can be done.

Problem i've got is the above won't work for when the date is in a column with date format.. i get a number overflow error.

 

[kss444]

Not sure I understood what's going on, but before you insert the value why don't you convert it to what ever you want, or manipulate the cell value by doing a replace ect..?

 

[snowmantle]

I was splitting the text and then making a date variant out of it and assigning it to a parameter.

It wasn't causing my problems though, the data from excel going into my recordset to begin with was.. its all fixed now.