import excel sheet into access table 1

[Junkie88]

i want to import an excel sheet into access table such that i ignore the middle 6 columns of the sheet. How can i do that? I tried to do this by first converting the excel sheet into a text file and then reading the text file. But i got a subscript out of range error. i think that this has something to do with the size of the tab in the text file but not sure.

 

[Remou]

Why not import the whole sheet and then delete the extra columns?

 

[Junkie88]

here is the code i am using to read the text fle.
the error occurs at If arList(iPos) <> "" Then

Set db = CurrentDb
Set rst = db.OpenRecordset("SELECT * FROM UPCS")
num = 15

With rst
strname = "C:\Documents and Settings\dima\Desktop\Barcodes\barcode1.txt"
Open strname For Input As #1
While Not EOF(1)
Line Input #1, DataRead
arList = Split(DataRead, " ")
.AddNew
iPos = 0
For Counter = 0 To num
If iPos < 4 Or iPos > 10 Then
If arList(iPos) <> "" Then
.Fields(Counter) = Val(arList(iPos))
End If
End If
iPos = iPos + 1
Next
.Update
Wend
Close #1
.Close
End With

 

[Junkie88]

how do i transfer excel sheets?

 

[kjv1611]

[blue]DoCmd.TransferSpreadsheet[/blue]

--

"If to err is human, then I must be some kind of human!" -Me

 

[bubba100]

This is one way. Look in the help for more detail. DoCmd.TransferSpreadsheet acImport, 8, "Table 1", "C:\Archive\File Directory.xls", True, "Sheet1!a1:e400"