Import data from Excel

[joycesolomon]

Hi,

I have an excel book, that i want to import the data from sheet TASK, column A and D into a table in access....each time this data is generated the amount of records varies.... the data has first two rows with two types of heading too


I created the below code in Access VBA

DoCmd.TransferSpreadsheet transfertype:=acImport, SpreadsheetType:=5, _
tablename:="TASK2", FileName:="C:\Documents and Settings\Desktop\Test.xls", _
Hasfieldnames:=True, Range:="Task!"


But i keep getting error that the field F1 is not present in the access table, can someone please help.....

 

[HunterN]

Hi,

I am new at this, but I got that error yesterday. I believe I fixed it by making sure that my Access table had the same field names as the Excel spreadsheet.

Nancy

 

[joycesolomon]

my access table has the exact same name actually

 

[Joe4]

I would probably try to name the range in Excel that you want to import (exclude one or both header rows, and only include column A-D), and use the named range in the Range argument of your TransferSpreadsheet command.

It is pretty easy to create an Excel macro to dynamically select the appropriate range each time, something like:

Sub MyNameRangeMacro()
 
    Dim myLastRow As Long
    Dim myRange As Range
 
'   Find last row with data
    myLastRow = Range("A2").SpecialCells(xlLastCell).Row
    
'   Set range to name
    Set myRange = Range("Sheet1!A2:D" & myLastRow)
 
'   Name range ("ImportRange")
    ActiveWorkbook.Names.Add Name:="ImportRange", RefersTo:=myRange
 
End Sub

 

[joycesolomon]

thanks i will give it a try

 

[joycesolomon]

RESOLVED: Import data from Excel

The issue has been resolved, thank you