Import Excel 1

[fileman1]

I found some code which I am trying to build up on to import Excel into an Access Table.

I have created a spreadsheet, having just one column. However the range errors, as I do not know how the range actually works. Can someone put me right, thanks

Dim db As DAO.Database
Set db = CurrentDb
On Error Resume Next:   db.TableDefs.Delete "tblImport":   On Error GoTo 0
db.TableDefs.Refresh
DoCmd.TransferSpreadsheet _
    TransferType:=acImport, _
    SpreadsheetType:=acSpreadsheetTypeExcel9, _
    TableName:="SallyImport", _
    FileName:="C:\SALLY1.xlsX", _
    HasFieldNames:=True, _
    Range:="SheetName!A:DN"
db.TableDefs.Refresh
db.Close:   Set db = Nothing

It errors on the line Range:="SheetName!AN"

 

[PHV]

So, you want to import 118 columns from a sheet named SheetName in workbook SALLY1 ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[fileman1]

Thanks, I don't think so!! Is there an easy to understand way to for example import columns A-B, rows 1 to 300 or is life not so easy? I looked at other examples and they were no clearer.

 

[PHV]

I'd try this:
Range:="A1:B300"

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[fileman1]

No, that gives me an error 2391, saying I don't have the field F1 in my table? Cannot see that anywhere in the code?

 

[PHV]

Seems like your data in excel have no header and thus the column names are F1, F2 and so on.
Therefore your SallyImport table should have fields with those name.

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[fileman1]

That did it, thanks. I renamed my table field name to F1, and it moaned there was no F2, so I added F2 and it worked. Thanks, I will try to see how I add a header in Excel to include field names. Have a good weekend