Salad <oil@vinegar.com> wrote in message news:<40327E67.22E1891F@vinegar.com>...[color=blue]
> Paul wrote:
>
> OK, I have a solution for you. My sample code is below, but that really is not the
> solution. I created a table with 4 fields, text, number, and ran it. Worked fine.
> Then I remembered the thing about a column with a mixture of text and numberic. And
> it did just like you said. The value was Null.
>
> I recalled from the old days or Lotus I'd enter a number in a cell as a number but
> if it were an alpha I'd enter an apostrophe before the number to define it as a
> label. So I highlited the column that had a mix of alphas and numerics and did a
> Format/Cells/Text. Now that the column was formated as text (it states "Text format
> cells are treated as text even when a number is in the cell) I ran my routine and it
> imported fine.
>
> So...instead of casting at the Access level it appears you need to cast at the Excel
> level. Not a big deal. Perhaps you can set it via a routine.
>
> I also think you could get some good Excel functionality (input routines, etc) from
> a newsgroup that specialized in VBA for Excel. See also
http://www.mvps.org. and
> check out the Excel link.
>
> Anyway, here's my code
>
> Sub UpdateX()
> Dim dbExcel As Database
> Dim thisdb As Recordset
> Dim mydb As Database
> Dim ITABLE As Recordset
> Dim boolfound As Boolean
> Dim i As Integer
> Dim intpressedcancel As Integer
> Dim strName As Variant
>
> On Error GoTo whatthebeephappened:
>
> boolfound = False
>
> Set mydb = DBEngine.Workspaces(0).Databases(0) 'Current DB
> Set ITABLE = mydb.OpenRecordset("Table1", dbOpenTable) 'Import Table
>
> 'Code used to open excel sheet as database rather than as excel object
> Set dbExcel = OpenDatabase("D:\AccessApps\Testing.XLS", False, True, "excel
> 8.0")
>
> 'loop used for each worksheet in workbook( there should only be one but just in
> case)
> strName = dbExcel.TableDefs(i).Name
> Set thisdb = dbExcel.OpenRecordset(strName, dbOpenSnapshot)
>
> thisdb.MoveLast
> thisdb.MoveFirst
> MsgBox "This db count is " & thisdb.RecordCount
>
> 'code to skip importing of headings
> 'thisdb.AbsolutePosition = 1
> Do While Not thisdb.EOF
> With ITABLE
> i = i + 1
>
> .AddNew
> 'starting at field1 since field(0) is an authonumber
> .Fields(1) = thisdb.Fields(0).Value ' SERIES (mixture text & number)
> .Fields(2) = thisdb.Fields(1).Value ' SERIES (number)
> .Fields(3) = thisdb.Fields(2).Value ' VIN (text)
> .Fields(4) = thisdb.Fields(3).Value ' ENGINE NO (date)
> .Update
> End With
> thisdb.MoveNext
> Loop
> thisdb.Close
> Set thisdb = Nothing
> ITABLE.Close
> Set ITABLE = Nothing
> MsgBox "Done"
> Exit_sub:
> Exit Sub
>
> whatthebeephappened:
> MsgBox Err.Description
> Resume Exit_sub
> End Sub[/color]
Salad,
Thanks once again for replying to the post. I toke your advice on
board and went back to the source and discovered that the file
presented to me had been "touched up" to look pretty. The actual file
as it is produced from an as 400 type machine formats the xls file in
a text format if that makes any sense. The person touching it up was
formatting all the cells in "general". So i have been able to recieve
teh file untouched and formatted it in text and wolla it works. I
would like to be able to maybe produce a macro in excel that formats
the cells before importation. So that might be my next post if I cant
find anything on there already.
