aziz rasul
Active member
- Local time
- Today, 22:01
- Joined
- Jun 26, 2000
- Messages
- 1,935
I have a spreadsheet that contains a column of data under the heading [SPUR]. I have set up a form which allows the data from the spreadsheet file to be imported successfully into a table. The [SPUR] column in the xls file can contain both numbers or letters and the data type in the MS Access table for holding the [SPUR] values is Text.
Here's the problem.
If the first data value in the [SPUR] column, of the spreadsheet, is a number then all is well. However if the first data value in the [SPUR] column is text then any subsequent numerical values in the [SPUR] column appear as Null when imported into the MS Access table.
I have found a work around to this problem: -
1. in the spreadsheet, highlight the [SPUR] column
2. on the menu, select Data\Text to Columns
3. in Step 3 of 3, select the Text option
4. save the spreadsheet
The import will now be successful irrespective of whether the first data value is numerical or text in the spreadsheet.
The above requires manual intervention to the spreadsheet to make it work. Is there a way of solving this problem using VBA within MS Access 97 since I do not know much about MS Excel 97?
Here's the problem.
If the first data value in the [SPUR] column, of the spreadsheet, is a number then all is well. However if the first data value in the [SPUR] column is text then any subsequent numerical values in the [SPUR] column appear as Null when imported into the MS Access table.
I have found a work around to this problem: -
1. in the spreadsheet, highlight the [SPUR] column
2. on the menu, select Data\Text to Columns
3. in Step 3 of 3, select the Text option
4. save the spreadsheet
The import will now be successful irrespective of whether the first data value is numerical or text in the spreadsheet.
The above requires manual intervention to the spreadsheet to make it work. Is there a way of solving this problem using VBA within MS Access 97 since I do not know much about MS Excel 97?
