Code not executing

[nayfeh]

Hi,
I'm trying to use this code to import data from an excel spreadsheet directly into a table in my database. I've copied and pasted most of this code so I'm sure I'm missing something. As well, the OpenAsTextStream(strPath, ForReading, 0)....does this not work for excel files????

Can you please direct me to the right solution.
Here is the code:

txtPath.SetFocus
strPath = txtPath.Text

Set oXL = CreateObject("Excel.Application&quot
Set fFolder = fso.GetFolder(strPath)

Set oWB = oXL.Workbooks.Open(fFile.Path)
Set oWS = oWB.Worksheets(1)

If oWS.Cells(9, 1) <> &quot;Date&quot; Or _
oWS.Cells(9, 2) <> &quot;Invoice #&quot; Or _
oWS.Cells(9, 3) <> &quot;Description&quot; Or _
oWS.Cells(9, 5) <> &quot;Card&quot; Or _
oWS.Cells(9, 6) <> &quot;Site Address&quot; Or _
oWS.Cells(9, 9) <> &quot;Grade&quot; Then Exit Sub

Else: Do
strsql = &quot;SELECT * FROM tbl_Transactions&quot;
Set db = CurrentDb
Set rs = db.OpenRecordset(strsql, dbOpenDynaset)
Set fs = CreateObject(&quot;Scripting.FileSystemObject&quot
Set f = fs.OpenAsTextStream(strPath, ForReading, 0)
rs.AddNew
rs!Date = Format(oWS.Cells(12, 1), &quot;mm/dd/yyyy&quot
rs!Invoice# = oWS.Cells(12, 2)
rs!Description = oWS.Cells(12, 3)

Loop
f.Close
rs.Close
Set db = Nothing
Set rs = Nothing
Set f = Nothing
Set fs = Nothing

End If

oWB.Close False
DoEvents
Me.Refresh
oXL.Quit


Thanks in advance.
TN

 

[SQLBI]

The easiest way to import a .xls file is to use the TransferSpreadsheet method of the DoCmd Object.

Access 2000 has an excellent helpfile on this.

Leigh Moore
LJM Analysis Ltd

http://www.ljm-analysis.co.uk

 

[nayfeh]

Can I use the transferspreadsheet method if the fields are not in sequential order. i.e. data is in fields 1, 3, 4, 6, 7, etc.. If so, can someone help me with the code for this.

Thanks in advance,
TN